Shrinking Database Files

Shrinking Database Files
I hate shrinking database files. I hadn’t shrunk files in years for all the good reasons you can read about with a simple google search. Recently, I had the opportunity to do some shrinking.

I had a database that contained multiple file groups for performance and backup reasons. The database is nearly ½ terabyte. All of the sudden the system had an un-expected and rather large increase in inserts, and one of the files in the filegroup grew to utilize the entire data drive. However, one of the other files in the file group had 50 gig free.

Getting new space allocated takes a while. Shrinking database files may be quicker. So, we started up a shrink command. Guess what, when a database is really busy, shrinking data files might take a while; a long while.

When it gets to midnight I’m ready for bed, not waiting for a file to shrink. My co-worker comes up with this very useful system views query to help us predict progress for shrinking the file. It turns out the query was rather accurate in our case.

SELECT
percent_complete,
start_time,
status,
command,
estimated_completion_time,
cpu_time,
total_elapsed_time
FROM
sys.dm_exec_requests
WHERE
command = 'DbccFilesCompact'

Another query I ran from time to time follows. We would run this query to determine if the process we were watching so closely was being blocked by any other processes.

select *
from Master.dbo.sysprocesses with (NOLOCK)
where hostname = 'YourServerName'

The moral of the story is:

1. Don’t shrink databases unless absolutely necessary
2. Shrinking database files is very intensive and can take some time. If you have to do it, be prepared to wait.
3. Don’t Cancel as shrink comamnd.
4. Try to predict, and allocate disk space before it is required.

Cheers,

Ben

$$SWYNK$$

Featured White Paper(s)
Ten Tips for Optimizing SQL Server Performance
read more)