SQL transaction logs growing and growing and …
Problems with growing transaction logs on SQL servers is a common problem. But many admins doesn’t think twice about the problem, they just add more disk space. The transaction log is only needed when you want to do a restore to an other point then the last backup. So in many cases they are not needed right after a backup. Or you want to take a backup on them and then free up the space on you raid 10 disks that are more expensive then your backup media. To free up all the space used you need to set the database in simple recovery mode and then do a shrink on the DB. When that is done you place the DB back in the FULL RECOVERY MODE. So why not do this automatically ones a week right after the backup?
Just add an T-SQL section to your maintenance plan and run the code below:
[sql]EXEC sp_MSForEachDB
‘ALTER DATABASE [?] SET RECOVERY SIMPLE;
DBCC SHRINKDATABASE (?, 10, TRUNCATEONLY);
ALTER DATABASE [?] SET RECOVERY FULL;'[/sql]
Or if you want to build a custom list of databases, so you don’t run this on all the DB’s run this code to generate the T-SQL:
[sql]EXEC sp_MSForEachDB
‘PRINT "ALTER DATABASE [?] SET RECOVERY SIMPLE";
PRINT "GO";
PRINT "DBCC SHRINKDATABASE (?, 10, TRUNCATEONLY);";
PRINT "GO";
PRINT "ALTER DATABASE [?] SET RECOVERY FULL;"
PRINT "GO";'[/sql]
The important thing is to set TRUNCATEONLY, if you don’t you will fragment the entire DB file. When you run the command above without the TRUNCATEONLY parameter it will take the last record in the DB file and place in the first hole of empty space in the DB file and so on. So you will create a fragmentation if you do so.
Another way to do it, which is slower and created more disk IO but can be done, is to do a transaction log backup and then just delete the transaction file backup.
MS documentation: http://msdn.microsoft.com/en-us/library/ms190488.aspx
Hackviking aka Kristofer Källsbo 