Now any sysadmin know that MS SQL server can generate whopper log files for the databases especially if there are a large number of transactions taking place. It’s simple enough to shrink the logs periodically but I wanted to find a way to safely automate this.
A quick Google turned up this very informative post by Roni Schuetz. In essence it will loop through all of the DB logs and shrink them back to “normal” size:
CREATE TABLE #TDatabases(
DBName nvarchar(128),
DBLogicalName nvarchar(128)
)
INSERT INTO #TDatabases
SELECT db.name DBName, mf.name DBLogicalName
FROM sys.databases db join sys.master_files mf
on db.database_id = mf.database_id
WHERE db.name not in ('master', 'tempdb', 'model', 'msdb',
'distribution') AND type_desc LIKE 'log'
SET NOCOUNT ON
DECLARE @VarDBLogicalName nvarchar(128)
DECLARE @VarDBName nvarchar(128)
DECLARE @VarRowCount int
SELECT top 1 @VarDBName = DBName, @VarDBLogicalName = DBLogicalName
FROM #TDatabases
SET @VarRowCount = @@rowcount
WHILE @VarRowCount <> 0
BEGIN
EXEC(' use ' + @VarDBName + ' backup log '+ @VarDBName + ' with no_log
dbcc shrinkfile(''' + @VarDBLogicalName + ''', TRUNCATEONLY) WITH
NO_INFOMSGS')
DELETE
FROM #TDatabases
WHERE DBName = @VarDBName
SELECT top 1 @VarDBName = DBName, @VarDBLogicalName =
DBLogicalName
FROM #TDatabases
SET @VarRowCount = @@ROWCOUNT
END
DROP TABLE #TDatabases
SET NOCOUNT OFF
Now you can also automate this process by savin the above script into a SQL file and then creating a BAT file to run this command:
osql -E -i shrinkalldatabases.sql -o result.txt
where shinkalldatabases.sql is the above SQL script.
Set it to run once a week and Bob’s your Uncle. And yes I do have an Uncle Bob…
