February 27, 2012 at 7:55 am
Hi Everyone,
Hope all is well.
I am seeing the below error occasionally in my sql server error log. Whenever I notice this log entry I see application timeout issues.
Autogrow of file '_data' in database 'DBName' was cancelled by user or timed out after 30077 milliseconds. Use ALTER DATABASE to set a smaller FILEGROWTH value for this file or to explicitly set a new file size.
I assume during an autogrow critical database resources will be held by the engine and this causes timeouts/concurrency issues. Per MS if we reduce the autogrow to a little growth in % then that should fix it. I was wondering if anyone faced such issues and know how to determine the data file autogrow value for a database(on what basis should the autogrow % value be determined). In my scenario the database is 45GB and I am using SS2008R2 X64 SE. The autogrow value was set to 10% by default and I want to lower this value. I have transaction log backups running at 1:00Pm and 5:00PM everyday. Would adding another TX log backup help fix this issue??
Please share your valuable inputs.
Thanks a bunch.
“If your actions inspire others to dream more, learn more, do more and become more, you are a leader.” -- John Quincy Adams
February 27, 2012 at 8:36 am
I never use percentage for autogrow. I only use whole numbers. Most of the times I use something between 200MB and 500MB, depending on what I think will be enough. Also take into consideration that I think of autogrow as sort of first aid if I made some kind of mistake, and didn’t notice that a database is running out of space. I try to give the database enough disk space so they won’t use the autogorw (but I have to admit that sometimes I get into situation that autogrow allocates more space).
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
February 28, 2012 at 10:33 am
I never use percentage for autogrow. I only use whole numbers. Most of the times I use something between 200MB and 500MB, depending on what I think will be enough. Also take into consideration that I think of autogrow as sort of first aid if I made some kind of mistake, and didn’t notice that a database is running out of space. I try to give the database enough disk space so they won’t use the autogorw (but I have to admit that sometimes I get into situation that autogrow allocates more space).
Adi
Thanks for the reply Adi. Currently, The file size in MB and Growth percentage is pasted below:
File Size (MB) Growth Percentage (%)
23461.69 NULL
25807.88 10.00
28388.69 10.00
31227.56 10.00
31900.44 2.15
35090.50 10.00
37200.56 6.01
40920.63 10.00
45012.69 10.00
So if I suddenly change the autogrowth to say 1024MB(instead of %) for this database data file would it cause any degradation in performance? Also would it fix the timeout issues during the auto growth?
Thanks for all your help.
“If your actions inspire others to dream more, learn more, do more and become more, you are a leader.” -- John Quincy Adams
February 28, 2012 at 10:41 am
To be honest you should never allow a file to autogrow by a percentage. Why Microsoft still push this as the default option is beyond me. You should always have growth done as a set amount, be it 1GB or 10GB, it doesn't really matter.
By the looks of things you don't have Instant File Initialization enabled and so right now any time growth happens it has to zero out the portion that you are growing by. This will significantly impact performance of the storage while that happens. Go read Kimberly Tripp's excellent post on what it is and how to enable it for your systems.
As a final note, you really should not have your files autogrow, you should be managing that growth manually and having autogrow turned on only for emergency situations where something unexpected happens. This will allow you to more actively manage your systems for the best performance.
February 28, 2012 at 10:44 am
Growing the data files will cost you disk resources (no matter when you do it) - and with DB's that constantly grow there is no way around this but to size it appropriately in hopes of reducing the frequency of it happening. We have fairly rapidly growing DB's (about 1.5GB/week) so similar to Adi we use a fixed growth size/rate
I would reccomend you read up on Instant File Initialization [/url] as this helps reduce disk IO when these files are forced/allowed to grow
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
February 28, 2012 at 11:02 am
You beat me to it Nicholas! I'm always a day late and a dollar short! hehe:
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
February 28, 2012 at 11:20 am
MyDoggieJessie (2/28/2012)
You beat me to it Nicholas! I'm always a day late and a dollar short! hehe:
I assure you, I'm no earlier and somebody else has your dollar, I have nothing to do with it being missing :Whistling:
February 28, 2012 at 12:06 pm
Thanks for the info guys. Yes, I knew about the instant file initialization and I have already scheduled to implement it.
“If your actions inspire others to dream more, learn more, do more and become more, you are a leader.” -- John Quincy Adams
February 28, 2012 at 12:45 pm
one last thing is on my test environment I have the sqlserver services running under local system account (unlike our production env where I changed it to run under a domain service account). I guess I dont need to worry about IFI for the test environment as it should already be part of perform volume maintenance tasks policy because the services are running under local system.
“If your actions inspire others to dream more, learn more, do more and become more, you are a leader.” -- John Quincy Adams
February 29, 2012 at 8:30 am
Nicholas Cain (2/28/2012)
To be honest you should never allow a file to autogrow by a percentage. Why Microsoft still push this as the default option is beyond me. You should always have growth done as a set amount, be it 1GB or 10GB, it doesn't really matter.By the looks of things you don't have Instant File Initialization enabled and so right now any time growth happens it has to zero out the portion that you are growing by. This will significantly impact performance of the storage while that happens. Go read Kimberly Tripp's excellent post on what it is and how to enable it for your systems.
As a final note, you really should not have your files autogrow, you should be managing that growth manually and having autogrow turned on only for emergency situations where something unexpected happens. This will allow you to more actively manage your systems for the best performance.
I agree with all the above.
1) Enable instant file init; note this does not affect log autogrowth.
2) Perform capacity planning; estimate your minimum, average, and maximum rates of growth, then, when you can take the time, size the database and log files to what it'll need at X time units in the future (6-18 months if you have to request things regularly, 10 years if you expect to have exactly the same storage for the next 8 years).
2a) Repeat 1/2 to 2/3rds of the way into your expected window, or sooner if your regular monitoring shows the curve's changed.
3) When you have a maintenance window, defrag your files at an OS level; if you've been letting autogrowth happen, they're probably fragmented.
4) Set autogrow based on:
4a) How much space will last how long (i.e. a month, six months, a day's).
4b) How much time you can afford to take during actual operations for autogrow (primarily affects log files, once you have instant file init enabled); a busy OLTP database might need autogrowth of log files to take less than half a second (and thus happen frequently if you fail step 2), or you might want autogrowth of logs files to take a couple minutes on a reporting database that's used for long-running queries (who cares if it returns in 38 minutes instead of 36 minutes this time).
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply