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 |
|
rajani
Constraint Violating Yak Guru
367 Posts |
 Posted - 2012-12-02 : 15:09:18
|
| Hello Guysjust quick question about auto growth of sql server database size, one of our clients (260GB data ,autogrowth set to 10%)recently complained extreme slowness of database operations. error from our .net application is "Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding."after some investigation and windows event viewer, there is a list of messages from MSSQLSERVER. "5144: Autogrow of one of file in database 'mydb' cancelled or timed out after 30547 ms. Use ALTER DATABASE to set a smaller FILEGROWTH or to set a new size."I changed auto growth size to smaller percentage and issue resolved. My question is , there was 200+Gb free space available at that time why this issue still occurred ? with default setting (10%) it should increase 30gb right?Can you advise on this please ? it helps plan better for future. it took me long time to figure out because i thought there was enough disk space thats why i did not initially think it was storage problem.thanks.Cheers |
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2012-12-02 : 19:37:19
|
| Correct Answer for this depends on what analysis you have done on the growth pattern of the Database.How is it Growing? Setting 10% growth rate on huge databases can cause performance issue and below errors. We set out large DB setting to grow at 500MB. That depends on how the growth pattern and detailed analysis.Check this good kb article:http://support.microsoft.com/kb/315512 |
 |
|
|
Elizabeth B. Darcy
Starting Member
39 Posts |
Posted - 2012-12-03 : 07:09:21
|
| In my mind, the most important best practice (which happens to be the #1 item in the link that sodeep posted as well), is "For a managed production system, you must consider autogrow to be merely a contingency for unexpected growth. Do not manage your data and log growth on a day-to-day basis with autogrow."Another thing you might want to check into is whether you have Instant Initialization enabled. If it is enabled, file growth will be faster. My familiarity with that is solely based on couple of blogs that Paul Randal has written, so I won't comment on it other than to point you to his blogs:http://sqlskills.com/BLOGS/PAUL/post/How-to-tell-if-you-have-instant-initialization-enabled.aspxhttp://sqlskills.com/BLOGS/PAUL/post/Follow-on-from-instant-initialization-privilege-checking.aspxEven if you have it enabled, it does not apply to log files. Also, if you have TDE on your database, instant file initialization cannot happen.All of which goes back to the best practice that I mentioned earlier - i.e., manage it on your own.______________________________________________-- "If a book is well written, I always find it too short" |
|
|
|
|
|
|
|
|
autogrowth question