Two methods of truncating the transaction logs for a database for SQL Server 2008 or newer:
Perform a backup to the nul device, essentially a "black hole":
BACKUP LOG [databaseName] TO DISK = 'nul:'
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!
« ‹ | December 2024 | › » | ||||
Sun | Mon | Tue | Wed | Thu | Fri | Sat |
1 | 2 | 3 | 4 | 5 | 6 | 7 |
8 | 9 | 10 | 11 | 12 | 13 | 14 |
15 | 16 | 17 | 18 | 19 | 20 | 21 |
22 | 23 | 24 | 25 | 26 | 27 | 28 |
29 | 30 | 31 |