SQL Stuff

Shrink All Files Across All Databases

Here’s some logic that will shrink all log files across all databases. First it checks to see if the database is in Full recovery. This way we know if the shrink file operation is even warranted. If Full Recovery is detected it will shink the log files. Additionally the script will only do this against databases that are currently ‘online’, so it will skip any databases that are being restored, that are offline. I usually just put this in a Job and run it every hour to aggressively manage file growth on the logs.

Hope you find this helpful.

use master;
set nocount on
declare @checkpoint_shrinkfile varchar(max)
set @checkpoint_shrinkfile = ''
select @checkpoint_shrinkfile = @checkpoint_shrinkfile +
'use [' + [master].sys.databases.name + ']; checkpoint; waitfor delay ''00:00:01'';' + char(10) + '
declare @recovery_type_' + cast([master].sys.databases.database_id as varchar) + ' varchar(50);' + char(10) + '
set @recovery_type_' + cast([master].sys.databases.database_id as varchar) + ' = ( select recovery_model_desc from master.sys.databases where database_id = ''' + cast([master].sys.databases.database_id as varchar) + ''' );' + char(10) + '
if @recovery_type_' + cast([master].sys.databases.database_id as varchar) + ' = ''FULL'' collate Latin1_General_CI_AS_KS_WS' + char(10) + ' begin ' + char(10) + '
dbcc shrinkfile (' + cast(sys.master_files.file_id as varchar) + ', 8)' + char(10) + 'print ''The shrinkfile operation has completed for Database [' + replace(db_name([master].sys.databases.database_id), '''', '') + ']''' + char(10) + '
end
else
print ''The shrinkfile operation is not required because the Recovery Model is SIMPLE on Database: [' + replace(db_name([master].sys.databases.database_id), '''', '') + ']'';' + char(10) + '' + char(10) + '' from
[master].sys.master_files join [master].sys.databases on
[master].sys.master_files.database_id = [master].sys.databases.database_id where
[master].sys.master_files.type_desc = 'log'
and [master].sys.databases.name not in ('master', 'model', 'msdb', 'tempdb') and [master].sys.databases.state_desc = 'online';
exec (@checkpoint_shrinkfile)
Advertisements

Categories: SQL Stuff

Tagged as: , , , ,

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s