3 Things You Didn’t Know About Black Box Trace

by Dec 15, 2014

Categories

Tags

Administration agent-based monitoring Agentless Monitoring alert responses alert thresholds alerting Alerts Amazon Aurora Amazon EC2 Amazon RDS Amazon RDS / Aurora Amazon RDS for SQL Server Amazon Redshift Amazon S3 Amazon Web Services (AWS) Analytics application monitoring Aqua Data Studio automation availability Azure Azure SQL Database azure sql managed instance Azure VM backup Backup and recovery backup and restore backup compression backup status Backup Strategy backups big data Blocking bug fixes business architecture business data objects business intelligence business process modeling business process models capacity planning change management cloud cloud database cloud database monitoring cloud infrastructure cloud migration cloud providers Cloud Readiness Cloud Services cloud storage cloud virtual machine cloud VM clusters code completion collaboration compliance compliance audit compliance audits compliance manager compliance reporting conference configuration connect to database cpu Cross Platform custom counters Custom Views customer survey customer testimonials Dark Theme dashboards data analysis Data Analytics data architect data architecture data breaches Data Collector data governance data lakes data lineage data management data model data modeler data modeling data models data privacy data protection data security data security measures data sources data visualization data warehouse database database administration database administrator database automation database backup database backups database capacity database changes database community database connection database design database developer database developers database development database diversity Database Engine Tuning Advisor database fragmentation database GUI database IDE database indexes database inventory management database locks database management database migration database monitoring database navigation database optimization database performance Database Permissions database platforms database profiling database queries database recovery database replication database restore database schema database security database support database synchronization database tools database transactions database tuning database-as-a-service databases DB Change Manager DB Optimizer DB PowerStudio DB2 DBA DBaaS DBArtisan dBase DBMS DDL Debugging defragmentation Demo diagnostic manager diagnostics dimensional modeling disaster recovery Download drills embedded database Encryption End-user Experience entity-relationship model ER/Studio ER/Studio Data Architect ER/Studio Enterprise Team Edition events execution plans free tools galera cluster GDPR Getting Started Git GitHub Google Cloud Hadoop Healthcare high availability HIPAA Hive hybrid clouds Hyper-V IDERA IDERA ACE Index Analyzer index optimization infrastructure as a service (IaaS) infrastructure monitoring installation Integrated Development Environment interbase Inventory Manager IT infrastructure Java JD Edwards JSON licensing load test load testing logical data model macOS macros managed cloud database managed cloud databases MariaDB memory memorystorage memoryusage metadata metric baselines metric thresholds Microsoft Azure Microsoft Azure SQL Database Microsoft PowerShell Microsoft SQL Server Microsoft Windows MongoDB monitoring Monitoring Tools Monyog multiple platforms MySQL news newsletter NoSQL Notifications odbc optimization Oracle PeopleSoft performance Performance Dashboards performance metrics performance monitoring performance schema performance tuning personally identifiable information physical data model Platform platform as a service (PaaS) PostgreSQL Precise Precise for Databases Precise for Oracle Precise for SQL Server Precise Management Database (PMDB) product updates Project Migration public clouds Query Analyzer query builder query monitor query optimization query performance Query Store query tool query tuning query-level waits Rapid SQL rdbms real time monitoring Real User Monitoring recovery regulations relational databases Releases Reporting Reports repository Restore reverse engineering Roadmap sample SAP Scalability Security Policy Security Practices server monitoring Server performance server-level waits Service Level Agreement SkySQL slow query SNMP snowflake source control SQL SQL Admin Toolset SQL CM SQL code SQL coding SQL Compliance Manager SQL Defrag Manager sql development SQL Diagnostic Manager SQL Diagnostic Manager for MySQL SQL Diagnostic Manager for SQL Server SQL Diagnostic Manager Pro SQL DM SQL Doctor SQL Enterprise Job Manager SQl IM SQL Inventory Manager SQL Management Suite SQL Monitoring SQL Performance SQL Quality SQL query SQL Query Tuner SQL Safe Backup SQL script SQL Secure SQL Security Suite SQL Server sql server alert SQL Server Migration SQL Server Performance SQL Server Recommendations SQL Server Security SQL statement history SQL tuning SQL Virtual Database sqlmemory sqlserver SQLyog Storage Storage Performance structured data Subversion Support tempdb tempdb data temporal data Tips and Tricks troubleshooting universal data models universal mapping unstructured data Uptime Infrastructure Monitor user experience user permissions Virtual Machine (VM) web services webinar What-if analysis WindowsPowerShell

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.

Try Rapid SQLWe 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:

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.

Whitepaper: From Zero to HeroNext 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.