December 16, 2009 at 5:13 am
Disk space cost isn't really relevant because of the simple reason that if the database was once 45Gb (or whatever) then it'll probably need to be that size again at some point. The only time I would say a shrink might be useful is if you had some sort of database problem that caused it to grow much larger than it should have, which would fall into that "one-off issues" category which has already been mentioned. Shrinking is certainly not something that needs to be done on a regular basis, whether that's every week or every year.
December 16, 2009 at 5:49 am
It would depend on specifics of the app and server, but I'd say 6 months, maybe. Not just one month though. More than that.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 16, 2009 at 7:44 am
I’m not saying what we’re doing is the best approach, but I work at a large shop so I thought I’d chime in and let you know what we’re doing just to give you another perspective.
We’ve got well over 10,000 SQL databases. We don’t regularly shrink any databases. The only time we shrink a database is if we need to (like if the drive is full and we need to reclaim some disk space). We’ve got monitoring on all our servers (as I’d presume you probably do too), and we get alerts if a disk is full. And we’ve got monitors/reports to help us monitor growth so we can get more disk space added to a server when/if necessary.
I hear what you’re saying about “Why pay for unallocated space”, but on the flip-side, why pay for unused disk? If you shrink a database then you give that back to the OS. Either the OS has the free/unused space, or the database has the free/unnallocated space.
As you said, in a large shop you can’t closely monitor every database. I guess our approach is that if the drive isn’t full, let the database take the space they want/need.
December 16, 2009 at 7:57 am
For the archival or removal of old data, horizontal partitioning w/ a sliding window should be considered as well -
Tommy
Follow @sqlscribeDecember 16, 2009 at 11:33 am
El RoboCopo (12/16/2009)
One-off SHRINKING IS NOT POSSIBLE with more than 6000 databases.
Why not? If you've done an unusual archive/data deletion on a database, you'd know about it and could consider shrinking after. What we've been saying is that you don't want to shrink regularly/based on an algorithm.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 31, 2009 at 12:38 am
I don't do any application data deletion and don't know when that happens so I run this code once a month. So far I see no excessive file allocation, but I will track this for the future. I still think that the fear of using dbcc shrinkfile is based on to little knowledge. This is the code run once a mont (checks for too much unallocated space) followed immediately by a index reorganization which is the trick used to get a low portion of unallocated space:
CREATE PROCEDURE dbo.spX_ShrinkFiles
@workdb VARCHAR(100) = '' -- #param @workdb The database you want to do DBCC SHRINKFILE ON. If blank, all databases will be shrunk.
,@targetsize INT = 50 -- #param @targetsize Means set a certain target database size..
,@freedisk VARCHAR(4) = '0.20' -- #param @freedisk Means if free space any database disk is less than e.g. 0.20 = 20% free.
AS
-- Use this SP to shrink database files
-- #desc This SP is used to run DBCC SHRINKFILE in on one or all databases. Note: The database being shrunk does not have to be in single-user mode; other users can be working in the database when the file is shrunk. You do not have to run the instance of SQL Server in single-user mode to shrink the system databases.
-- #author Lennart Gerdvall, 2009-12-08
-- #version 1.0, only SQL 2005 and 2008
/*
EXEC Filer.dbo.spX_ShrinkFiles
@workdb = 'Mailmarshal'
,@targetsize = 50
,@freedisk = '0.20'
*/
DECLARE
@databasename VARCHAR(100)
,@cmdexec NVARCHAR(4000)
,@sqlcommand nvarchar(4000)
,@dbmode VARCHAR(50)
,@statusmsg NVARCHAR(1000)
,@statussub NVARCHAR(200)
,@myjobtime VARCHAR(24)
,@mystrlen INT
,@myedition VARCHAR(50)
,@snapshotok INT
,@return INT
,@snapshotcreated INT
,@myerrno INT
,@myseverity INT
,@myerrmess NVARCHAR(2048)
,@fullerrmess NVARCHAR(3000)
,@mejlmess NVARCHAR(3000)
,@catchsubject NVARCHAR(500)
,@statusmess NVARCHAR(3000)
,@versioncode INT
,@alladdresses NVARCHAR(1024)
,@myrecipients VARCHAR(100)
,@mycurrentaddres VARCHAR(1024)
,@datapath varchar(256)
,@current_database_file_id INT
,@ss_stamp varchar(12)
,@ss_db NVARCHAR(300)
,@lowdisk bit
,@snapshoterrmess NVARCHAR(300)
,@sys_usr CHAR(30)
,@usermess VARCHAR(200)
,@testcolpos INT
,@testslash VARCHAR(2)
,@lengthofshare INT
,@eofshare varchar(200)
,@lenshare int
,@slashpos int
,@errdatadisk VARCHAR(100)
,@published bit
,@subscribed bit
,@merged bit
,@hours int
,@minutes int
,@seconds int
,@startdate datetime
,@enddate datetime
,@startdateall datetime
,@timer nvarchar(100)
,@finishmess nvarchar(300)
,@cmd VARCHAR(500)
,@fileidcount INT
,@partitioncount INT
,@maxiteration INT
,@maxdatabasefiles INT
,@databasefilecounter INT
,@total_extents INT
,@used_extents INT
,@regain DECIMAL(5,2)
,@usagecount INT
,@partschema INT
,@parm_my_ps_schema NVARCHAR(500)
,@sqlmypartition NVARCHAR(1000)
,@sparsedb INT
,@sparse_schema NVARCHAR(500)
,@sqlsparsecheck NVARCHAR(1000)
SET NOCOUNT ON
SET DEADLOCK_PRIORITY LOW
-- Check if we have the right SQL version
------------------------------------------------------------------------------------------------------
EXEC Filer.dbo.spF_check_sqlversion @versioncode OUTPUT
IF @versioncode <= 8
BEGIN
RAISERROR ('This SP is not written for SQL 2000 and earlier.',16,1);
RETURN
END
-- Starting date for jobb
------------------------------------------------------------------------------------------------------
SET @startdateall = getdate()
-- Get mail addresses
------------------------------------------------------------------------------------------------------
SET @alladdresses = N''
DECLARE MAILResults_CURSOR CURSOR FORWARD_ONLY READ_ONLY FOR
SELECT email_address FROM msdb.dbo.sysoperators WITH (NOLOCK) where email_address IS NOT NULL
OPEN MAILResults_CURSOR
FETCH NEXT FROM MAILResults_CURSOR INTO @myrecipients
WHILE @@FETCH_STATUS = 0
BEGIN
SET @mycurrentaddres = @myrecipients + CHAR(59)
SET @alladdresses = @alladdresses + @mycurrentaddres
FETCH NEXT FROM MAILResults_CURSOR INTO @myrecipients
END
CLOSE MAILResults_CURSOR
DEALLOCATE MAILResults_CURSOR
IF @alladdresses = N'' SET @alladdresses = N'dba@payex.com'
-- Check if low disk space - mail and return if low
------------------------------------------------------------------------------------------------------
EXEC Filer.dbo.spX_check_disks @limit=@freedisk,@upddisks=1,@lowdisk=@lowdisk OUTPUT
IF @lowdisk <> 0
BEGIN
SET @catchsubject = N'Message from ' + @@SERVERNAME + N' when running DBCC SHRINKFILE'
SET @statusmess = N'DBCC SHRINKFILE ' + N' on server ' + @@SERVERNAME + + N' could not be performed due to low disk space.'
EXEC msdb.dbo.sp_send_dbmail
@profile_name = NULL
,@recipients = @alladdresses
,@copy_recipients = NULL
,@blind_copy_recipients = NULL
,@subject = @catchsubject
,@body = @statusmess
,@body_format = 'TEXT'
,@importance = 'Normal'
,@sensitivity = 'Normal'
,@file_attachments = NULL
,@query = NULL
,@execute_query_database = 'Filer'
,@attach_query_result_as_file = 0
,@query_attachment_filename = NULL
,@query_result_header = 0
,@query_result_width = 256
,@query_result_separator = ' '
,@exclude_query_output = 0
,@append_query_error = 1
,@query_no_truncate = 0
RETURN
END
-- Check filegroups for [Fileid] using DBCC SHOWFILESTATS
------------------------------------------------------------------------------------------------------
CREATE TABLE #databasefiles
(
[Fileid] INT NOT NULL,
[FileGroup] INT NOT NULL,
[TotalExtents] INT NOT NULL,
[UsedExtents] INT NOT NULL,
[Name] VARCHAR(200) NOT NULL,
[FileName] VARCHAR(200) NOT NULL
CONSTRAINT [PK_MyFiles]
PRIMARY KEY CLUSTERED ([Fileid])
WITH (IGNORE_DUP_KEY = OFF)
);
-- Print job headline
------------------------------------------------------------------------------------------------------
SET @sys_usr = SYSTEM_USER;
SET @usermess = 'Started database shrinking using DBCC SHRINKFILE on ' + @@SERVERNAME + ' at ' + convert(varchar(24), getdate(),120) + '. '
SET @usermess = @usermess + 'Current system user is ' + RTRIM(@sys_usr)
SET @mystrlen = len(@usermess)
PRINT @usermess
PRINT REPLICATE ('-', @mystrlen)
-- A particular database may be specified, else all databases will be processed
----------------------------------------------------------------------------------------------------------
IF @workdb <> ''
DECLARE SHRINKFILE_CURSOR CURSOR READ_ONLY FOR
SELECT [name] FROM sys.databases WITH (NOLOCK) WHERE [name] = @workdb
ELSE
DECLARE SHRINKFILE_CURSOR CURSOR READ_ONLY FOR
SELECT [name] FROM sys.databases WITH (NOLOCK) WHERE [name] NOT IN ('master','model','msdb','tempdb') ORDER BY [name] ASC
OPEN SHRINKFILE_CURSOR
FETCH NEXT FROM SHRINKFILE_CURSOR INTO @databasename
WHILE @@FETCH_STATUS=0
BEGIN
-- Set startdate for this database
------------------------------------------------------------------------------------------------------
SET @startdate = getdate()
-- Clear any previous data
------------------------------------------------------------------------------------------------------
TRUNCATE TABLE #databasefiles
-- Check if low disk
------------------------------------------------------------------------------------------------------
EXEC Filer.dbo.spX_check_disks @limit=@freedisk,@upddisks=0,@lowdisk=@lowdisk OUTPUT
IF @lowdisk <> 0
BEGIN
SET @catchsubject = N'Message from ' + @@SERVERNAME + N' when running DBCC SHRINKFILE'
SET @statusmess = N'Running DBCC SHRINKFILE on database ' + CHAR(39) + @databasename + CHAR(39) + N' on server ' + @@SERVERNAME + + N' aborted due to low disk space.'
EXEC msdb.dbo.sp_send_dbmail
@profile_name = NULL
,@recipients = @alladdresses
,@copy_recipients = NULL
,@blind_copy_recipients = NULL
,@subject = @catchsubject
,@body = @statusmess
,@body_format = 'TEXT'
,@importance = 'Normal'
,@sensitivity = 'Normal'
,@file_attachments = NULL
,@query = NULL
,@execute_query_database = 'Filer'
,@attach_query_result_as_file = 0
,@query_attachment_filename = NULL
,@query_result_header = 0
,@query_result_width = 256
,@query_result_separator = ' '
,@exclude_query_output = 0
,@append_query_error = 1
,@query_no_truncate = 0
RETURN
END
--Check Database Accessibility
------------------------------------------------------------------------------------------------------
SELECT @dbmode = 'OK'
IF (DATABASEPROPERTYEX(@databasename, 'Status') = N'ONLINE'
AND DATABASEPROPERTYEX(@databasename, 'Updateability') = N'READ_WRITE'
AND DATABASEPROPERTYEX(@databasename, 'UserAccess') = N'MULTI_USER')
SET @dbmode = 'OK'
ELSE SET @dbmode = 'NOT AVAILABLE'
IF @dbmode <> 'OK'
BEGIN
SET @statussub = N'Database could not be checked on ' + @@SERVERNAME
SET @statusmsg = N'Unable to check database health for ' + @databasename + N' using DBCC CHECKDB '
+ N' - database is in ' + @dbmode + N' state.'
PRINT @statusmsg
EXEC msdb.dbo.sp_send_dbmail
@profile_name = NULL
,@recipients = @alladdresses
,@copy_recipients = NULL
,@blind_copy_recipients = NULL
,@subject = @statussub
,@body = @statusmsg
,@body_format = 'TEXT'
,@importance = 'Normal'
,@sensitivity = 'Normal'
,@file_attachments = NULL
,@query = NULL
,@execute_query_database = 'Filer'
,@attach_query_result_as_file = 0
,@query_attachment_filename = NULL
,@query_result_header = 0
,@query_result_width = 256
,@query_result_separator = ' '
,@exclude_query_output = 0
,@append_query_error = 1
,@query_no_truncate = 0
GOTO NEXTDB
END
ELSE
BEGIN
-- Init counters
------------------------------------------------------------------------------------------------------
SET @fileidcount = 0
SET @partitioncount = 0
SET @maxdatabasefiles = 0
SET @databasefilecounter = 0
SET @regain = 0.00
SET @usagecount = 0
SET @partschema = 0
-- Check if this is a sparse database or not
------------------------------------------------------------------------------------------------------
SET @sparsedb = 0
SET @sparse_schema = N'@sparseflag INT OUTPUT '
SET @sqlsparsecheck = N'SELECT @sparseflag = COUNT(*) '
+ N' FROM [' + @databasename + '].sys.database_files '
+ N' WHERE is_sparse = ' + CAST(1 AS CHAR(1))
EXECUTE sp_executesql @sqlsparsecheck, @sparse_schema, @sparseflag = @sparsedb OUTPUT
-- This is not a sparse database - go to next database.
IF @sparsedb >= 1 GOTO NEXTDB
-- Get files data for current database
------------------------------------------------------------------------------------------------------
SET @cmd = 'USE [' + @databasename + ']; DBCC SHOWFILESTATS WITH TABLERESULTS;'
INSERT INTO #databasefiles
(
[Fileid]
,[FileGroup]
,[TotalExtents]
,[UsedExtents]
,[Name]
,[FileName]
)
EXEC(@cmd)
-- Check if this is a Usage* database. If so, it is considered to be a partitioned database.
------------------------------------------------------------------------------------------------------
SELECT @usagecount = COUNT([Name]) FROM #databasefiles WHERE [Name] LIKE 'Usage%'
-- Check if this is a database with a partition schema
------------------------------------------------------------------------------------------------------
SET @parm_my_ps_schema = N'@pmypartitionflag INT OUTPUT'
SET @sqlmypartition = N'SELECT @pmypartitionflag = COUNT(*) '
+ N' FROM [' + @databasename + '].sys.data_spaces '
+ N' WHERE [type] = ' + CHAR(39) + 'PS' + CHAR(39)
EXECUTE sp_executesql @sqlmypartition, @parm_my_ps_schema, @pmypartitionflag = @partschema OUTPUT
-- Decide how many files to run DBCC SHRINKFILE(nn,50) on!
------------------------------------------------------------------------------------------------------
SELECT @fileidcount = COUNT([Fileid]) FROM #databasefiles
-- PRINT '# of database files in database ' + @databasename + ' is ' + CAST(@fileidcount AS VARCHAR(20)) + '...'
-- If this is a Usage-database OR has a PARTITION_SCHEME, then it is considered to be a PARTITIONED database.
IF (@usagecount >= 5 OR @partschema >= 1)
BEGIN
IF @fileidcount >= 5 SET @maxdatabasefiles = @fileidcount - 4 ELSE SET @maxdatabasefiles = @fileidcount
PRINT 'Shrinking ' + CAST(@maxdatabasefiles AS VARCHAR(10)) + ' files (of a total of ' + CAST(@fileidcount AS VARCHAR(10)) + ') for the PARTITIONED database ' + @databasename + '...'
END
ELSE
BEGIN
SET @maxdatabasefiles = @fileidcount
PRINT 'Shrinking ' + CAST(@maxdatabasefiles AS VARCHAR(10)) + ' files for the STANDARD database ' + @databasename + '...'
END
-- Process the database files
------------------------------------------------------------------------------------------------------
DECLARE DB_Files CURSOR READ_ONLY FOR
SELECT [Fileid],[TotalExtents],[UsedExtents] FROM #databasefiles
OPEN DB_Files
FETCH NEXT FROM DB_Files INTO @current_database_file_id,@total_extents,@used_extents
WHILE @@fetch_status = 0
BEGIN
-- Skip small files
IF @total_extents < 2000
BEGIN
PRINT 'Skipping file with ID ' + CAST(@current_database_file_id AS VARCHAR(10)) + ', total extents allocated is < 2000 (' + CAST(@total_extents AS VARCHAR(50)) + ' is allocated)...'
GOTO NEXTDATABASEFILE
END
-- If there is not a lot of space to regain by shrinking a non-partitioned file, skip that file.
SELECT @regain = CAST(@used_extents AS DECIMAL(30,2)) / CAST(@total_extents AS DECIMAL(30,0))
IF @partschema = 0
BEGIN
IF @regain > 0.80
BEGIN
PRINT 'Skipping non-partitioned file with ID ' + CAST(@current_database_file_id AS VARCHAR(10)) + ', there is less than ' + cast(cast((@regain * 100) as INT) as varchar(10)) + '% space to regain (' + CAST(CAST((@regain * 100) AS INT) AS VARCHAR(10)) + '% is used)...'
GOTO NEXTDATABASEFILE
END
END
ELSE
BEGIN
IF @regain > 0.90
BEGIN
PRINT 'Skipping partitioned file with ID ' + CAST(@current_database_file_id AS VARCHAR(10)) + ', there is less than ' + cast(cast((@regain * 100) as INT) as varchar(10)) + '% space to regain (' + CAST(CAST((@regain * 100) AS INT) AS VARCHAR(10)) + '% is used)...'
GOTO NEXTDATABASEFILE
END
END
SET @sqlcommand = N'USE ' + @databasename + N'; DBCC SHRINKFILE (' + CAST(@current_database_file_id AS VARCHAR(10)) + N',' + CAST(@targetsize AS VARCHAR(10)) + N');'
PRINT 'Shrinking database file with ID ' + CAST(@current_database_file_id AS VARCHAR(10)) + ' in database ' + @databasename + ' with command ' + @sqlcommand + '...'
BEGIN TRY
EXEC @return = sp_executesql @sqlcommand
END TRY
BEGIN CATCH
SELECT
@myerrno = ERROR_NUMBER(),
@myseverity = ERROR_SEVERITY(),
@myerrmess = ERROR_MESSAGE();
SET @statusmess = N'Shrinking of database files in database ' + CHAR(39) + @databasename + CHAR(39) + N' on server ' + @@SERVERNAME + + N' has failed.'
SET @catchsubject = N'Message from ' + @@SERVERNAME + N' when running DBCC SHRINKFILE'
SET @fullerrmess = N'Error: ' + CAST(@myerrno AS NVARCHAR(10)) + N', severity: ' + CAST(@myseverity AS NVARCHAR(10)) + N', message: ' + @myerrmess + N'.'
SET @mejlmess = @statusmess + CHAR(13) + @fullerrmess + CHAR(13)
-- Mail on error
EXEC msdb.dbo.sp_send_dbmail
@profile_name = NULL
,@recipients = @alladdresses
,@copy_recipients = NULL
,@blind_copy_recipients = NULL
,@subject = @catchsubject
,@body = @mejlmess
,@body_format = 'TEXT'
,@importance = 'Normal'
,@sensitivity = 'Normal'
,@file_attachments = NULL
,@query = NULL
,@execute_query_database = 'Filer'
,@attach_query_result_as_file = 0
,@query_attachment_filename = NULL
,@query_result_header = 0
,@query_result_width = 256
,@query_result_separator = ' '
,@exclude_query_output = 0
,@append_query_error = 1
,@query_no_truncate = 0
END CATCH
NEXTDATABASEFILE:
SET @databasefilecounter = @databasefilecounter + 1
IF @databasefilecounter >= @maxdatabasefiles GOTO DBREADY
FETCH NEXT FROM DB_Files INTO @current_database_file_id,@total_extents,@used_extents
END
DBREADY:
CLOSE DB_Files
DEALLOCATE DB_Files
-- Print ending message for database
------------------------------------------------------------------------------------------------------
SET @enddate = getdate()
SET @hours = floor(DATEDIFF(ss,@startdate,@enddate)/3600)
SET @minutes = floor(DATEDIFF(ss,@startdate,@enddate)/60)- floor(DATEDIFF(ss,@startdate,@enddate)/3600)*60
SET @seconds = floor(DATEDIFF(ss,@startdate,@enddate))- floor(DATEDIFF(ss,@startdate,@enddate)/60)*60
IF (@hours IS NULL OR @hours < 1) SET @hours = 0
IF (@minutes IS NULL OR @minutes < 1) SET @minutes = 0
IF (@seconds IS NULL OR @seconds < 1) SET @seconds = 1
SET @timer = N''
IF @hours > 0 SET @timer = CAST(@hours AS VARCHAR(2)) + N' hour(s), '
IF @minutes > 0 SET @timer = @timer + CAST(@minutes AS VARCHAR(2)) + N' minute(s), '
IF @seconds > 0 SET @timer = @timer + CAST(@seconds AS VARCHAR(2)) + N' second(s)'
SET @myjobtime = convert(varchar(24), getdate(),120)
SELECT @statusmsg = N'Finished checking database ' + @databasename + N' at ' + @myjobtime + N' using ' + @timer
+ N' - status is ' + @dbmode
+ N': ' + CAST(DATABASEPROPERTYEX(@databasename, 'Status') AS VARCHAR(20))
+ N', ' + CAST(DATABASEPROPERTYEX(@databasename, 'Updateability') AS VARCHAR(20))
+ N', ' + CAST(DATABASEPROPERTYEX(@databasename, 'UserAccess') AS VARCHAR(20)) + N'...'
PRINT @statusmsg
PRINT ''
END
NEXTDB:
FETCH NEXT FROM SHRINKFILE_CURSOR INTO @databasename
END
-- Print finish message and clean up
------------------------------------------------------------------------------------------------------
CLOSE SHRINKFILE_CURSOR
DEALLOCATE SHRINKFILE_CURSOR
SET @enddate = getdate()
SET @hours = floor(DATEDIFF(ss,@startdateall,@enddate)/3600)
SET @minutes = floor(DATEDIFF(ss,@startdateall,@enddate)/60)- floor(DATEDIFF(ss,@startdateall,@enddate)/3600)*60
SET @seconds = floor(DATEDIFF(ss,@startdateall,@enddate))- floor(DATEDIFF(ss,@startdateall,@enddate)/60)*60
IF (@hours IS NULL OR @hours < 1) SET @hours = 0
IF (@minutes IS NULL OR @minutes < 1) SET @minutes = 0
IF (@seconds IS NULL OR @seconds < 1) SET @seconds = 1
SET @timer = N''
IF @hours > 0 SET @timer = CAST(@hours AS VARCHAR(2)) + N' hour(s), '
IF @minutes > 0 SET @timer = @timer + CAST(@minutes AS VARCHAR(2)) + N' minute(s), '
IF @seconds > 0 SET @timer = @timer + CAST(@seconds AS VARCHAR(2)) + N' second(s)'
SET @finishmess = N'Finished shrinking database using DBCC SHRINKFILE on ' + @@SERVERNAME + N' at ' + convert(varchar(24), getdate(),120) + N' using ' + @timer + N'!'
SET @mystrlen = len(@finishmess)
PRINT @finishmess
PRINT REPLICATE ('-', @mystrlen);
Lennart Gerdvall
payex.com
December 31, 2009 at 10:03 pm
Simply making much better choices as to what the clustered index is (ie. temporal according to inserts) will keep much of this type of data growth from occuring. Regular index maintenance will help a lot as well.
--Jeff Moden
May 23, 2010 at 8:23 am
After adding 48tb of storage, the size of the database is no longer an issue
Viewing 9 posts - 16 through 23 (of 23 total)
You must be logged in to reply to this topic. Login to reply