February 4, 2011 at 7:18 am
Someone created a maintance job to shrink databases every week 500 MB limit, free space 10%. Is this a good idea to do it? From my experience shrinking database is not a good thing to do, any suggestions? Thank you
February 4, 2011 at 7:28 am
No, it's not a good idea at all - and also rather pointless in this case, as all it will do is grow again
See here for a detailed explanation as to why it is bad:
http://blogs.msdn.com/b/sqlserverstorageengine/archive/2006/06/13/629059.aspx
EDIT: Actually, this was the article I was thinking of:
http://www.sqlskills.com/BLOGS/PAUL/post/Why-you-should-not-shrink-your-data-files.aspx
February 4, 2011 at 7:40 am
It's not considered good practice to shrink a production database.
If the database has been shrunk and new data is added then it has to grow again, probably at an inconvenient time. Grows and shrinks are expensive IO operations
Also shrinking a database reorders data and index pages to fit into a smaller volume, hence you are fragmenting your indexes every time you shrink. You have to reorganize your indexes, everytime you perform a shrink.
If its a maintenanace plan, tht means it will reoccur again every week..
Shrinking can be done only if you are sure that the data in database wont again grow and also if you have a space issue.
Regards,
Sushant
Regards
Sushant Kumar
MCTS,MCP
February 4, 2011 at 1:20 pm
I can't help but wonder, are you sure it is the actual database and not the log file they are shrinking each week? Not that this makes it that much better, but it unfortunately is much more likely.
Chris Powell
George: You're kidding.
Elroy: Nope.
George: Then lie to me and say you're kidding.
February 4, 2011 at 2:40 pm
chris.s.powell (2/4/2011)
I can't help but wonder, are you sure it is the actual database and not the log file they are shrinking each week? Not that this makes it that much better, but it unfortunately is much more likely.
mazzz already provided the great links on why not to shrink.
Even with IFI enabled, it is still a bad practise because of the file fragmentation it will cause at extend time. (possibly already encountered right after the mandatory index maint after the shrink ops)
chris.s.powell pointed to another option, but I don't recall being able to only shrink the log file using a maint plan. However it needs to be checked.
If they are shrinking the log file, they would better keep it under size controle by raising the frequency of their log backups.
Keep in mind IFI is not used for log files, so extends will take longer.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
February 7, 2011 at 1:47 pm
Thank you for your help,
this is what I have:
DBCC SHRINKDATABASE(N'virtualDBA', 10, TRUNCATEONLY)
shrink database when it goes beyoung to 500mb
amount of free space to remain after shrink 10%
Return freed space to operating system
It is scheduled once a week
February 7, 2011 at 2:06 pm
Maybe some1 put up that maintenance plan because the ddatabase was growing out of size frequently..
You can disable that, but be sure to monitor the size of db and the drive containing it.
Regards,
Sushant
Regards
Sushant Kumar
MCTS,MCP
February 8, 2011 at 8:52 am
Krasavita (2/7/2011)
...DBCC SHRINKDATABASE(N'virtualDBA', 10, TRUNCATEONLY)
...
The TRUNCATEONLY here means you don't have to rebuild / reorganize your indexes - its doesn't actually move any data around. Unfortunately, it also means that the Shrink probably doesn't do much - it only frees space down to the last allocated extent in the data file.
Having said that, I totally agree with what everyone else has said - Shrinking on a regular basis is a bad idea, especially if the files are going to regrow. In this case you also have the added issue that if there is 10MB of data, but the last allocated extent is at 501MB, you're going to try to Shrink it every week, with no effect.
February 8, 2011 at 9:05 am
If you've been shrinking and growing weekly for awhile, you should check for filesystem (OS) level fragmentation with the OS defrag, Defraggler, or another tool, as well as index level fragmentation.
Essentially, you want "big chunks"; I don't see a massive problem with a 500GB database being in 4 100GB+ fragments, even if the OS says the drive is "100% fragmented". Now, a 10GB database in 100,000 fragments (I kid you not), that's a problem.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply