Tag Archives: WhileLoop

Shrink a Data File

Normally, you would never want to shrink data files. Not even in case of emergency when running out of space. If you were to shrink the data file, the database would simply grow again and you end up with the same problem. If the database grew, it is because the database needed the space. If it is partially empty, it is because some data was removed, however, it is very likely that it would be needed again so it is a bad idea to shrink it again. If there was an error that caused the database to grow, or there was a change in process, or something extremely rare happened, you might need to shrink a database file. Since shrinking a database will hit performance and possible introduce some locks on the tables, if you must do it, it is better to do it little by little, using a script similar to this below. Remember, after the shrink is done, your indexes will be fragmented, so it is recommended to rebuild all your indexes (and hence the DB will grow a little)

In order to use this script, you should first find if there is space available on the data files, and find the logical name of the data file to shrink.

To do that, run this script first http://www.cookingsql.com/2016/01/data-file-size-and-free-space-one-database/ and use its output to run the script below.

 

/*
DESCRIPTION   Shrink a single DATA file, in increments.
It will loop to execute the DBCC SHRINKFILE command to shrink the database file by the desired increment
until it reaches the target free space.


CONFIGURATION    TOP.
Run this script in the database with the file to be shrunk.
1.  Make sure to set the USE statement for the correct database
2. Set @DBFileName  Enter the Logical File name (not the physical name)
3. Set @TargetFreeMB to the desired file free space in MB after shrink.
4. Set @ShrinkIncrementMB to the increment to shrink file by in MB

 

Compatibility list:
MSSQL2005
MSSQL2008/SQL2008R2
MSSQL2012
MSSQL2014 
 
*/
SET NOCOUNT ON


declare @DBFileName sysname
declare @TargetFreeMB int
declare @ShrinkIncrementMB int

/*------ CONFIGURATION ---------*/

--USE DATABASE 

-- Set Name of Database file to shrink
set @DBFileName = 'AdventureWorks2012_Data2'

-- Set Desired file free space in MB after shrink
-- Remember, it is not the target file size but how much empty space will be left after the shrink operation
-- @SizeMB = @TargetFreeMB + @usedSpace
set @TargetFreeMB = 1300

-- Set Increment to shrink file by in MB
-- Make sure the @TargetFreeMB + @ShrinkIncrement is bigger than @EmptyDB
set @ShrinkIncrementMB = 300

/*------ END CONFIGURATION ---------*/

-- Declare variables
declare @sql varchar(8000)
declare @SizeMB int
declare @EmptyMB int
declare @UsedMB int

-- Check Input Values

IF NOT EXISTS (select name from sysfiles where name = @DBFileName)
begin
print 'The Data File does not exist on the current database. You either did not set
the USE statement or did not specify the Logical File name correctly
Terminating Batch'
GOTO EOL
end

select
@SizeMB = CAST(size/128.0 AS int)
,@UsedMB = CAST(FILEPROPERTY(name, 'SpaceUsed' ) AS int)/128.0
,@EmptyMB = @SizeMB - @UsedMB
 
FROM sys.database_files
where name = @DBFileName



IF @EmptyMB < @TargetFreeMB
BEGIN
print 'Cannot proceed with operation because the target free space: ' +cast(@TargetFreeMB as nvarchar(10)) +'MB (user entry)
is bigger than the current free space: '+cast(@EmptyMB as nvarchar(10))+'MB
Terminating Batch'
GOTO EOL
END

IF @EmptyMB < @TargetFreeMB+@ShrinkIncrementMB
BEGIN
print 'Cannot proceed with operation because the Shrink Increment: ' +cast(@ShrinkIncrementMB as nvarchar(10)) +'MB (user entry)
Plus the Target Free '+cast(@TargetFreeMB as nvarchar(10))+'MB is bigger than the empty Space '+cast(@EmptyMB as nvarchar(10))+'MB
Terminating Batch'
GOTO EOL
END

 
declare @loopExit int
set @loopExit =  0


-- Loop until file at desired size
while  (@SizeMB > @UsedMB+@TargetFreeMB+@ShrinkIncrementMB) and (@loopExit < 10)
	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 at '+convert(varchar(30),getdate(),121)

		-- Sets again Original Values
		select
		@SizeMB = CAST(size/128.0 AS int)
		,@UsedMB = CAST(FILEPROPERTY(name, 'SpaceUsed' ) AS int)/128.0
		,@EmptyMB = @SizeMB - @UsedMB
		,@loopExit = @loopExit+1
		FROM sys.database_files
		where name = @DBFileName

	end
------------------------------------

IF (@loopExit = 10)
BEGIN
PRINT 'The SHRINK FILE operation completed 10 loops
This could mean that the file is not shrinking
or you need to increase the "shrink increment value"
Proceed with CAUTION
'
GOTO EOL
END

-- Show Size, Space Used, Unused Space, and Name of all database files


SELECT   
b.name
, b.type_desc
, CAST(b.size/128.0 AS int) 'SizeMB'  
, CAST(b.size/128.0 - CAST(FILEPROPERTY(b.name, 'SpaceUsed' ) AS int)/128.0 AS int) 'SpaceUsedMB'  
, ceiling(b.size/128.0 -CAST(FILEPROPERTY(b.name,'SpaceUsed' ) AS int)/128.0) 'FreeSpaceMB'
, CAST(ceiling(100 * (b.size/128.0 -CAST(FILEPROPERTY(b.name,'SpaceUsed' ) AS int)/128.0)/(b.size/128.0)) AS varchar(8)) + '%'  'FreeSpace'

,  CASE
	WHEN b.is_percent_growth = 0 THEN LTRIM(STR(b.growth * 8.0 / 1024,10,1)) + ' MB, '  
    ELSE 'By ' + CAST(b.growth AS VARCHAR) + ' percent, '
 END
 + CASE  
    WHEN b.max_size = -1 THEN 'unrestricted growth'
	ELSE 'restricted growth to ' + LTRIM(STR(b.max_size * 8.0 / 1024,10,1)) + ' MB'  
    END AS Autogrow
, cast(b.max_size/128.0 as int) 'MaxSize'
, physical_name
 
FROM sys.database_files b



EOL:


 

Generate script to backup all databases

This is a SCRIPT generator. It does not execute code.

The output can be used as a script to backup databases, enter into a SQL job to run backups, etc.

 

 /*
DESCRIPTION:
The following code generates a SQL script to do a FULL backup ALL databases to the specified location. It also creates a folder for each database on that location
CONFIGURATION
Just change the value for @LOCATION. example: SET @LOCATION = 'c:\temp'
If @EachFolder = 1 it makes a subfolder for each databases
If @EachFolder = 0 it does not. all BAK in same folder
Compatibility list: MSSQL2012 MSSQL2008 MSSQL2005
DOES NOT WORK MSSQL2000
*/
DECLARE @LOCATION VARCHAR(2000)
SET @LOCATION = 'F:\SQL Backups\BeforeUpgrade'
DECLARE @EachFolder BIT
-- If @EachFolder = 1 it makes a subfolder for each databases
-- If @EachFolder = 0 it does not. all BAK in same folder
SET @EachFolder = 0
-- ***** DO NOT MODIFY BELOW THIS LINE ********
DECLARE @mydb VARCHAR(2000)
DECLARE @rowcount INT
DECLARE @table table (rownum int identity(1,1),name sysname)
 
 
USE MASTER
PRINT 'DECLARE @loc VARCHAR(2000)'
PRINT 'set @loc = '''+@LOCATION+''''
PRINT 'DECLARE @mydb VARCHAR(2000)'
PRINT 'DECLARE @subdir VARCHAR(2000)'
PRINT 'DECLARE @sufixbk VARCHAR(20)'
PRINT 'DECLARE @full_bk_name VARCHAR(2000)'
PRINT 'DECLARE @bk_name VARCHAR(2000)'
PRINT 'DECLARE @msg VARCHAR(2000)'
PRINT 'DECLARE @backupSetId AS INT'
PRINT ''
 
-- Optional : you can modify the SELECT list for the while loop
-- This SELECT statement determines what databases to generate the backup scripts
 
INSERT INTO @table
SELECT name FROM sysdatabases WHERE VERSION is not null
and databasepropertyex(name,'Status') = 'online'
and name not in ('tempdb')
-- and name in ('database1','database2','database3')
 
SET @rowcount =1
WHILE @rowcount < = (select count(name) from @table)
BEGIN
 
SELECT @mydb = name from @table where rownum =@rowcount
 
 
PRINT '--*************** processing '+@mydb+' ************'
PRINT ' '
PRINT 'set @subdir = '''+ @mydb+''''
IF (@EachFolder=1) PRINT 'set @loc = @loc +''\'' +@subdir + ''\'''
PRINT 'set @sufixbk = CONVERT(VARCHAR(20),GETDATE(),112)'
PRINT 'set @bk_name = '''+@mydb+ '''+''_'' + @sufixbk + ''.bak'''
PRINT 'set @full_bk_name = @loc + ''\'' + @bk_name'
IF (@EachFolder=1) PRINT 'EXECUTE master.dbo.xp_create_subdir @loc'
PRINT 'BACKUP DATABASE ['+@mydb+'] TO DISK = @full_bk_name WITH NOFORMAT, NOINIT, NAME =@bk_name, SKIP, REWIND, NOUNLOAD, STATS = 10'
PRINT ',BUFFERCOUNT = 2200, BLOCKSIZE = 65536, MAXTRANSFERSIZE=2097152'
PRINT 'select @backupSetId = position from msdb..backupset where database_name='''+@mydb+''' and backup_set_id=(select max(backup_set_id) from msdb..backupset where database_name='''+@mydb+''')'
PRINT 'set @msg =''Verify failed. Backup information for database ['+@mydb+'] not found.'''
PRINT 'if @backupSetId is null begin raiserror(@msg, 16, 1) end'
PRINT 'RESTORE VERIFYONLY FROM DISK = @full_bk_name WITH FILE = @backupSetId, NOUNLOAD, NOREWIND'
PRINT ' '
 
SET @rowcount = @rowcount+1
END