Black Box Trace
In earlier versions of SQL Server, there have been situations where root cause analysis was never possible because data saved by SQL was just confined to SQL Server Error Logs. From SQL Server 2005, there is a default trace that runs in the background as soon as SQL Server is installed. We call it black box trace. It is mostly used for root cause analysis which is similar to the black box flight recorder in airplanes. This trace is designed to capture very minimal but essentially critical data. It can be identified by its default column in sys.traces catalog view. This is a SQL Server generated server side trace which starts with SQL Server unless a DBA explicitly stops/disables it.
We need to remember that default trace doesn't keep information forever. The maximum size per trace file is 20 MB and SQL Server keeps only 5 traces. Every restart also resets the trace. If there are many activities happening on the server, you might want to keep a schedule job to move older files to some archived location.
Did you know that you can get the following information from a black box trace?
- Who/When dropped or altered an object in my database?
- What is the growth pattern of my database?
- Who/When/How configuration settings were changed?
To understand the power, we should first know the events which are being captured by the default trace. Here is the query which we can use to get list of events
SELECT distinct te.name AS trace_event_name
FROM sys.traces t
CROSS APPLY fn_trace_geteventinfo(t .id) AS e
JOIN sys.trace_events te
ON te.trace_event_id = e.eventid
where is default = 1
Here is the list:
| Audit Add DB User Event |
| Audit Add Login to Server Role Event |
| Audit Add Member to DB Role Event |
| Audit Add Role Event |
| Audit Add login Event |
| Audit Backup/Restore EventAudit Change Audit Event |
| Audit Change Database Owner |
| Audit Database Scope GDR EventAudit DBCC Event |
| Audit Login Change Property Event |
| Audit Login Failed |
| Audit Login GDR Event |
| Audit Schema Object GDR Event |
| Audit Schema Object Take Ownership Event |
| Audit Server Alter Trace Event |
| Audit Server Starts And Stops |
| Data File Auto Grow |
| Data File Auto ShrinkDatabase Mirroring State Change |
| ErrorLog |
| FT:Crawl Started |
| FT:Crawl Stopped |
| Hash Warning |
| Log File Auto Grow |
| Log File Auto Shrink |
| Missing Column Statistics |
| Missing Join Predicate |
| Object:Altered |
| Object:Created |
| Object:Deleted |
| Plan Guide Unsuccessful |
| Server Memory Change |
| Sort Warnings |
We can use these events to find out various types of information. Here are three queries which can identify things that are useful to a DBA.
When/Who dropped or altered an object in the database?
Here is the query that can show all DDL across SQL Server which is available in trace files.
set nocount on
go
declare @curr_tracefilename varchar(500
declare @base_tracefilename varchar(500
declare @indx int
select @curr_tracefilename = path from sys.traces where is_default = 1
set @curr_tracefilename = reverse(@curr_tracefilename)
select @indx = PATINDEX('%\%', @curr_tracefilename)
set @curr_tracefilename = reverse(@curr_tracefilename)
set @base_tracefilename = LEFT( @curr_tracefilename,len(@curr_tracefilename) – @indx) + '\log.trc'
select ObjectName
, DatabaseName
, StartTime
, ServerName
, LoginName
, ApplicationName
, DDL = case EventClass
WHEN 46 THEN 'CREATE'
WHEN 47 THEN 'DROP'
WHEN 164 THEN 'ALTER'
END
from ::fn_trace_gettable( @base_tracefilename, default )
where EventClass in (46,47,164)
and EventSubclass = 0
and DatabaseID <> 2 — tempdb and add other databases if need be
and ObjectType <> 21587 — stats
go
set nocount off
go
We can filter for specific events for our database using the “DatabaseID” field.
What is the growth pattern of my database?
The key here is the event class which is 92 and 93 for Data File and Auto File auto grow events.
SET NOCOUNT ON
GO
DECLARE @curr_tracefilename VARCHAR(500
DECLARE @base_tracefilename VARCHAR(500
DECLARE @indx INT
SELECT @curr_tracefilename = path
FROM sys.traces
WHERE is_default = 1
SET @curr_tracefilename = reverse(@curr_tracefilename)
SELECT @indx = PATINDEX('%\%', @curr_tracefilename)
SET @curr_tracefilename = reverse(@curr_tracefilename)
SET @base_tracefilename = LEFT(@curr_tracefilename, len(@curr_tracefilename) – @indx) + '\log.trc'
SELECT DatabaseName
,StartTime
,ServerName
,LoginName
,ApplicationName
,WhichFile = CASE EventClass
WHEN 92
THEN 'Log File Auto Grow'
WHEN 93
THEN 'Data File Auto Grow'
END
,Duration / 1000000.0 'Sec'
FROM::fn_trace_gettable(@base_tracefilename, DEFAULT) b
WHERE EventClass IN (92,93) — ('Log File Auto Grow', 'Data File Auto Grow')
ORDER BY b.StartTime DESC
GO
SET NOCOUNT OFF
GO
As a DBA you want to be in control of database growth patterns. It is not a good practice to allow databases to grow randomly.
Who/When/How configuration settings were changed?
Whenever there is a configuration changed using sp_configure, it is logged in SQL ERRORLOG in the below format:
Configuration option '%ls' changed from %ld to %ld. Run the RECONFIGURE statement to install
Below piece of the code would read default traces and look for the text and show information in meaningful format.
DECLARE @curr_tracefilename VARCHAR(500
DECLARE @base_tracefilename VARCHAR(500
DECLARE @indx INT
DECLARE @Default_Trace_Data TABLE (
textdata NVARCHAR(MAX) collate database_default
,login_name SYSNAME collate database_default
,start_time DATETIME
,event_class INT
,ApplicationName SYSNAME
SELECT @curr_tracefilename = path
FROM sys.traces
WHERE is_default = 1
SET @curr_tracefilename = reverse(@curr_tracefilename)
SELECT @indx = PATINDEX('%\%', @curr_tracefilename)
SET @curr_tracefilename = reverse(@curr_tracefilename)
SET @base_tracefilename = LEFT(@curr_tracefilename, len(@curr_tracefilename) – @indx) + '\log.trc'
INSERT INTO @Default_Trace_Data
SELECT TextData
,LoginName
,StartTime
,EventClass
,ApplicationName
FROM::fn_trace_gettable(@base_tracefilename, DEFAULT)
WHERE (
(
EventClass = 22
AND Error = 15457
)
)
SELECT CASE event_class
WHEN 22
THEN substring(textdata, 58, patindex('%changed from%', textdata) – 60)
END AS config_option
,start_time
,login_name
,CASE event_class
WHEN 22
THEN substring(substring(textdata, patindex('%changed from%', textdata), len(textdata) – patindex('%changed from%', textdata)), patindex('%changed from%', substring(textdata, patindex('%changed from%', textdata), len(textdata) – patindex('%changed from%', textdata))) + 13, patindex('%to%', substring(textdata, patindex('%changed from%', textdata), len(textdata) – patindex('%changed from%', textdata))) – patindex('%from%', substring(textdata, patindex('%changed from%', textdata), len(textdata) – patindex('%changed from%', textdata)))– 6)
END AS old_value
,CASE event_class
WHEN 22
THEN substring(substring(textdata, patindex('%changed from%', textdata), len(textdata) – patindex('%changed from%', textdata)), patindex('%to%', substring(textdata, patindex('%changed from%', textdata), len(textdata) – patindex('%changed from%', textdata))) + 3, patindex('%. Run%', substring(textdata, patindex('%changed from%', textdata), len(textdata) – patindex('%changed from%', textdata))) – patindex('%to%', substring(textdata, patindex('%changed from%', textdata), len(textdata) – patindex('%changed from%', textdata))) – 3)
END AS new_value
,ApplicationName
FROM @Default_Trace_Data
ORDER BY start_time DESC
From these examples, I hope that you are able to appreciate the power of default traces. There are a number of other events that are captured and we can build similar queries to read them, too.
Next Steps
You may also be interested to read From Zero to Hero with SQL Server Management Studio, a whitepaper by Pinal Dave. Read this paper to learn 12 cool tips for working with SQL Management Studio.
Embarcadero Rapid SQL is the intelligent SQL IDE empowering database developers and DBAs the ability to create high-performing SQL code on all major databases from a single interface. This toolset simplifies SQL scripting, query building, object management, debugging and version control with intuitive, innovative tools.
Learn more about Rapid SQL and try Rapid SQL for free.