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'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.

share|improve this question

migrated from stackoverflow.com Jan 22 '12 at 14:38

This question came from our site for professional and enthusiast programmers.

    
You seem to be having some kind of database related issue here –  Scozzard Aug 25 '10 at 21:16
6  
thanks for that mate, I'm glad you have a good handle on it all –  Amjid Qureshi Sep 1 '10 at 23:42
    
Have you tried to change Initial Size of files of old database to 29000 MB? –  WebMAOhist Sep 23 '10 at 4:25
    
and, then. gradually reducing the size to detect the close hit? You cannot expect to reduce the database/file size by 30% having deleted 30% of data –  WebMAOhist Sep 23 '10 at 4:33

10 Answers 10

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.

share|improve this answer
    
I've been trying to get my bosses to buy redgate for ages now, and they wont budge, they reckon its too much –  Amjid Qureshi Sep 22 '10 at 2:23

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.

share|improve this answer
    
The server is on compatibility mode 90, but is SP2. I Have done multiple backups, I have done 3, one after the other. Still no joy. We have a re-index script that does it for all tables. Thsi has been run before and after backups to no avail. I will read the article about text etc and see how I get on –  Amjid Qureshi Sep 27 '10 at 3:27
    
Just realised I did do multiple backups but without reindexes, will try that now –  Amjid Qureshi Sep 27 '10 at 3:41
    
So I've now done the following 3 times, reindex all tables. Backup DB, shrink File, Shrink DB. Repeat..... and again nothing. Cheers for the help all but I'm over this. Just have to accept it –  Amjid Qureshi Sep 27 '10 at 22:23

If you really want to do this:

  • set to recover mode to simple
  • be aware of: DBCC SHRINKDATABASE (MyDatabase, TRUNCATEONLY);
share|improve this answer
    
Have set it to simple, no change. –  Amjid Qureshi Aug 22 '10 at 23:50

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.)

share|improve this answer
    
I tried 20800 and then kept going up til 29000 (29gig) and it wont let me change it –  Amjid Qureshi Aug 23 '10 at 0:17

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.

share|improve this answer
    
p.s. Just checked and the Recovery Model for my database is set to Simple. –  adrift Sep 16 '10 at 21:07
    
Giving that a go now, will let you know how it pans out –  Amjid Qureshi Sep 16 '10 at 22:19
    
That didn't make any difference, I'm pretty sure thats te same that gets done via the front end. –  Amjid Qureshi Sep 16 '10 at 23:03

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.

share|improve this answer
    
Yeh I have been coming to to same conclusion, Unfortunately as we semi-regularly grab a copy of live DB and overwrite dev/test we would have to do this on Live. The amount of time it would take to do this and the risk involved makes this impossible to do. WE have something like 500 table I would like a way of doing it that would not have the risk of exposrting it to a new DB. –  Amjid Qureshi Sep 20 '10 at 5:11
    
Regarding moving the data to another file, I tried that and it copied all but 5% of the data. This is what lead me to try and drop all the text columns –  Amjid Qureshi Sep 20 '10 at 5:16
    
"cannot be shrunk smaller than its original size". But file "Initial Size(MB)" can be reduced later (if it is not greater than current occupied size). I have just checked it on sample database –  WebMAOhist Sep 23 '10 at 4:48

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.

share|improve this answer
    
Just trying the reindex now –  Amjid Qureshi Sep 22 '10 at 2:26
    
I've just reindexed all tables, then done a shrnk on teh file and then the DB. No change –  Amjid Qureshi Sep 22 '10 at 3:51

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.

share|improve this answer

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:

share|improve this answer
    
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 –  Amjid Qureshi Sep 27 '10 at 4:30
    
That didnt work too well. 13903.16mb unalloacted space. Unused index 1688944 KB –  Amjid Qureshi Sep 27 '10 at 4:31

First set DB in Simple Mode. Then Shrink desired file. Set DB back in Full Mode. Do Full Backup. It's done!

Regards, Marcin

share|improve this answer

Your Answer

 
discard

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