SQL Server's Default Trace

Are you familiar with SQL Server's default trace setting? It can be helpful with finding basic who/when type information on major events. For example, you may want to know who was creating and dropping databases on a given instance.

SQL Server has a couple of options that might help you find out more about when/by who the database is being created and dropped. One is Policy Based Management but you would need to configure it ahead of time. Another option is to run a profiler trace that captures information such as CREATE, ALTER, DROP DATABASE. Some of the DMVs might have the execution information if you capture it fast enough after it happens. XEvents can be used in SQL 2008 to find all sorts of information. However, the one that might be most appropriate in this case is the Default Trace.

1) Make sure the default trace is enabled in your configuration options for this instance. If it is not enabled, you can enable it through the sp_configure settings.

-- Check to see if the default trace is enabled (0=off, 1=on)

EXEC sp_configure 'default trace enabled'

GO

-- Enabled the default trace

EXEC sp_configure 'default trace enabled', 1

GO

RECONFIGURE

2) The trace files will eventually overwrite themselves, so check for the output soon after the problem occurs (perhaps make periodic copies of the files). They will be under the log directory where SQL Server is installed. For example, for my SQL 2008 instance named WASH the output files are in C:\Program Files\Microsoft SQL Server\MSSQL10.WASH\MSSQL\Log. The files will be named log_xxx.trc and there will be up to 5 of them.

3) Find the trace which covers the time period when the database was created or dropped. You can either open it in the Profiler GUI or you can use the query below to pull out the appropriate data. Look for the create and/or drop events and see who executed them from what workstation and at what time. Some applications will send their "application name" so you may be able to tell that as well.

Key Points:

· You cannot control what is captured by the default trace, how many files it captures before rolling over, or any other options. Your only option is to turn it on or off. If you want a similar trace that differs in any way you can create your own and configure it to start when SQL Server starts (or whatever time period is appropriate).

· The trace file name/number will continue to increase until you delete the files.

· The trace does NOT capture all events, it is very lightweight.

References:

· SQL Server 2008 Internals - Chapter 1 page 73

· Searching for a Trace - Solving the mystery of SQL Server 2005's default trace enabled option https://www.sqlmag.com/Articles/ArticleID/48939/pg/1/1.html

· SQL Server Default Trace https://blogs.technet.com/beatrice/archive/2008/04/29/sql-server-default-trace.aspx

· Default Trace in SQL Server 2005 https://blogs.technet.com/vipulshah/archive/2007/04/16/default-trace-in-sql-server-2005.aspx

· Default Trace in SQL Server 2005https://www.mssqltips.com/tip.asp?tip=1111

Query:

-- Example of using the default trace to find out more about who/when/why a database is dropped or created

-- Get current file name for existing traces

SELECT * FROM ::fn_trace_getinfo(0)

-- CHANGE THIS VALUE to the current file name

DECLARE @Path nvarchar(2000)

SELECT  @Path = 'C:\Program Files\Microsoft SQL Server\MSSQL10.WASH\MSSQL\Log\log_120.trc'

-- Get information most relevant to CREATE/DROP database

SELECT SPID, LoginName, NTUserName, NTDomainName, HostName, ApplicationName, StartTime, ServerName, DatabaseName

      ,CASE EventClass

            WHEN 46 THEN 'CREATE'

            WHEN 47 THEN 'DROP'

            ELSE 'OTHER'

       END AS EventClass

      , CASE ObjectType

            WHEN 16964 THEN 'DATABASE'

            ELSE 'OTHER'

       END AS ObjectType

      --,*

FROM fn_trace_gettable

(@Path, default)

WHERE ObjectType = 16964 /* Database */ AND EventSubClass = 1 /* Committed */

ORDER BY StartTime

GO

/* BOL

== Event Class

46 Object:Created

 Indicates that an object has been created, such as for CREATE INDEX, CREATE TABLE, and CREATE DATABASE statements.

47 Object:Deleted

 Indicates that an object has been deleted, such as in DROP INDEX and DROP TABLE statements.

 == Object Type

 16964 Database

== EventSubClass

 int Type of event subclass.

0=Begin

1=Commit

2=Rollback

 */