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 |
|
zekmoe
Starting Member
5 Posts |
 Posted - 2008-05-01 : 08:40:57
|
| Hello all, thanks in advance for any advice here. My question is, what's the effect of the Enterprise Manager > Tasks > Shrink Database function? It seems to reduce the used space on the device. Testing it on some dev machines, I've seem to have gotten back as much as 20g of space. I know it should grow back to that, but the time it took was minimal, and it didn't seem to affect my developers. They tend to add alot and delete alot during testing. What negative effects of running this should I look out for? Will it affect the DB long term? Is is preferable to schedule once a month or so? Is this done on Production DB's?Thanks for any guidence on the usage of this.BobNot a downstroke, fistpicker |
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2008-05-01 : 08:43:38
|
| shrinking the database effectivly reshuffles all your data pages causeing extensive index fragmentation.this is obvioulsy bad since it direclty affects range look ups on data._______________________________________________Causing trouble since 1980blog: http://weblogs.sqlteam.com/mladenpSSMS Add-in that does a few things: www.ssmstoolspack.com |
 |
|
|
zekmoe
Starting Member
5 Posts |
Posted - 2008-05-01 : 08:49:54
|
| Strange? So what's the point of it? Or is it intended as part of another task? Would I rebuild indexes afterwards? Not sure of it's intention. If I rebuild the indexes, would they then reconsume the space? Or would shrink and rebuild just resize and defragment as expected?Thanks again.BobNot a downstroke, fistpicker |
|
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2008-05-01 : 08:53:46
|
shrinking is meant to be a last straw before buying more disk space  yes you'd need to reindex all your tables after the shrink.the thing is that if you have a clustered index and you have a lot of insert that cause a lot of page splits you might get huge volumes of mostly empty data pages. this happened to a friend of mine a while ago.he had approx 400.00 pages of only 500 bytes of data on each page. so that was lots of unused space.first he reindexed the db to rebuild the pages. then he shrunk it to compress the empty space then he rebuilt the indexes again.and it went from 40 GB to 2 Gb database._______________________________________________Causing trouble since 1980blog: http://weblogs.sqlteam.com/mladenpSSMS Add-in that does a few things: www.ssmstoolspack.com |
|
|
|
Lumbago
Norsk Yak Master
3271 Posts |
Posted - 2008-05-02 : 03:55:36
|
quote:
Originally posted by spirit1
shrinking is meant to be a last straw before buying more disk space
No offence spirit but I'm gonna have to object to this one. I assume that the Shrink database-function in EM is the same as DBCC SHRINKFILE and I use it quite regularly after large DML-operations and expecially in a test environment. I used to work in a DWH-team up until a month ago and we used it all the time because the log- and database-files became huge when testing the different data load processes. I also use it on occasion in the production environment after abnormally big inserts/deletes for example and I can't see any real problem with that. Scheduling shrinks however, is a big nono. Reindexing and reorganizing is of course (regardless of database size/space used) a vital part of database maintenance --Lumbago |
|
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2008-05-04 : 06:46:27
|
| i never said it's wrong to do it. and i was talking about the prod environment not dev.however you can't deny that when you find yourselft doing shrink often on prod it's time to buy more disk space, no?_______________________________________________Causing trouble since 1980blog: http://weblogs.sqlteam.com/mladenpSSMS Add-in that does a few things: www.ssmstoolspack.com |
|
|
|
Lumbago
Norsk Yak Master
3271 Posts |
Posted - 2008-05-05 : 03:07:35
|
quote:
however you can't deny that when you find yourselft doing shrink often on prod it's time to buy more disk space, no?
I think we both agree on this and how to use shrink, I just wanted to make it clear that a shrink isn't always the last resort before buying more diskspace. It's perfectly justifiable to do a shrink, but more so in a test environment than in prod and the fewer times you have to do it the better. But as you say, if you run shrinks regularly in prod it would be good to get more diskspace instead as it will cause heavy fragmentation. --Lumbago |
|
|
|
|
|
|
|
|
Effect of Shrink Database Question