Here is a short script to quickly shrink your log file if you DO NOT NEED to ever recover the database before the last full backup. Keep in mind that changing the Recovery Model to SIMPLE will break the log chain. You will need perform a backup of your database to start a new log chain and rebuild log-shipping. This solution should only be used if you know fully well the database you are applying it to.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
USE MyDatabase GO EXEC sp_helpfile GO ALTER DATABASE MyDatabase SET RECOVERY SIMPLE GO DBCC SHRINKFILE (MyDatabase_log, 1) GO EXEC sp_helpfile GO -- If the log file was successfully shrunk, and you need to put the recovery model back to FULL: ALTER DATABASE MyDatabase SET RECOVERY FULL GO |
Here’s a good link discussing the issue:
How to shrink the database transaction log
Views – 2201