patternsqlMinor
What caused the database [.mdf file] to grow suddenly
Viewed 0 times
mdfthefilewhatsuddenlydatabasegrowcaused
Problem
I am trying to find the cause which have bloated the mdf of a database from 20 GB to 100 GB.
So far i tried checking the autogrow events to find time, but could not find none using standard reports and even default trace files.
We don't have 3rd party monitoring tools to confirm if any maintenance job like rebuild would have done or any other process.
How can i find what caused the growth on this mdf?
So far i tried checking the autogrow events to find time, but could not find none using standard reports and even default trace files.
We don't have 3rd party monitoring tools to confirm if any maintenance job like rebuild would have done or any other process.
How can i find what caused the growth on this mdf?
Solution
This will find all of the autogrow activities in all of the errorlog files still in active sequence, from my answer here:
Now, once you've identified a suspect autogrowth event, you can see information like the application name and host name that caused the event. It's possible that you might capture other activity by the same SPID, but you can't rely on this. Just take a look for things that started or ended within an arbitrary window - this looks at 5 minutes before and 5 minutes after, and hard-codes the SPID observed above:
If you are rolling through 20 errorlog files per day, something is not configured correctly or you are performing way too much of something that is filling those log files with noise. IMHO.
DECLARE @path NVARCHAR(260);
SELECT
@path = REVERSE(SUBSTRING(REVERSE([path]),
CHARINDEX(CHAR(92), REVERSE([path])), 260)) + N'log.trc'
FROM sys.traces
WHERE is_default = 1;
SELECT
DatabaseName,
HostName,
ApplicationName,
[FileName],
SPID,
Duration,
StartTime,
EndTime,
FileType = CASE EventClass WHEN 92 THEN 'Data' ELSE 'Log' END
FROM sys.fn_trace_gettable(@path, DEFAULT)
WHERE EventClass IN (92,93)
-- AND DatabaseName = N'AdventureWorks'
ORDER BY StartTime DESC;Now, once you've identified a suspect autogrowth event, you can see information like the application name and host name that caused the event. It's possible that you might capture other activity by the same SPID, but you can't rely on this. Just take a look for things that started or ended within an arbitrary window - this looks at 5 minutes before and 5 minutes after, and hard-codes the SPID observed above:
SELECT * FROM sys.fn_trace_gettable(@path, DEFAULT)
WHERE StartTime >= DATEADD(MINUTE, -5, '2018-03-19 11:41:16.970')
AND EndTime < DATEADD(MINUTE, 5, '2018-03-19 11:41:16.970')
-- AND TextData IS NOT NULL
AND SPID = 63;If you are rolling through 20 errorlog files per day, something is not configured correctly or you are performing way too much of something that is filling those log files with noise. IMHO.
Code Snippets
DECLARE @path NVARCHAR(260);
SELECT
@path = REVERSE(SUBSTRING(REVERSE([path]),
CHARINDEX(CHAR(92), REVERSE([path])), 260)) + N'log.trc'
FROM sys.traces
WHERE is_default = 1;
SELECT
DatabaseName,
HostName,
ApplicationName,
[FileName],
SPID,
Duration,
StartTime,
EndTime,
FileType = CASE EventClass WHEN 92 THEN 'Data' ELSE 'Log' END
FROM sys.fn_trace_gettable(@path, DEFAULT)
WHERE EventClass IN (92,93)
-- AND DatabaseName = N'AdventureWorks'
ORDER BY StartTime DESC;SELECT * FROM sys.fn_trace_gettable(@path, DEFAULT)
WHERE StartTime >= DATEADD(MINUTE, -5, '2018-03-19 11:41:16.970')
AND EndTime < DATEADD(MINUTE, 5, '2018-03-19 11:41:16.970')
-- AND TextData IS NOT NULL
AND SPID = 63;Context
StackExchange Database Administrators Q#201671, answer score: 6
Revisions (0)
No revisions yet.