Find SQL Server Auto Growth Events from Default Trace
DECLARE @path NVARCHAR(260);
SELECT @path = REVERSE(SUBSTRING(REVERSE([path]), CHARINDEX('\', REVERSE([path])), 260))+N'log.trc'
FROM sys.traces
WHERE is_default = 1;
SELECT TOP 10000 Database_Name = DB_NAME(DatabaseID),
DatabaseName,
starttime,
endtime,
textdata = CAST(textdata AS VARCHAR(4000)),
Duration_Sec = CAST(duration / 1000 / 1000.0 AS DEC(10, 1)),
CPU_Sec = CAST(cpu / 1000.0 AS DEC(10, 1)),
Reads_K = CAST(reads / 1000.0 AS DEC(10, 0)),
Writes_K = CAST(writes / 1000.0 AS DEC(10, 1)),
hostname,
LoginName,
NTUserName,
ApplicationName = CASE LEFT(ApplicationName, 29)
WHEN 'SQLAgent - TSQL JobStep (Job '
THEN 'SQLAgent Job: '+
(
SELECT name
FROM msdb..sysjobs sj
WHERE SUBSTRING(ApplicationName, 32, 32) = (SUBSTRING(sys.fn_varbintohexstr(sj.job_id), 3, 100))
)+' - '+SUBSTRING(ApplicationName, 67, LEN(ApplicationName)-67)
ELSE ApplicationName
END,
SPID,
TE.*
FROM sys.fn_trace_gettable(@path, DEFAULT) T
LEFT JOIN sys.trace_events TE ON T.EventClass = TE.trace_event_id
WHERE endtime > DATEADD(DD, -7, GETDATE())
--AND Duration > 1000000 * 15 --in seconds
--AND CPU > 1000 * 10 --in seconds
--AND Writes > 10000
--AND Reads > 1000000
--AND te.category_id = 2 --Auto Grow/Shrink found in default trace
--AND t.databaseid = DB_ID('master')
--AND t.DatabaseName = 'tempdb'
--AND t.LoginName <> 'shood'
--AND t.textdata like '%%'
ORDER BY endTime DESC,
starttime DESC;
0 Comments:
Post a Comment
Subscribe to Post Comments [Atom]
<< Home