February 5, 2016 at 7:56 am
We had a situation where my DB wasn't responsive anymore and our application went into outage.
We had our MDF auto-growth set to 1GB and LDF set to 10%.
Our Monitoring Tool had the following error:
Autogrow of file "DB_NAME" in database "DB_NAME" was cancelled by user or timed out after 17152 milliseconds. Use ALTER DATABASE to set a smaller FILEGROWTH value
for this file or to explicitly set a new file size.
This was making the application non-responsive for any insert to the DB.
Therefore we switched MDF auto-growth to 512MB and LDF to 512MB and it started again.
My questions are;
1) Why did this start happening now when the application has been live for more than a year?
2) What causes this issue?
3) Is backing up transaction log every 15 minutes a best practice? If not, what would be better.
4) When does the file start the auto-growth? Does it wait for the space available to be 0 or it does it prior to this?
Thank you in advance for all your help.
February 5, 2016 at 8:01 am
have you enabled the local security policy "perform volume maintenance tasks" for the sql server service account?
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
February 5, 2016 at 8:02 am
sgrimard (2/5/2016)
Autogrow of file "DB_NAME" in database "DB_NAME" was cancelled by user or timed out after 17152 milliseconds. Use ALTER DATABASE to set a smaller FILEGROWTH valuefor this file or to explicitly set a new file size.
Which file was having the growth issue, data file or log file, 10% growth for t log is generally a bad idea
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
February 5, 2016 at 8:06 am
I don't think we have activated that policy. And it did not specify if it was the LDF or MDF.
So turn off Auto-Growth on LDF? What would you set it at?
February 5, 2016 at 8:07 am
sgrimard (2/5/2016)
My questions are;1) Why did this start happening now when the application has been live for more than a year?
2) What causes this issue?
3) Is backing up transaction log every 15 minutes a best practice? If not, what would be better.
4) When does the file start the auto-growth? Does it wait for the space available to be 0 or it does it prior to this?
Thank you in advance for all your help.
1. It is happening now because at this moment the database is experiencing shortage of space
2. The cause (of datafile growing) is adding extra data or the cause (of logfile growing) is long running queries or log backups not taken frequently enough
3. Transaction log backups every 15 minutes is done by many DBA's, but there is no 'best practice'. The time between log backups must be determined by the acceptable loss of data in case of disaster.
4. Filegrowth will occur when the available space in the file is 0. Best practice is to have proper capacity planning in place and manually increase the size during off-peak moments and to prevent autogrowth from happening.
February 5, 2016 at 8:13 am
sgrimard (2/5/2016)
I don't think we have activated that policy. And it did not specify if it was the LDF or MDF.So turn off Auto-Growth on LDF? What would you set it at?
Set the growth to such a size that it will be finished in a short amount of time, but not that small that it will cause too much fragmentation and/or too many VLF's (for log). But try to prevent autogrwoth from happening and only use is as a last resort.
February 5, 2016 at 8:14 am
Did someone cancel the growth operation? If not, then you may have IO storage issues. The files need to be able to grow if they need space in an emergency. Just like adding data to an Excel file causes it to grow.
This is an expensive operation, and as HanShe has pointed out, you want to keep space in here. If you have a monitoring tool, it should report on space in the database and log file, and you should be growing these manually when space is low. I would suggest you grow them enough to at least handle 3 months worth of data growth.
Your log file size depends on workload and log backup frequency. You need what you need here. If you backup the log more often, you can use less space, but your total log backup space for a day will be the same. It's a question of how many files and how large each is.
Here are are a few pieces that might help:
http://www.sqlservercentral.com/articles/134523/
February 5, 2016 at 8:18 am
sgrimard (2/5/2016)
I don't think we have activated that policy. And it did not specify if it was the LDF or MDF.
This code will read the default trace and extract the autofilegrowth events from it. It will specify how many autogrowth events took place and on which file(s).
-- declareer variabele
declare @value sql_variant
-- bepaal huidige tracefile van default trace (alleen SQL 2005 of hoger)
SELECT
@value = value
FROM
fn_trace_getinfo(default)
WHERE
traceid = 1
and property = 2;
-- lees de tracefile uit voor event 92 (Data File Auto Grow) en 93 (Log File Auto Grow) en groepeer resultaat per database file
SELECT
DatabaseName
, FileName
, min(EndTime) as FirstAutogrowth
, max(EndTime) as LastAutogrowth
, count(EndTime) as Autogrowth_Actions
FROM
fn_trace_gettable(cast(@value as NVARCHAR(200)),1)
WHERE
EventClass IN (92, 93)
GROUP BY
DatabaseName
, FileName
ORDER BY
max(EndTime) desc
-- show all files for each database with autogrowth action
select
db.name as DatabaseName
, altfiles.filename
--, altfiles.growth
, 8*altfiles.growth/1024 as growth_MB
, max(trace.endtime) as LastAutogrowth
from
master.sys.databases db
right outer join master.sys.sysaltfiles altfiles
on db.database_id = altfiles.dbid
left outer join fn_trace_gettable(cast(@value as NVARCHAR(200)),1) trace
on db.name = trace.DatabaseName
and altfiles.name = trace.FileName
and EventClass IN (92, 93)
group by
db.name
, altfiles.filename
, altfiles.growth
, trace.FileName
having
db.name IN (select DatabaseName
from fn_trace_gettable(cast(@value as NVARCHAR(200)),1)
where EventClass IN (92, 93)
)
order by
db.name
, max(trace.endtime) desc
, altfiles.filename
February 5, 2016 at 8:18 am
Perry Whittle (2/5/2016)
have you enabled the local security policy "perform volume maintenance tasks" for the sql server service account?
Seconded, I actually forgot to set this on a build I was doing 2 months ago. Took 20 mins to create a 8GB tempdb file, enabled the policy and the file was created instantly.
February 5, 2016 at 8:23 am
sgrimard (2/5/2016)
I don't think we have activated that policy. And it did not specify if it was the LDF or MDF.So turn off Auto-Growth on LDF? What would you set it at?
The logical file name it reported would have told you data or log file 😉
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
February 5, 2016 at 8:38 am
Thank you so much everyone.. I will look into this immediately.
Thanks again for all your answers, I appreciate the quick turnaround..
February 5, 2016 at 1:35 pm
HanShi (2/5/2016)
This code will read the default trace and extract the autofilegrowth events from it. It will specify how many autogrowth events took place and on which file(s).
Unfortunately if auto-grow has failed this code will show nothing.
February 6, 2016 at 1:11 am
Phil Parkin (2/5/2016)
maybe turning on IFI might help you.
As long as it's not a log file growth issue
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply