Shrink data and log Files in small chunks.(DBCC)

DBCC shrink is not a good practice, it leads index fragmentation. However some cases DBA’S needs to shrink data or log files. The following script shrink into small chunks.

create table #tmpspc (Fileid int, FileGroup int, TotalExtents int, UsedExtents int, Name sysname, FileName nchar(520))
insert #tmpspc EXEC (‘dbcc showfilestats’)

–drop table #tmpspc

Declare @ExecSQL varchar(2000)
Declare @DataFileName varchar(2000)
Declare @FileSize int
Declare @AvailableSpace int
Declare @Increment int

Set @Increment = 200

—- Cursor declaration
DECLARE filedetail_cursor CURSOR FOR
SELECT
s.name AS [DataFileName],
Cast(((s.size * CONVERT(float,8))/1024) as int) AS [FileSize],
Cast(CAST(tspc.UsedExtents*convert(float,64) AS float)/(1024) as int) as AvailableSpace
FROM
sys.filegroups AS g
INNER JOIN sys.master_files AS s ON (s.type = 0 and s.database_id = db_id() and (s.drop_lsn IS NULL)) AND (s.data_space_id=g.data_space_id)
INNER JOIN #tmpspc tspc ON tspc.Fileid = s.file_id
WHERE
(CAST(cast(g.name as varbinary(256)) AS sysname)=N’PRIMARY’)
order by 3

OPEN filedetail_cursor

FETCH NEXT FROM filedetail_cursor INTO @DataFileName, @FileSize, @AvailableSpace

WHILE @@FETCH_STATUS = 0
BEGIN

While(@FileSize > @AvailableSpace)
Begin
Set @ExecSQL = ‘DBCC SHRINKFILE(‘ + cast(@DataFileName as varchar) + ‘, ‘ + cast((@FileSize-@Increment) as Varchar) + ‘)’
–EXEC(@ExecSQL)
Set @FileSize = @FileSize – @Increment
Print @ExecSQL
End

FETCH NEXT FROM filedetail_cursor INTO @DataFileName, @FileSize, @AvailableSpace
End

CLOSE filedetail_cursor
DEALLOCATE filedetail_cursor

drop table #tmpspc

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 )

Google+ photo

You are commenting using your Google+ 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 )

w

Connecting to %s