Shrink DB Log File
SQL Server 2000: Tested
SQL Server 2005: Tested
SQL Server 2008: Tested
SQL Server 2008R2: Tested
SQL Server 2012: Not Tested
This is a piece of code that I'm pretty sure was sourced from a Microsoft engineer on one of their forums. In any case, it has worked for me for over 10 years now with no modification.
Shrinking the database log file is ideally something that should never need to be done. However, even the best planned DB implementation is subject to changes in usage patterns or application bugs that can cause the transaction log to grow. Reporting and/or batch jobs are usually the culprits (as these are usually not something I find that is well planned or designed ahead of release time, despite the urgings of the DBA)
Therefore, the will inevitably come a time when the transaction log bloats and causes disk space or performance problems and this is where this script has been invaluable.
Just substitute the required values into the places noted (in Orange) and run the script.
Some points to note are:
The script has a default max run time of 10 minutes. I have generally found that it will run in 2 or 3 minutes
It may not work fully the first time (ie. only partial space recovery) and can safely be run multiple times
It can be run (in an emergency) against an operational DB.
Ensure you have a full backup of the DB before running it.
Ensure you take a full backup of the DB after running it.
In all cases, ensure you are familiar with your backup scheme and that you know the effect that running this will have on the Log Sequence Number and your ability to restore to the level specified in any SLA, especially if your backup scheme includes Differential, Incremental or File level backups