-
Notifications
You must be signed in to change notification settings - Fork 4
Expand file tree
/
Copy pathDefault Trace - Queries.sql
More file actions
80 lines (59 loc) · 2.32 KB
/
Copy pathDefault Trace - Queries.sql
File metadata and controls
80 lines (59 loc) · 2.32 KB
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
/* THIS SECTION READS THE ENTIRE DEFAULT TRACE FILE */
DECLARE @filename nvarchar(1000);
-- Get the name of the current default trace
SELECT @filename = cast(value as nvarchar(1000))
FROM ::fn_trace_getinfo(default)
WHERE traceid = 1 and property = 2;
-- view current trace file
SELECT *
FROM ::fn_trace_gettable(@filename, default) AS ftg
INNER JOIN sys.trace_events AS te ON ftg.EventClass = te.trace_event_id
WHERE starttime > '2015-03-24'
AND ftg.TextData LIKE '%RESTORE%'
ORDER BY ftg.StartTime
/* THIS SECTION GETS SCHEMA CHANGES */
DECLARE @filename nvarchar(1000);
-- Get the name of the current default trace
SELECT @filename = cast(value as nvarchar(1000))
FROM ::fn_trace_getinfo(default)
WHERE traceid = 1 and property = 2;
-- view current trace file
SELECT *
FROM ::fn_trace_gettable(@filename, default) AS ftg
INNER JOIN sys.trace_events AS te ON ftg.EventClass = te.trace_event_id
WHERE (ftg.EventClass = 46 or ftg.EventClass = 47)
and DatabaseName <> 'tempdb'
and EventSubClass = 0
ORDER BY ftg.StartTime;
/* THIS SECTION GETS AUTOGROWTH EVENTS */
DECLARE @filename nvarchar(1000);
-- Get the name of the current default trace
SELECT @filename = cast(value as nvarchar(1000))
FROM ::fn_trace_getinfo(default)
WHERE traceid = 1 and property = 2;
-- Find auto growth events in the current trace file
SELECT
ftg.StartTime
,te.name as EventName
,DB_NAME(ftg.databaseid) AS DatabaseName
,ftg.Filename
,(ftg.IntegerData*8)/1024.0 AS GrowthMB
,(ftg.duration/1000)as DurMS
FROM ::fn_trace_gettable(@filename, default) AS ftg
INNER JOIN sys.trace_events AS te ON ftg.EventClass = te.trace_event_id
WHERE (ftg.EventClass = 92 -- Date File Auto-grow
OR ftg.EventClass = 93) -- Log File Auto-grow
ORDER BY ftg.StartTime
/* THIS SECTION GETS SECURITY CHANGES */
DECLARE @filename nvarchar(1000);
-- Get the name of the current default trace
SELECT @filename = cast(value as nvarchar(1000))
FROM ::fn_trace_getinfo(default)
WHERE traceid = 1 and property = 2;
-- process all trace files
SELECT *
FROM ::fn_trace_gettable(@filename, default) AS ftg
INNER JOIN sys.trace_events AS te ON ftg.EventClass = te.trace_event_id
WHERE ftg.EventClass
in (102,103,104,105,106,108,109,110,111)
ORDER BY ftg.StartTime