3 Things You Didn’t Know About Black Box Trace

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?

1. Who/When dropped or altered an object in my database?
2. What is the growth pattern of my database?
3. 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:

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.