Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Jay Nichols
Starting Member
3 Posts |
 Posted - 2011-03-23 : 13:16:11
|
| I have a db size .9TB I am trying to shrink. I know there is about 300 MB of space as I just dropped a table that size. The DBCC command is being run on the server. It has been running over 18 hours. I've checked for blocking locks - none, running (activity monitor) - physical io increasing. Is there something wrong. I understand I can stop the shrink operation and pick it up later, but I need the space (I think) as I have to replace that 300MB table with new data.Question is: is the command running too long?? How much longer do we think it will run?J Nichols |
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2011-03-23 : 13:38:45
|
| If you have to replace the 300 MB with new data in the same database, don't shrink. All that will happen is that the DB will have to grow again to accommodate the new data. Shrink is only for when you free up space that you know will NOT be reused by objects in that database.--Gail ShawSQL Server MVP |
 |
|
|
Jay Nichols
Starting Member
3 Posts |
Posted - 2011-03-23 : 15:39:22
|
| Thanks Gail. The 300 MB table actually existed 3 times at once so at most only 2 were needed and when I finish we will only need one. So I guess I'll redo the shrink this weekend after I get the data all in one table.J Nichols |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2011-03-23 : 17:34:06
|
| Why shrink? That's 3 or 6% of the database in total. Is that tiny amount of space really needed in the filesystem rather than the DB and is the DB never going to grow again?You're just wasting time and processing power. After shrinking you'll need to rebuild all your indexes to undo the fragmentation that the shrink caused and that will likely just make the DB grow back to it's former size if not larger.--Gail ShawSQL Server MVP |
|
|
|
Jay Nichols
Starting Member
3 Posts |
Posted - 2011-03-24 : 14:31:53
|
| My mistake. The table is 300GB in size. That represents almost 33% of the total size. However I am interested in your comment about rebuilding the indexes. If the primary key to this table is defined as clustered does that make any difference? In fact, all the main tables in this db are configured with the PK as clustered.Help. You seem to know a whole bunch more than I do.J Nichols |
|
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
|
|
|
|
|
DBCC shrinkfile