Agent Jobs

Quickly Shrink All SQL Data Files

Here’s some quick SQL Logic that will shrink all SQL Data Files but ignore Filestream Data Files, and FullText Data Files.
This will also ONLY affect databases that are currently ONLINE and ignore Databases that are presently set as the Mirror in database mirroring.

 use master;
set nocount on

declare @shrinkfiles	varchar(max)
set	@shrinkfiles	= ''
select	@shrinkfiles	= @shrinkfiles +
'use [' + sd.name + '];' + char(10) +
'dbcc shrinkfile (' + cast(smf.file_id as varchar(3)) + ');' + char(10) from	sys.databases sd
join sys.database_mirroring sdm on sd.database_id = sdm.database_id join sys.master_files smf on sd.database_id = smf.database_id where	sd.database_id > 4
and	sd.state_desc = 'online'
and	sdm.mirroring_role_desc is null
or	sdm.mirroring_role_desc != 'mirror'
and	smf.type < 3
order by
sd.database_id asc
exec	(@shrinkfiles)
Advertisements

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