Tell me more ×
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.
up vote 5 down vote favorite

I had a table with 70+ Million records in a msSql database, I drooped that table to free up space on the disk.

But it looks like the size didn't change much. but I do see that I could shrink the db to a minimum.

Is that the way to do it?

Also last time I did a shrink using the sql management studio it took him a few hours to complete, is there a faster way around?

enter image description here

share|improve this question
Do you expect to have to drop this table regularly or is this a one time event? – Ram Jan 8 '12 at 4:26
its a one time thing. – Ezi Jan 8 '12 at 16:18

migrated from stackoverflow.com Jan 6 '12 at 2:40

3 Answers

up vote 7 down vote accepted

Dropping a table will free up the space within the database, but will not release the space back to Windows. That requires shrinking the database file. However we don't want the database file full. We want lots of free space so that as we load more data we don't have to grow the data file frequently. This causes fragmentation of the data file on the physical disks.

As for making shrinking faster, no there's no way to make it faster. Shrinking the database requires reading and rewriting most of the data within the database so that all the white space can be released from the database file back to the OS. All this IO takes time, and causes a lot of fragmentation problems.

share|improve this answer
up vote 5 down vote

You'll want to read about DBCC SHRINKFILE very carefully.

http://technet.microsoft.com/en-us/library/ms189493.aspx

In general, you don't want to shrink your database to the smallest possible size on disk. You want to leave SQL Server with plenty of space so that it doesn't have to auto grow much. The answer here has a lot of useful info:

To 'DBCC SHRINKDATABASE' or Not To 'DBCC SHRINKDATABASE': That's the question

share|improve this answer
thanks for that info. my main concern is the backup size. will the backup be smaller now after drop table? – Ezi Jan 4 '12 at 18:07
1  
Yes, definitely. Also, if you have SQL 2008+, check out backup compression. It's easy and I got really good results from it. – tnktnk Jan 4 '12 at 18:13
Ok that's great. (this cust uses sql 05) – Ezi Jan 4 '12 at 18:19
up vote -1 down vote

Shrink is quite expensive procedure and might take hours. To use space most efficiently you may transfer tables into new filegroup with clustered indexes precreated and drop previous. I personally prefer this because it's easier to make this "shrink" in several steps and more predictable- thus easier to plan. When you start DBCC SHRINK you don't know how much time it will take.

Update (Thanks to mrdennny for pointing that): this is a very specific approach and can only be used if you have a read-only database (like data warehouse) because while copying data to another table no writes are allowed to this table for the sake of consistency. To save time and gain max performance of it you may switch database to SIMPLE recovery model and use TABLOCK hint - this will allow system to use minimal logging and do much less writings into transaction log.

share|improve this answer
How exactly do you precreate a clustered index on another file group? You can only have one clustered index per table, and what ever filegroup the clustered index is in is where the table is as the clustered index is the table. – mrdenny♦ Jan 9 '12 at 7:15
Well, youve probably misunderstood me - I didn't mean creation of new clustered index on same table - its not possible of course. I meant creation of a completely new filegroup, a new table there and move data into it. Then just drop old table. sqlskills.com/blogs/paul/post/… Creating clustered index on new table beforehand allows to have only that amount of space, that data consumes, but slows down data load, of course. – Vlad Ogay Jan 10 '12 at 5:35
From a consistancy point of few that would be a nightmare. You would have the application writing data to the old table, while you are busy writing to the new table. If you wanted to move the tables to another file group, just do an ALTER on the clustered index and change the file group that it's being stored in. SQL will rebuild it in the new file group. If done online there's no outage to the application. This is all pretty drastic just to shrink a file. – mrdenny♦ Jan 10 '12 at 9:17
Well, author didn't say anything about writing but he said it's just one time thing. What I'm talking about is a read-only table in data warehouse and there you usually load data rarely. That's probably not the case for author, I agree, but ALTER rebuilding requires extra disk space which will also have to be freed up in the end - isn't it? Online slows down this process if we want it to be done quickly in a time-window, also. If you can switch database into simple recovery model and do these transfers in BULK INSERT mode - that saves you time. – Vlad Ogay Jan 10 '12 at 10:26
Doing an ALTER into another file group should take the same amount of space as writing the data to a new table. Yes the online operation takes a bit longer, but the system is up and running the entire time. When operations can take days there is no such thing as working within a maintenance window. Also there is the issue of if there are foreign keys using this table as a parent which may need to be addressed. Rebuilding to another file group should leave no data left to be cleaned up. In any case this isn't a technique I'd ever recommend to a customer. – mrdenny♦ Jan 11 '12 at 2:29

Your Answer

 
discard

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.