Reducing the transaction log file size in MS SQL 2005

In the past, when using MS SQL 2000, it was fairly easy to reduce the file size used by the transaction log. Just backup the transaction log, perform a shrink command and then the file size has dropped to a minimum.

In MS SQL 2005 this won't work. If you do so, then the file might be shrinked a bit, but it is not very small yet. To do this you would need to do the shrink command (including reorganizing the pages to the beginning of the file) before doing the backup and once again after the backup. If you do so, then the file has been really minimalized.

To do this automatically, you would set up a maintenance plan. In that plan it would be sufficient to do a shrink and then the backup.

The reason that you have to shrink the file first has to do with the active log pages and the order of it. For more information on why you need to shrink the file first, take a look at this page: http://support.microsoft.com/kb/907511