July 29, 2009 at 9:58 am
Greetings everyone.
I am designing a script that will shrink my databases periodically. Part of the scripts are the DBCC Shrinkfile and DBCC ShrinkDatabase commands. Is there a way to suppress the results of executing these?
Link to my blog http://notyelf.com/
July 29, 2009 at 10:28 am
Why are you shrinking your databases? This is not a recommended practice. Please read these blog posts:
http://www.sqlskills.com/BLOGS/PAUL/post.aspx?id=08f8fcc0-8ec7-4403-8257-2d6bf6293e73
http://www.sqlservercentral.com/blogs/steve_jones/archive/2009/07/07/shrinking-the-log.aspx
http://www.sqlskills.com/BLOGS/PAUL/post.aspx?id=a2b9f8a7-ec99-43a5-b365-40143cbb9180
Those are just a few of many articles/blog posts about why not to use shrink. Certainly there are rare cases where it may be necessary, but they are very rare.
To actually answer your question I think you want to add "With NO_INFOMSGS" to the command(s).
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
July 29, 2009 at 10:41 am
shannonjk (7/29/2009)
Greetings everyone.I am designing a script that will shrink my databases periodically. Part of the scripts are the DBCC Shrinkfile and DBCC ShrinkDatabase commands. Is there a way to suppress the results of executing these?
This is a bad idea. In fact, your databases should have enough free space to allow data to be inserted/updated/deleted for at least 3 to 6 months before needing to be expanded. You should not rely on the database to autogrow on a regular basis.
In addition, if you are routinely shrinking your database, you are requiring your database to grow on a routine basis as well. This will affect system performance and result in heavy file fragmentation on the OS level which will also affect system performance.
July 29, 2009 at 10:46 am
Plus when you shrink you're fragmenting all of your indexes. If you don't rebuild, you're likely to get poor performance. If you do rebuild, the DB's going to grow as part of the rebuild.
See - http://sqlinthewild.co.za/index.php/2007/09/08/shrinking-databases/
What's the reason behind the script to regularly shrink databases?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 29, 2009 at 10:49 am
Why are you shrinking your databases? What problem are you trying to solve?
What is the recovery model you are using? If 'full' you'll need to backup transaction logs (.ldf) or else they'll just keep on growing.
July 29, 2009 at 10:56 am
Wow I did not know all of that!
Ok I guess I need to make some modification to my maintenance plans!
Thank you all for your help!
Link to my blog http://notyelf.com/
July 29, 2009 at 11:06 am
shannonjk (7/29/2009)
Wow I did not know all of that!Ok I guess I need to make some modification to my maintenance plans!
Thank you all for your help!
That's the great thing about SSC. Most members don't just provide the answer to the question, but try to educate. Glad we could help.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
July 29, 2009 at 11:45 am
One caveat - I did not read all of Jack's previous links - BUT wanted to add, shrinking the DB and/or logs, when they auto-grow (if the setting is enabled), you're going to lead to potential disk-level fragmentation and another potential performance hit. Determine what it is you need, leave ample space (based on disk constraints) and let it go. Monitor disk usage and ensure you have the capacity to meet the seemingly never-ending growth capacity. Not enough space? Add more disk or come up with an archive strategy to accomodate your business needs.
-- You can't be late until you show up.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply