SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 SQL Server Administration (2008)
 autogrowth question
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

rajani
Constraint Violating Yak Guru

367 Posts

Posted - 12/02/2012 :  15:09:18  Show Profile  Reply with Quote
Hello Guys
just 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
Flowing Fount of Yak Knowledge

USA
7174 Posts

Posted - 12/02/2012 :  19:37:19  Show Profile  Reply with Quote
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

Go to Top of Page

Elizabeth B. Darcy
Starting Member

United Kingdom
39 Posts

Posted - 12/03/2012 :  07:09:21  Show Profile  Reply with Quote
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.aspx
http://sqlskills.com/BLOGS/PAUL/post/Follow-on-from-instant-initialization-privilege-checking.aspx

Even 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"
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000