Backtrack:  
 
by lunarg on May 26th 2015, at 16:17

Two methods of truncating the transaction logs for a database for SQL Server 2008 or newer:

Backup transaction log to "nowhere"

Perform a backup to the nul device, essentially a "black hole":

BACKUP LOG [databaseName] TO DISK = 'nul:'

Run a shrink on the transaction log:

Temporarily set the recovery model to SIMPLE, then shrink the transaction log:

ALTER DATABASE databaseName SET RECOVERY SIMPLE
DBCC SHRINKFILE('databaseName_log', 0, TRUNCATEONLY)
ALTER DATABASE databaseName SET RECOVERY FULL

WARNING: truncating the transaction log without a backup may result in data loss in case of a database failure!

EDIT (26/05/2015):

You could attempt the backup/shrink routine without changing the recovery mode, but this will most likely only partially release space. Your best bet is to run the statements several times in a row (leave a few minutes between runs), increasing the chance of a proper shrink of the transaction log. But do note that on a very busy system, this won't be a good idea because the transaction log will always be in use.

This is a more safer approach than changing the recovery model to SIMPLE, but will not release as much as space. I also do not recommend running this on a production system during heavy load, as this would only slow down the server and not release any space because of the transaction log being in use all the time. And in conclusion, as always, use with caution!

 
 
« March 2019»
SunMonTueWedThuFriSat
     12
3456789
10111213141516
17181920212223
24252627282930
31      
 
Links
 
Quote
« Most people tend to avoid true conflict. Ironically this breeds more conflict. »