Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

SQLTechnet

Vinoth is currently a Senior SQL Server DBA and 10 years of experience as SQL Server DBA. Started my career as a DBA in SQL 6.5/7 has worked in all subsequent SQL Server version. Vinoth has worked in some of the largest SQL server environments in the world in various domains ranging from Finance, Retail, Manufacturing, Consulting, Web etc. Vinoth has Engineering Degree in Computer Science and has certified in MCITP - Database Adminstrator in 2008/2005, MCDBA and ITIL Foundation V3.

SQL database Incremental Shrink TSQL


When we shrink a SQL database through GUI we usually find the database never releases all the free space, It is always a good practice particularly in Production OLTP systems to shrink the database in small chunks so the data databse pages can be re-arranged and can free all unused space. The below TSQL will allow  shrink a database file in increments until it reaches a target free space limit.

/*
This script is used to shrink a database file in
increments until it reaches a target free space limit.


Run this script in the database with the file to be shrunk.
1. Set @DBFileName to the name of database file to shrink.
2. Set @TargetFreeMB to the desired file free space in MB after shrink.
3. Set @ShrinkIncrementMB to the increment to shrink file by in MB
4. Run the script
*/
set nocount on
declare @DBFileName sysname
declare @TargetFreeMB int
declare @ShrinkIncrementMBint
-- Set Name of Database file to shrink
set @DBFileName = ''
-- Set Desired file free space in MB after shrink
set @TargetFreeMB = 0
-- Set Increment to shrink file by in MB
set @ShrinkIncrementMB= 100
-- Show Size, Space Used, Unused Space, and Name of all database files
select [FileSizeMB] = convert(numeric(10,2),round(a.size/128.,2)),
       [UsedSpaceMB]= convert(numeric(10,2),round(fileproperty( a.name,'SpaceUsed')/128.,2)) ,
       [UnusedSpaceMB]= convert(numeric(10,2),round((a.size-fileproperty( a.name,'SpaceUsed'))/128.,2)) ,
       [DBFileName]= a.name
from sysfiles a
declare @sql varchar(8000)
declare @SizeMB float
declare @UsedMB float


-- Get current file size in MB
select @SizeMB = size/128. from sysfiles where name = @DBFileName
-- Get current space used in MB
select @UsedMB = fileproperty( @DBFileName,'SpaceUsed')/128.0
-- Loop until file at desired size
while  @SizeMB > @UsedMB+@TargetFreeMB+@ShrinkIncrementMB
begin
 set @sql = 'dbcc shrinkfile ( '+@DBFileName+', ' + convert(varchar(20),@SizeMB-@ShrinkIncrementMB)+' ) WITH NO_INFOMSGS'
 print 'Start ' + @sql + ' at ' + convert(varchar(30),getdate(),121)
 exec ( @sql )
 print 'Done ' + @sql + ' at '+convert(varchar(30),getdate(),121)
 -- Get current file size in MB
 select @SizeMB = size/128. from sysfiles where name = @DBFileName
 -- Get current space used in MB
 select @UsedMB = fileproperty( @DBFileName,'SpaceUsed')/128.0
 print 'SizeMB=' + convert(varchar(20),@SizeMB) + ' UsedMB=' + convert(varchar(20),@UsedMB)
end
select [EndFileSize] = @SizeMB, [EndUsedSpace] = @UsedMB, [DBFileName] = @DBFileName

Comments

Leave a comment on the original post [www.sqltechnet.com, opens in a new window]

Loading comments...