Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I'm working in an Enterprise system with a 1.5 terabyte database that is on a single filegroup, with the log in Simple Recovery Mode. Since we have many teams working on different projects in parallel, we have about 20 copies of this database in different environments as well as the main DEV, QA, Staging and Production copies.

For DEV, QA and other environments we do not need the full database so we developed a script to remove old data from the top 20 tables. This reclaims about 1 terabyte of free space. Across all environments we will save at least 15 terabytes of disk space on the SAN. I know this shrinking process causes index fragmentation - but don't mind rebuilding all indexes after the database has been shrunk.

We have two problems with Shrink database:

  1. It seems to take forever; more than 48 hours.

  2. It does not shrink the database as much as expected. The shrink process completes without error but leaves 100's of MB's of free space in the database.

I have tried:

DBCC SHRINKFILE ( 'XXXX_Data', NOTRUNCATE ) 
GO
DBCC SHRINKFILE ( 'XXXX_Data', TRUNCATEONLY )
GO 
DBCC SHRINKFILE ( 'XXXX_Log' ) GO

I have also tried creating a new filegroup and moving data to it - with no result.

Has anybody had to do this type of thing before on a similar sized database? Any recommendations on how best to do this? Or does anybody know why the database shrink does not work?

share|improve this question
    
One possibility I can think about is to move the tables you care about to a new data file and drop the one with unimportant tables. I am sure other folks on this forum will have better ideas, so lets wait for them. –  yogirk Sep 6 '12 at 9:49

4 Answers 4

There will always be some free space in the database, stored within pages that are too full for other data. Shrinking databases is an incredibly intensive operation, and will take a lot of time as it moves almost every record to somewhere new. A terabyte is a lot of data. Interestingly, you may have more luck creating space by applying compression such as the Hyperbac stuff from Red Gate. That can help remove the free space and compress the rest, but potentially doing fewer writes (and certainly not having to do lots of log activity) in the process. Your mileage may vary though - I don't want to try to speak authoritatively on how Hyperbac compression of your data will work without knowing much about your data. Hope this helps though...

share|improve this answer
    
Thanks - it does I had not heard of Hyperbac.. I'll take a look and pass on to DBA's –  Mel Sep 6 '12 at 10:10
    
I'm going to look at the SQL Virtual Restore feature from Hyperbac stuff from Red Gate. –  Mel Sep 6 '12 at 15:42
    
My initial feelings on Hyperbac & virtual restore are pretty good - It provides massive space saving and performance about 30-50% slower which is good enough for most dev senarios. Our large batch jobs failed to complete though - so we are still investigating. –  Mel Sep 18 '12 at 12:51

I have always found that trying to shrink a lot of space out of a data file in one go either takes an inexplicably long time (with low I/O on the files while the command is running) or completes after I've lost patience or blown through my timebox limit on when I can do the operation.

Instead, first get an idea of how many MB you will want to keep (that's going to be data space plus some extra to handle reindexing--if you shrink out all of the free space, it will be painful when SQL auto-grows the file back out during the reindex that you will have to run to fix the fragmentation).

Next, write a bunch of SHRINKFILE commands (to save time, you can use a text editor with a good macro or scripting facility or write a small script in tsql, powershell or vbscript) that will shrink the file in small chunks, maybe 1 GB or 500 MB at a time. Start with the current size of the file and slowly move down towards where you want the file to be.

As a dumb example, if you have a database file that is 500 GB and you want to shrink it to 400 GB, you would want to use statements more like:

DBCC SHRINKFILE ( 'XXXX_Data', 500000) -- shrink about 1 GB at a time
DBCC SHRINKFILE ( 'XXXX_Data', 499000)
DBCC SHRINKFILE ( 'XXXX_Data', 498000)

--- a lot more of these statements go here ---

DBCC SHRINKFILE ( 'XXXX_Data', 401000)
DBCC SHRINKFILE ( 'XXXX_Data', 400000)

--- OK, We are done so reindex the database ... ---

A small chunk should complete in a reasonable time. You may find that chunks of the same size can take wildly different times to complete. (Some might take a few seconds, others may take minutes.) Normally, until I get a good handle on how things actually behave, I will run a couple of these at a time. Once I have an understanding of how it behaves, I would wrap it up in a template script that I can reuse.

As you know, SHRINKFILE can cause index fragmentation. You should plan for reindexing before letting the developers into the database.

If you regularly move databases from prod to test, it should be possible to come up with a script that handles the shrinking and reindexing. I suggest you have a look at Ola Hallengren's reindexing stored procedure, which is very popular with DBAs.

share|improve this answer

Consider using a different approach which avoids shrinking. If the data you are deleting has an order, such as datetime, i wonder if you could partition the 20 tables into seperate files and filegroups. Then when you go to refresh the test databases you could drop the partitions and filegroups. No need to do any shrinking at all using this method.

It would require partitioning the production database first in order to get data into partitions. Shrinking the production database would also be necessary but would be a one time event.

Here is a short tutorial on partitioning: http://blog.sqlauthority.com/2008/01/25/sql-server-2005-database-table-partitioning-tutorial-how-to-horizontal-partition-database-table/

share|improve this answer

Regarding the time:

Moving 1 TB , in worst case scenario (all random) is going to cost you 6.4 hours on a 10k IOPS system. 1tb/8kb pages x 2 (one time reading Kone time writing. That could take a lot of time.

If you copy 20 x 1,5 TB why not consider replication? (replicating only the subset of data you need to a central database and copy that database to all your destinations)

It's automated, saves you 66% of the copy payload. And saves you the 20 TB reallocating by shrink and the 20x 0.5 rebuilding of indexes.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.