February 3, 2014 at 4:22 pm
I have an alert that checks sys.master_files.size. A database grew 20gigs in less than 24 hours. I verified the mdf file thinking maybe the alert was wrong. I want to investigate this growth as it is unusual but am not sure where to start. We are using full recovery model. Last full backup was taken last night. I checked around with some users who regularly use the database and they weren't doing anything that would have added that much data to the file, or so they claim.
I've done some googling and all the articles I find talk about shrinking the file but not about determining how it came to be in the first place. Any help is appreciated. Sorry if I didn't post enough info, accidental DBA here.
February 3, 2014 at 4:30 pm
So, the first question I have is:
Do you have a ton of free space inside the database now?
Next question
Did you check for any "maintenance" jobs that might have run overnight? If you created (or if somebody created) a maintenance plan that rebuilds indexes - then that can cause significant data file growth.
Next question
Do you have something monitoring to trap the sql that caused the growth? You can put low level tracking in place that would trap the statements that are running when the growth occurs. You can also query the default trace to find who caused the growth and at what time it happened.
If needed, I have an article here at SSC that can show you a mechanism for trapping the tsql running when the file grew.
Final question
Do you have a baseline of what the table sizes were before this huge growth?
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
February 3, 2014 at 4:44 pm
24GB free now.
63GB is the size.
Maintenance jobs run late Saturday / early Sunday. The alert was not triggered Sunday after these ran.
I don't have monitoring in place to trap statements. I'm only familiar with doing this through profiler.
I didn't manually check size yesterday for a baseline but the alert is triggered when size > 30GB.
February 3, 2014 at 5:24 pm
You can try this to find exactly when the file grew and if it was grown manually or automatically. You can also tell if it was done via ssms and by whom.
DECLARE @Path VARCHAR(512)
,@StartTimeDATETIME
,@EndTimeDATETIME
/* These date ranges will need to be changed */
SET @StartTime = '2014-02-1 11:00:00'
SET @EndTime = '2014-02-13 23:59:59'
SELECT
gt.DatabaseName,
gt.TextData,
gt.HostName,
gt.ApplicationName,
gt.LoginName,
gt.NTDomainName,
gt.StartTime,
gt.EndTime,
gt.EventClass,
te.name
FROM sys.fn_trace_gettable(CONVERT(VARCHAR(150),
( SELECT TOP 1 f.[value]
FROM sys.fn_trace_getinfo(NULL) f
WHERE f.property = 2
)), DEFAULT) gt
INNER JOIN sys.trace_events te
ON gt.EventClass = te.trace_event_id
INNER JOIN sys.trace_categories tc
ON te.category_id = tc.category_id
INNER JOIN sys.trace_subclass_values tv
ON gt.EventSubClass = tv.subclass_value
AND gt.EventClass = tv.trace_event_id
WHERE 1 = 1
AND ObjectType = 16964 /* Database */
AND gt.StartTime BETWEEN @StartTime AND @EndTime
AND gt.EventSubClass = 1
AND (gt.EventClass = 164
/* 164 = Object:Altered -- in case the file was manually grown by somebody*/
OR te.name IN ('Data File Auto Grow','Log File Auto Grow')
);
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
February 4, 2014 at 7:31 am
SQLRNNR (2/3/2014)
You can try this to find exactly when the file grew and if it was grown manually or automatically. You can also tell if it was done via ssms and by whom.
DECLARE @Path VARCHAR(512)
,@StartTimeDATETIME
,@EndTimeDATETIME
/* These date ranges will need to be changed */
SET @StartTime = '2014-02-1 11:00:00'
SET @EndTime = '2014-02-13 23:59:59'
SELECT
gt.DatabaseName,
gt.TextData,
gt.HostName,
gt.ApplicationName,
gt.LoginName,
gt.NTDomainName,
gt.StartTime,
gt.EndTime,
gt.EventClass,
te.name
FROM sys.fn_trace_gettable(CONVERT(VARCHAR(150),
( SELECT TOP 1 f.[value]
FROM sys.fn_trace_getinfo(NULL) f
WHERE f.property = 2
)), DEFAULT) gt
INNER JOIN sys.trace_events te
ON gt.EventClass = te.trace_event_id
INNER JOIN sys.trace_categories tc
ON te.category_id = tc.category_id
INNER JOIN sys.trace_subclass_values tv
ON gt.EventSubClass = tv.subclass_value
AND gt.EventClass = tv.trace_event_id
WHERE 1 = 1
AND ObjectType = 16964 /* Database */
AND gt.StartTime BETWEEN @StartTime AND @EndTime
AND gt.EventSubClass = 1
AND (gt.EventClass = 164
/* 164 = Object:Altered -- in case the file was manually grown by somebody*/
OR te.name IN ('Data File Auto Grow','Log File Auto Grow')
);
It doesn't appear to work in 2005. It doesn't find any of the growth events that you can see in the "diskusage reports" for a given database. Would that be a true statement?
--Jeff Moden
Change is inevitable... Change for the better is not.
February 4, 2014 at 7:43 am
Jeff Moden (2/4/2014)
SQLRNNR (2/3/2014)
You can try this to find exactly when the file grew and if it was grown manually or automatically. You can also tell if it was done via ssms and by whom.
DECLARE @Path VARCHAR(512)
,@StartTimeDATETIME
,@EndTimeDATETIME
/* These date ranges will need to be changed */
SET @StartTime = '2014-02-1 11:00:00'
SET @EndTime = '2014-02-13 23:59:59'
SELECT
gt.DatabaseName,
gt.TextData,
gt.HostName,
gt.ApplicationName,
gt.LoginName,
gt.NTDomainName,
gt.StartTime,
gt.EndTime,
gt.EventClass,
te.name
FROM sys.fn_trace_gettable(CONVERT(VARCHAR(150),
( SELECT TOP 1 f.[value]
FROM sys.fn_trace_getinfo(NULL) f
WHERE f.property = 2
)), DEFAULT) gt
INNER JOIN sys.trace_events te
ON gt.EventClass = te.trace_event_id
INNER JOIN sys.trace_categories tc
ON te.category_id = tc.category_id
INNER JOIN sys.trace_subclass_values tv
ON gt.EventSubClass = tv.subclass_value
AND gt.EventClass = tv.trace_event_id
WHERE 1 = 1
AND ObjectType = 16964 /* Database */
AND gt.StartTime BETWEEN @StartTime AND @EndTime
AND gt.EventSubClass = 1
AND (gt.EventClass = 164
/* 164 = Object:Altered -- in case the file was manually grown by somebody*/
OR te.name IN ('Data File Auto Grow','Log File Auto Grow')
);
It doesn't appear to work in 2005. It doesn't find any of the growth events that you can see in the "diskusage reports" for a given database. Would that be a true statement?
I will test it on a 2005 instance.
While I test that some more - I would be curious to know if the default trace has been disabled on that box.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
February 4, 2014 at 8:28 am
Ahhh.... I found the problem. It has to do with which trace file is selected and I've got it working on my prod server. I'm redacting the code and I'll post it here sometime between now and later. 😀
--Jeff Moden
Change is inevitable... Change for the better is not.
February 4, 2014 at 8:35 am
Jeff Moden (2/4/2014)
Ahhh.... I found the problem. It has to do with which trace file is selected and I've got it working on my prod server. I'm redacting the code and I'll post it here sometime between now and later. 😀
OIC. I also use the following when investigating the default trace from time to time
DECLARE @Path VARCHAR(512)
SELECT @Path = REVERSE(SUBSTRING(REVERSE([PATH]),
CHARINDEX('\', REVERSE([path])), 260)) + N'LOG.trc'
FROM sys.traces
WHERE is_default = 1;
...
Select blah blah blah
FROM ::fn_trace_gettable( @Path, DEFAULT ) gt
etc etc etc
This might behave slightly differently in 2005 in regards to that file selection.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
February 4, 2014 at 10:13 am
check the growth factor on the file. maybe a small amount of extra data led to a huge growth.
the standard reports in ssms (disk usage) will show when the growth(s) happened and by how much.
---------------------------------------------------------------------
February 4, 2014 at 10:29 am
Thanks for all of the responses. I have determined what was causing the growth which was caused by an application that was having issues.
February 4, 2014 at 10:40 am
jiayi345 (2/4/2014)
Thanks for all of the responses. I have determined what was causing the growth which was caused by an application that was having issues.
Cool - good to hear.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply