Cannot shrink a large database that is full of holes

Question

Cannot shrink a large database that is full of holes

Viewing 5 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

MohammedU SSCoach Points: 19644 More actions · Answer 1

Run DBCC DBREINDEX first so that you can remove the fragementation and then run the shrinkfile command. If any of the tables contains text columns you may not be able shrink the file...

Reducing SQL Server Index Fragmentation

http://www.mssqlcity.com/Articles/Adm/index_fragmentation.htm

1. DBCC DBREINDEX()

2. To shrink data/log file use DBCC SHRINKFILE command...

Check BOL for more details...

DBCC SHRINKFILE and SHRINKDATABASE commands may not work because of sparsely populated text, ntext, or image columns

http://support.microsoft.com/kb/324432

MohammedU
Microsoft SQL Server MVP

kencowin Valued Member Points: 64 More actions · Answer 2

Thanks Mohammed. Will do some defraging/re-indexing of the indexes this weekend (slow time for production). Will also create a temp clustered index on an appropriate field(s) to get the data re-sequenced and hopefully packed tighter. Then delete the temp clustered index.

By the way, I created a test database on the same instance and copied a couple of these larger tables. One table was 5GB on original production database and the copied table on the test database only took 22MB!! This table did not have a clustered index. The other table I copied was 28GB with a clustered index and the copied version only took up 1.2GB.

Hoping not to have to do some more drastic moves (copy individual tables to a temp database and then copy them back to original db) and that the DEFRAG/RE-INDEX and building temporary clustered indexes will pack these tables tight. Then I will shrink the database.

Stay tune.

Thanks

kencowin Valued Member Points: 64 More actions · Answer 3

Mohammed,

Thanks for your input !

Yesterday, when usage was low for this application, there was about 9 tables of any size (2-35GB) and all but one had no clustered index. I found that if I created a cluster index on the primary key field (all were an id field) and I used a 95% fill factor, that I got anywhere from a 50% to 90% space gain. Doing further defrag and reindexes did not seem to help anymore than that. Then I deleted this newly created clustered index.

After working with all 9 tables, the database was still sitting at 88GB, but it had about 30GB of freespace. Then did a Shrinkfile doing the compress option. After 2 hours, the database was at 53GB with an additional 20GB free. The Shrinking and compressing seemed to come up with still more free space but had not released this extra free space.

Planned on doing some more shrinking next weekend, but did not want to wait another two hours yesterday.

I am thinking I should re-create those clustered indexes and leave them out there. Pretty much all the tables I was working with have an id field as its key where rows are continually added with this id field keep on bumping up. There is little data deleted once it is added at this point. So, I do not have to worry much about later page splits in trying to add keys in the middle.

Definitely opportunities for further improvements, but getting this space knocked down will takes the pressure off. []

colin.Leversuch-Roberts SSC Guru Points: 52643 More actions · Answer 4

OK first point .. if your table is fragmented at leaf level and is a heap ( no clustered index ) then no matter how many times you rebuild indexes you will not optimise the data structure.

Your table act_log has loads of wasted space and is probably a heap - either create a clustered index to regain space and then remove it , or add a clustered index for good. To be honest it's generally best practice to have tables with clustered indexes unless they're very small.

I hate shrinkfile ops they can seriously degrade performance and with hard disk prices so cheap just increase storage. Once you've done your shrinking you must rebuild all your indexes in an attempt to make your data storage contiguous.

I'd suggest you read up on how indexes work and such so you understand the significance of what you are attempting to do. Fillfactor will bloat your data storage and degrade performance. If you haven't researched which indexes and structures require a fill factor then take it off and regain your space - then monitor and add fillfactor to tune.

Ultimately the method to regain space is to move all the data to a new database. If you have text columns then there are some other issues you may face.

[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/

kencowin Valued Member Points: 64 More actions · Answer 5

Colin,

I believe you are pretty much right on on all your points. Thanks