Thursday, October 22, 2009

SQL Server: Automated log file maintenance

Ok you've all seen it before. You remote desktop onto a client's sql server to upload your app, when your upload fails due to a lack of disk space. Darnit! So you have to reduce the LDF's, which has grown to be several GBs large.

The following little script will reduce all the non-system db's log files. One can also schedule this script as a job that automatically reduces the log files. (Disclaimer: Read-up on the function log files fulfil before simply using this script. I would not recommend running this on critical production systems.)

-- This gets us all on the same page
use [master]

-- Define some variables
declare @statement varchar(2000)
declare @dbname sysname
declare dbname_cursor cursor for

-- Select all non-system db's
select [name] from master.dbo.sysdatabases where
[name] <> 'master' and
[name] <> 'model' and
[name] <> 'msdb' and
[name] <> 'tempdb'

-- Iterate through each db record
open dbname_cursor
fetch next from dbname_cursor into @dbname
while @@fetch_status <> -1
select @statement = ''
select @statement = @statement + 'use ' + @dbname + '; '

-- Backup db by runcating only (will not reduce physical file size)
select @statement = @statement + 'backup log ' + @dbname + ' with truncate_only; '

-- Get the db log file name
select @statement = @statement + 'declare @log_file varchar(2000); '
select @statement = @statement + 'select @log_file = [name] from sys.database_files where type_desc = ''LOG''; '

-- Reduce the physical file size
select @statement = @statement + 'exec(''dbcc shrinkfile ('' + @log_file + '', 0)''); '
fetch next from dbname_cursor into @dbname
close dbname_cursor
deallocate dbname_cursor

No comments: