| Author |
Topic |
|
cshah1
Constraint Violating Yak Guru
347 Posts |
 Posted - 2007-03-09 : 12:55:56
|
| I just dropped a large table ( 5 GB and 9 million rows). Dropping table has not reduced size of the database? What should I do to reclaim space used by my db. Should I use DBCC updaeusage afterwords? |
|
|
snSQL
Master Smack Fu Yak Hacker
1837 Posts |
Posted - 2007-03-09 : 13:16:51
|
| You can use DBCC SHRINKDATABASE or DBCC SHRINKFILE or if the database is set to autoshrink, it will eventually shrink on its own. |
 |
|
|
Kristen
Test
22859 Posts |
Posted - 2007-03-09 : 14:01:30
|
| Easiest way is probably to use Enterprise Manager, right click the database, choose SHRINK DATABASE and select the "Move data to start of file" option.When ti finished reselect the Shrink Database option and check that the "Available space" is close to zero.Kristen |
|
|
|
sql_er
Constraint Violating Yak Guru
267 Posts |
Posted - 2007-03-09 : 14:59:37
|
| Guys,What are the disadvantages of shrinking the database?I know about the transaction log file - i.e. since the file is circular, it is usually not a good idea to shrink, as growing is io/cpu intensive. Does the exactly same logic apply to the database file itself (i.e. .mdf)?Thanks |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-03-09 : 15:45:49
|
| You are going to be throwing away the "free space". Given that a 5GB table has just been dropped that seems like a reasonable one-time-deal to me! However, a shirnk may also cause the tables to be moved earlier in the file, and at a guess that might negate some of the benefit of any recent REINDEX that has carefully placed the table contiguously within the file.Same thing applies to extending the MDF file as it does to the LDF file - in fact, in my experience, extending the MDF file is much more resource intensive than extending the LDF file.OTOH having an overly large database will increase the time to restore (to a brand new database) because of the "resource" to pre-create the large, empty, files. But I think that's pretty much the only "cost" The backups won't be any bigger, for example.Kristen |
|
|
|
sql_er
Constraint Violating Yak Guru
267 Posts |
Posted - 2007-03-09 : 16:06:01
|
| Hmm ... I actually thought that the backups are taking everything into an account. That is one of the main reasons why I was considering a shrink. Well, since according to you the backups are not affected, it is not really worth it for me to do a shrink.Thanks |
|
|
|
cshah1
Constraint Violating Yak Guru
347 Posts |
Posted - 2007-03-09 : 16:45:18
|
| Kristen, can you shrink databae while it is being used? |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2007-03-09 : 19:54:05
|
quote:
Originally posted by cshah1
Kristen, can you shrink databae while it is being used?
A database can be shrunk while it is being used.You can use the script on the link below to shrink a database file.Shrink DB File by Increment to Target Free Spacehttp://www.sqlteam.com/forums/topic.asp?TOPIC_ID=80355CODO ERGO SUM |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-03-10 : 03:19:31
|
| "can you shrink databae while it is being used?"Yes (as MVJ says), but you should expect a bit of a hit on performance if you are shrinking the Data file and its shot full of slack space!Kristen |
|
|
|
|
Dropped a large table (5 GB)