Shrink a database below its initial size

Question

I've got a dev database that is a copy of live 30gb, we have deleted some data that is not needed in dev that brings the data file used to 20gb.

Its SQL server 2005

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)

nojoy.

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 tried

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

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

Have done the shrink then changed the recovery mode from FUll to simple and back again. no joy

Any ideas??

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

ADDITION 2 : Cheers everyone for your answers, no single answert has sorted teh problem. The only option left is to reimport the data to another DB. This is not practicle as it would have to be done on the Live DB, which carries too much risk. I've no idea what will happen to the bonus points. I cant pick an answer as nothing has given me a solution to my problem

ADDITION 3 : I've tried lots of things and nothign has worked, thanks all for your help but I've just got to accept that its gonna be 30 gig at least.

Answer 1

If you really want to do this:

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

Answer 2

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 3

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 4

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 5

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 6

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 7

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 8

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 9

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