Questions About / Problems(?) With SHRINKDATABASE and SHRINKFILE Command Behavior

Question

Questions About / Problems(?) With SHRINKDATABASE and SHRINKFILE Command Behavior

Gail Wanabee

SSCrazy Eights

Points: 8471
More actions
August 12, 2009 at 3:26 pm

#137253

Using SQL Server 2005 Standard Edition.
I need to reduce the size of a large database. It's current statistics are:
* Total Space Usage: 1,321,776 MB
* Data Files Space Usage: 1,320,650 MB
* Transaction Log Space Used: 1,126 MB
Data Files Space Usage:
* 65% free
* 8% index
* 27% data
Transaction Log Space Usage:
* 82% free
* 18% free
Primary File 1:
* 996 GB space reserved
* 188 GB space used
Primary File 2:
* 293 GB space reserved
* 259 GB space used
Goal: Reduce database and log files physical sizes, then manually regrow them in large increments.
Reason: Significant physical fragmentation due to inadvertant choice of small Auto-Growth setting in the past.
Problem: Unexpected behavior of the SHRINKDATABASE and SHRINKFILE Commands.
Specific Problems:
DBCC SHRINKDATABASE( 'Database Name', TRUNCATEONLY ); - Reduced database and log file sizes; ran for a short while.
DBCC SHRINKDATABASE( 'Database Name', NOTRUNCATE ); - Ran for 5 hours. Stopped. No change in statistics.
DBCC SHRINKDATABASE( 'Database Name' ); - Ran for 17 hours. Stopped. No change in statistics.
DBCC SHRINKFILE( 1, 200 ); - Ran for 1 hour. Stopped. No change in statistics.
DBCC SHRINKFILE( 3, 260 ); - Ran for 1 hour. Stopped. No change in statistics.
________________
I have not worked on a database this large before nor have I worked on one with more than one database file.
Questions:
1. What behavior should I expect from these commands in terms of execution time and anticipated results?
2. Do I need to perform any preparatory work to obtain the best results?
3. Is there a question I haven't asked, whose answer would allow me to achieve the desired results?
Thanking you in advance,
LC

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

JeremyE SSCoach Points: 16123 More actions · Answer 1

Try shrinking in smaller increments. It's going to take a while (hours) to shrink a data file while the data pages are moved.

When using DBCC SHRINKFILE( 1, 200 ); It tells me you are trying to shrink a 996GB datafile down to 200MB.

Try shrinking in increments of like 10-20GB. Right now datafile 1 is 1,019,904MB. I would try

DBCC SHRINKFILE(1, 999424) --shrinks the file 20GB

GO

DBCC SHRINKFILE(1, 978944) --shrinks the file another 20GB

GO

So on and so forth until the file is shrunk to your desired size. Keep in mind this is going to fragment your indexes and they will need to be rebuilt after the shrink.

Gail Wanabee SSCrazy Eights Points: 8471 More actions · Answer 2

Thanks for your response. I'm not sure why what you suggested will work but I'll do it.

And thanks for the reminder about fragmented indexes. I intend to rebuild all of them.

The database I'm working on has been neglected for a long time. As the company's first DBA, I've been putting out fires for over 6 weeks. The shrinkage and index rebuilding are the last fires to extinguish before I implement a maintenance plan to permanently maintain the data, the database, and the server.

LC