Shrink a database below its initial size

Question

I've got a SQL server 2005 dev database that is a 30GB copy of live. We have deleted some data that is not needed in dev, which brings the data file space used down to 20GB. So we have about 33% unused.

I need to reclaim the space, which will allow us to have a second dev DB on the server (based on the cut down version); however, I cannot reclaim the space, I have done the following:

• The initial size of the file SMS2_Data is 30GB.

  DBCC SHRINKFILE (N'SMS2_Data' , 0, TRUNCATEONLY)
  

  followed by

  DBCC SHRINKFILE (N'SMS2_Data' , 19500)
  

No joy. I have tried making a backup, creating a new DB with a low initial size then restoring, no joy as the initial size gets overwritten. Have also tried:

ALTER DATABASE SMS2HazSub MODIFY FILE (NAME = 'SMS2_Data', SIZE = 20000) 

This errored, saying:

MODIFY FILE failed. Specified size is less than current size.

I tried 20800 and then kept going up til 29000 (29GB) and it still will not let me change it.

Have done the shrink then changed the recovery mode from FULL to SIMPLE and back again. No joy.

I thought it was to do with some TEXT fields. We have about 6 across the system. So as a test I dropped them all and then did a shrink of the file and still no change.

The only option left is to reimport the data to another DB. This is not practical, as it would have to be done on the live DB, which carries too much risk. We semi-regularly grab a copy of the live DB and overwrite dev/test. We have something like 500 tables. I would like a way of doing it that would not have the risk of exporting data to a new DB.

I tried moving the data to another file, and it copied all but 5% of the data. This is what lead me to try and drop all the text columns.

The server is on compatibility mode 90, but is SP2. I've now done the following 3 times: reindex all tables, backup database, shrink file, shrink database. Still no joy.

EXECUTE sp_spaceused returns:

database_name   database_size   unallocated space
SMS2Tests       31453.94 MB     13903.16 MB

reserved     data         index_size   unused
16545568 KB  10602264 KB  4254360 KB   1688944 KB

Answer 1

As some people already mentioned you could create new database and "copy" stuff over from old database. This would be the best option for you. However I've noticed that you want to do it quite regularly. So your best option is Redgate Data Compare and Redgate Compare. Both are part of Redgate SqlToolbelt package.

So what you do:

1. Create an empty DB with small initial size.
2. Use Redgate Compare to copy db structure, functions etc from old db
3. Use Redgate Data Compare to copy data from old database to new one
4. You work on dev database and then at any time you either do just Data Compare and update the Dev DB regularly, or if you do any changes to db you can deploy those changes using Redgate Compare and then doing Redgate Data Compare.

What is good with Data Compare is that after you copy those 30gb of data (you can do it starting with some tables only) after a while it just needs to 'recompare' only some changes and not whole 30gb of data. Which means it will do a lot less impact on both databases then it would by copying it normally.

Answer 2

A few possibilities here.

First of all, are you on SQL 2005 SP3 or later? Some have reported SHRINKFILE issues on prior versions (see http://www.sqlservercentral.com/Forums/Topic981292-146-6.aspx#bm985164)

Secondly, can you verify that the database is set to mode 90 compatibility, and not mode 80? This apparently was an issue with SQL 2000, but the restriction was lifted in SQL 2005. If your database is still set to mode 80 compatibility, this might still be a problem.

Thirdly, this could be an issue with LOB data (text, ntext, varchar(max)). See Paul Randall's excellent article here

I'm assuming that you understand what SHRINKing really does, and that it can negatively effect your performance:

• SHRINK works by blindly moving pages from the end of the file to the beginning
• As such, it can horribly fragment indexes, which can cause significant performance issues
• Because its such a data intensive operation, the shrink could take significant time

I would normally recommend following up the shrink with a full reindex (which will reclaim some of the space that was just freed), but it doesn't sound like you're even able to get to that point.

If you look at your shrink SPID in activity monitor, does it appear to be doing anything? (IO and CPU counters changing) Or is it blocked? The only other thing I can think of is if there is other activity on the database, blocking the shrink. Make sure no other active spids are making use of the database at the time.

Switching to Simple recovery mode during this process is also a good idea, to keep the log from growing too much.

Answer 3

If you really want to do this:

• set to recover mode to simple
• be aware of: DBCC SHRINKDATABASE (MyDatabase, TRUNCATEONLY);

Answer 4

SHRINKDATABASE will only shrink the database (at best) to its MinSize, so that will not help you.

When you try you shrink the FILE via the SSMS UI using the defaults, it uses 'DBCC SHRINKFILE (N'MyDB' , 0, TRUNCATEONLY)'. That command will only shrink the file (at best) to the last allocated extent.

If you want to shrink the file below the MinSize, just change the parameter on the DBCC SHRINKFILE from 0 to the size that you want to attempt to shrink the file to in MBs. A non-zero number will tell SHRINKFILE to shrink the file to that size if possible. So DBCC SHRINKFILE('MyDB', 300, TRUNCATEONLY) will shrink the file to 300MB if the database has space.

You can see the MinSize by running this:

DBCC TRACEON(3604)
go
DBCC PAGE('MyDB', 1, 0, 3) with tableresults
go

MinSize in MBs is calculated this way: (MinSize*8)/1024

Answer 5

If you have, say, 20003 (mb) of actual datat in the database, then "SIZE = 20000" would be too small. Try with 21000 or 25000, see if that works. (And remember, 1 GB = 1024 MB.)

Answer 6

Try

DBCC SHRINKDATABASE (N'SMS2_Data' , 0)

I used this on a SQL 2005 database I have here and the allocated space for the data file went from 10.2 GB to 3 GB.

Fyi, this is a lengthy process, and took a little over 19 minutes for my database.

Answer 7

I'm assuming that you have a single database file with the logical name SMS2_Data. You also have one or more transaction log files in the database.

You have a challenge that can't be fixed on the current copy of the database. The important piece of information you state is that the 'original size of the database file is 30 GB'. Unfortunately, This file cannot be shrunk smaller than its original size.

As you already experienced, SHRINKDB and SHRINKFILE are not giving you what you want. These commands follow the can't shrink smaller than original size rule. So, you can only shrink a database to the original size and no smaller.

The database backup and restore to an existing, smaller database doesn't work either. When you do a database restore, the database files are restored to the file sizes as they were during the backup. And, the recovery model (simple, full, etc.) has no relevance to this issue.

And, one last bad-news point. You might consider adding other, smaller database files to the database, transfer all the data out of the 30 GB file, and then drop it. Unfortunately, this won't work either because you can't delete the initial file from the database.

So, the best solution is to copy the data to another database. You have a few options here, and maybe you're already aware of them. The first step is to create a new database with a size that is smaller than the data size. Then, you can expand the database size to the required size.

You may consider SSIS as a way to transfer the data from one database to the other. You will find a copy database task that will help you out. You can use the following steps:

1. Create new database with a smaller size than source database
2. Setup SSIS package with the transfer database task. Set the task to use online transfer.
3. Run the SSIS package.
4. The SSIS package may alter the database size to match the source database size. Shrink this database, because it has a smaller original file size.

See additional information on the SSIS transfer database task.

Answer 8

Some unused space in the database is normal.
If you have many large records (say, long strings), there could be much unused space in the data pages (because one record is usually not split between pages).
Another thing is a fill factor - initially, clustered indexes are not created 100% full to avoid page splits (an expensive operation) on subsequent insertions.
If lots of data were deleted from the database, the space previously occupied by these data will not automatically be reclaimed - it will stay allocated to the table.

Try calling DBCC DBREINDEX (table_name, '', 100) on every table in your database - it will rebuild all the indexes with 100% fill factor, so the data is placed as compactly as possible. Then try shrinking the database again.

Answer 9

I've discovered that shrinking a SQL Server database can be troublesome. It feels like you've got to do a song and dance routine.

This is the process I usually go through:

Backup Shrink database Backup Shrink log and database files separately. Backup Repeat until it finally shrinks.

I've had to do this process some times up to three times for it to finally work. We've had a database over 68 GB in size, with something 98% unused space. Went through this song-and-dance routine several times, but it finally shrunk down to under 1GB.

Answer 10

I would have tried to reduce initial size of mdf file to 29 000 MB first, then to 28, 000 detecting the close hit.

It is unreasonable to expect reducing 30% in database file size by deleting 30% of data.

You can estimate how much unused space in your database by

execute sp_spaceused

in context of your database (use yourdatabaename;)
Can you post the result of its execution?

---

Update:
I posted my related question on it:

• compression, defragmentation, reclaiming space, shrinkdatabase vs. shrinkfile