Taking advantage of drive space after delete 1

[NeelyJ]

I deleted several million rows from a table in SQL, but never saw the change in drive space. How do I regain this space?

 

[foxdev]

While databases are set to expand by default, they are not set to shrink by default. If you are pretty certain you won't need that space in the database any time soon, you can use DBCC SHRINKDATABASE.

Robert Bradley

http://www.foxdev.com/

 

[NeelyJ]

Thanks for your response. The situation is I have a single table of 11 gig (74 mil rows). I deleted 20 mil rows, but the size stayed the same. I have tried dbcc shrinkfile, and it didn't help. Does "shrinkdatabase" do something different? If so, could you tell me the command line? I apologize for my ignorance and appreciate your help more than you will ever know.

 

[foxdev]

With a table of that size, I suspect it may take awhile before SQLS finishes the process - its not going to be immediate.

You can also set a database to Autoshrink, although that may not be advisable in your circumstances.

Note that a database won't shrink smaller than its initial size (when you created the database) or if you've used ALTER DATABASE to change its minimum size; in these cases, you must use DBCC SHRINKFILE to shrink the files individually.

Robert Bradley

http://www.foxdev.com/