November 14, 2007 at 2:43 pm
Hello,
I have just read the fairly unambiguous article "Auto Close and Auto Shrink - Just Don't[/url]" but I have a couple of questions.
1. If I turn off Auto Shrink where I see it on - and I have already found a few cases - do I also need to add a shrink task to the maintenance plan that corresponds to that database?
2. Is there a way to tell (as with SQL Profiler) when shrinks are happening now and how long they are taking? Is it possible that the shrinks are causing some of the blocking we are seeing on our database server? I just want to have a baseline to quantify whether turning off the Auto Shrink options has any positive effect on performance.
I realize that adding the shrinks to the maintenance plan will simply shift the performance hit from one time to another, but I am hoping that at least (1) I will be able to control when the shrinks happen and (2) I can set a threshold, which seems possible using the Optimizations option "Shrink database when it grows beyond...".
Thanks in advance for any help!
webrunner
-------------------
A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html
November 14, 2007 at 3:20 pm
I would turn off the auto shrink. It is possible it is causing some of your issues, as it may be shrinking a database at an inopportune time, like just as it may also need the space it is releasing. Also, constantt shrinking and growing of the database files can result in extreme fragmenting of your database files.
If you really must shrink your databases periodically, it is better to do it in your mainteneance plans, or to create a specific job that does it at a scheduled time. The only time I have done a shrink on our production databases is when I have restored them as training or development databases for our developers.
😎
November 26, 2007 at 11:06 am
Turn it off: http://blogs.msdn.com/sqlserverstorageengine/archive/2007/03/28/turn-auto-shrink-off.aspx
It could cause blocking, but it's a bad idea.
November 26, 2007 at 12:07 pm
Steve Jones - Editor (11/26/2007)
Turn it off: http://blogs.msdn.com/sqlserverstorageengine/archive/2007/03/28/turn-auto-shrink-off.aspxIt could cause blocking, but it's a bad idea.
Agreed. while it might be okay to occasionally shrink the log files if something goes out of control, shrinking DB files tends to make them a MESS, so it would be something to do as little as possible, followed by reorg'ing and reindexing just about everything you have in the DB just to undo the "damage" caused by a DB shrink.
That kind of brings us to the "why" question. Most DB's either grow linearly (i.e. continually acquire more records), or are some variant of "destroy and rebuild". Either case kind of works best if you leave the space allocation alone - what leads you think that you NEED to shrink the DB? The option in the maintenance plan? Just curious if there is something out of the ordinary going on or not.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
November 26, 2007 at 12:22 pm
- problem with authshrink is that you don't know _when_ it is going to happen.
- autoshrink is active, and apps are still accessing the db, they will suffer, or even worse have timeouts, deadlocks, ...
- SQLserver allocated the space because there was a need for it. extending files is a costly action and "freezes" access during the extend.
- sql server will reuse empty pages if it can.
- reorganizing tables and indexes is THE thing to do. this will optimize table/index internal arrangement and although the reindex or indexdefrag may also cause interference, your apps will gain because of the more optimal access plans that result from it.
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
November 26, 2007 at 1:24 pm
webrunner (11/14/2007)
Hello,1. If I turn off Auto Shrink where I see it on - and I have already found a few cases - do I also need to add a shrink task to the maintenance plan that corresponds to that database?
No. Why are you shrinking the database in the first place? The chances are good that it's just going to grow again, meaning you've wasted resources twice. Once to shrink it, once to grow it, and in the process you've very likely increased the internal and external fragmentation of the data file.
Here's another article[/url] for you to read. Also check the two links in there that go to blogs posts of a former member of the SQL storage engine team
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
November 27, 2007 at 4:53 am
What about shrinking TLog files? (SQL2K)
We a few months back we started getting "disk space low" errors on a very large drive hosting the TLog files for the SQL Instance. There are several hundred databases on this production server. We discovered the Transaction Log files on several DB's had grown to in some cases several gigabytes each. I listed the biggest offenders and in Enterprise Manager checked the DB properties. In all cases, the DB's were set to Simple Recovery Model and Auto Shrink = True. TLogs for these DB's are not getting backed up as part of a maintenance plan, but don't need to for any of these databases (they aren't transactional - they're updated once a day by jobs that pull data from an AS400).
Shrinking the DB's brought all of the T-Log files back down to 2048K.
2 questions:
1, why do the TLog files grow continuously and not release they're space on an Auto-Shrink Simple recovery DB?
2, is it safe to just shrink the TLog files on these DB's and leave the MDF's alone?
I'm new to the site. Thanks in advance for any help on this.
Jerry Boutot MCAD, MCP
AppDataWorks, LLC
Jerry Boutot, MCAD MCP, MTA
Jerry Boutot Official
December 3, 2007 at 1:21 am
First I would suggest turning off the autoshrink. That will try and shrink both the data and the log files.
In simple recovery mode, the transaction log will grow to a size required for the longest running/largest transaction you have. The log truncates on a checkpoint, but active portions of the transaction log (ie portions with uncommitted transactions) can't be truncated.
Either look through your import process and break large tranactions into smaller ones, or accept that the tran log needs to be that size.
A log that keeps growing when the db is in simple indicated an uncommitted transaction (or transactional replication that's not working well) Use DBCC OPENTRAN to see if you have uncommitted transactions.
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
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply