8

I had a table with 70+ million records in a SQL Server database, I dropped that table (a one-time thing) to free up space on the disk, but it looks like the size didn't change much. I do see that I could shrink the db to a minimum.

Is that the way to do it?

The last time I did a shrink using SQL Server Management Studio, it took a few hours to complete. Is there a faster way?

enter image description here

Improve this question
0

4 Answers 4

Reset to default
12

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.

Improve this answer
8

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:

https://stackoverflow.com/questions/4522719/to-dbcc-shrinkdatabase-or-not-to-dbcc-shrinkdatabase-thats-the-question

Improve this answer
2
  • thanks for that info. my main concern is the backup size. will the backup be smaller now after drop table?
    – Ezi
    Commented Jan 4, 2012 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
    Commented Jan 4, 2012 at 18:13
-2

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.

Improve this answer
5
  • 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
    Commented Jan 9, 2012 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
    Commented Jan 10, 2012 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
    Commented Jan 10, 2012 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
    Commented Jan 10, 2012 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
    Commented Jan 11, 2012 at 2:29
-2

Check out this article: http://itknowledgeexchange.techtarget.com/sql-server/deleting-lob-data-and-shrinking-the-database/

"The solution that we came up with was actually pretty simple. Do the database deletion as normal. Then backup and restore the database. Then do the shrink, followed by rebuilding the clustered indexes in order to fix the fragmentation issue which the shrink will introduce."

Improve this answer
1
  • A shrink followed by a rebuild of clustered index will bloat the database again. Database is meant to grow, you just have to plan how much to grow (autogrowth). Also, what will you gain in doing a shrink and getting couple of gigs back, if the database is going to grown to that size again ?
    – Kin Shah
    Commented Jan 11, 2016 at 17:11

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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