INCREMENTAL SHRINK
* SQL 2005 -- Shrink data or log files in set increments to a desired target size
*
* Description:
* Shrinking database files can be a very resource intensive task
* when dealing with large files. Performing a DBCC Shrinkfile in
* small increments should help. This script will automate this task.
* Set your desired target file size and set the increment size for
* each shrink operation. Change the increment size accordingly based
* on your database environment. Start with a small increment size of 128MB
* and see how it goes.
*
*
*
******************************
DECLARE @FileSize varchar(20),
@LogicalFile varchar(50),
@TargetFileSize int,
@NewFileSize varchar(20),
@IncrementSize int
---Logical name of Database file to shrink
SET @LogicalFile = 'DWAnalytics'
---This is the desired target size in MB after the shrink operation.
SET @TargetFileSize = 820375
---The increment amount in MB the file will shrink by in each pass (e.g. 128)
SET @IncrementSize = 1024
SET @FileSize = (SELECT [Size]/128 FROM dbo.Sysfiles WHERE [name] = @LogicalFile)
SET @NewFileSize = @FileSize -@IncrementSize
WHILE @FileSize > @TargetFileSize
BEGIN
BEGIN
EXEC ('DBCC SHRINKFILE ' + '(N' + '''' + @LogicalFile +'''' + ', ' + @NewFileSize + ')')
END
SET @FileSize = (SELECT [Size]/128 FROM dbo.Sysfiles WHERE [name] = @LogicalFile)
SET @NewFileSize = @FileSize -@IncrementSize
END
GO