SQL Server Performance Forum – Threads Archive
Shrinking Database (NOT LOGS)
I have a problem in shrinking the datafiles in a couple of databses.I started out with a 50GB db, and deleted a couple of years of historical data. I then rebuilt all the indexes, and ran a dbcc checkdb. Now when I try to shrink the database using either the gui or using dbcc shrink database or dbcc shrinkfile, I get the message back that my db has been successfully shrunk, but I still see that the data file has 18GB of unused space, and seems the same size on the disk. I have managed to do this on another database, which shrunk successfully using the same apporach (via the gui), but on this particular one – no cigar. I have the luxury that this database is not yet part of the production system but it will be when shrunk, so I am able to take it offline,and restart the server etc… I incorrectly posted this thread on a topic about shrinking logfies, and got some very useful advice for shrinking logs, but my logfiles are just dandy, it’s tha actual datafiles which are being a pain. Any ideas of what I can do to force the datafile to shrink? Tia
Paul
When you say it has 18Gb free can you be sure that’s true? Does running sp_spaceused @updateusage=true in the database confirm the value? I just wonder if it won’t shrink because it is still full. Does the shrink take long when you do attempt it? If it returns the output instantaneously, I’d be sceptical as to whether it’s actually doing anything. This is where I’d start.. Tom Pullen
DBA, Oxfam GB
Thanks Thomas.
I think the statistics are correct as I did a checkdb.
on running the sp_spaceused @updateusage=true I get: reserved data index_size unused
—————— —————— —————— ——————
27785096 KB 26711952 KB 1067296 KB 5848 KB but sysfiles shows fileid groupid size maxsize growth status
—— ——- ——- ——– —— ——-
1 1 5894224 11904000 0 1081346 and sp_helpdb shows: name db_size
——————- ———
MyBigFatDatabase 46049.88 MB So I believe my database is actually only about 28GB(ish), and the file is 46GB.
If I try and shrink using the gui, the minimum file size I am offered is 27135MB which is consistent with my belief about the actual data+index size.
The first time I ran the shrink it did run for quite some time (which is a good thing), but the size remained unchanged. Subsequently it return within abouts 5 seconds.
This does sound strange and I’ve never encountered similar behaviour. Might be worth trying restarting SQL Server if you haven’t already… other than that I’m at a bit of a loss, maybe someone else will have a brighter idea. The last resort is to copy everything into a new smaller database but I’d say that was totally last resort as it’s a lot of hassle. Tom Pullen
DBA, Oxfam GB
I’ve tried bouncing the box in case there was a problem with the interaction between the sqlserver process and the filesystem. I had considered copying the objects to a new smaller DB, but the prospect doesn’t thrill me, and was hoping there was something really obvious that I was missing. (or some cunning script which forcibly returns unused pages to the filesystem).
The copy database wizard is pretty reliable (I believe) and it’s simple to use but I’d carefully check it’s copied all the indexes and foreign keys. I have had trouble with it when it doesn’t do the tables in the correct order then the data copy fails because of referential integrity violations. It’s because of these reasons I’d leave it till all other avenues have been exhausted… Tom Pullen
DBA, Oxfam GB
I did it once on mssql server 6.5. I first created script for dropping all fks and another one for (re)creating them . Then I dropped fks, moved all data and recreated fks. Scripts read from sys* tables and should work on mssql 2000 too. Let me know if you decide to go that way, I believe I can find them.
Have you tried running a CHECKPOINT, then a DBCC DBREINDEX, then shrinking just the data file? MeanOldDBA
[email protected] When life gives you a lemon, fire the DBA.
]]>