{"id":1103,"date":"2011-03-14T00:00:00","date_gmt":"2011-03-14T00:00:00","guid":{"rendered":"https:\/\/test.simple-talk.com\/uncategorized\/the-default-trace-in-sql-server-the-power-of-performance-and-security-auditing\/"},"modified":"2026-03-18T13:36:50","modified_gmt":"2026-03-18T13:36:50","slug":"the-default-trace-in-sql-server-the-power-of-performance-and-security-auditing","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/databases\/sql-server\/performance-sql-server\/the-default-trace-in-sql-server-the-power-of-performance-and-security-auditing\/","title":{"rendered":"SQL Server Default Trace: Auditing Guide with Scripts"},"content":{"rendered":"<div id=\"pretty\">\n<p>The SQL Server default trace is a lightweight, always-on trace that ships enabled by default since SQL Server 2005. It records events across six categories &#8211; Database (file growths\/shrinks), Errors and Warnings, Full-Text, Objects (schema changes), Security Audit (login failures, permission changes), and Server events &#8211; rotating across five .trc files in the SQL Server installation directory.<\/p>\n<p>Because it\u2019s already running, the default trace is the fastest way to investigate \u201cwhat changed\u201d on a SQL Server instance without setting up Extended Events or a server-side trace. This guide provides ready-to-run T-SQL scripts for auditing every event category the default trace captures.<\/p>\n<h2>Introduction<\/h2>\n<p class=\"start\">SQL Server provides us with variety of tools for auditing. All of them have their advantages and pitfalls. The default trace, introduced in SQL Server 2005, has the great advantage of being switched on by default, and is usually already there to use. It provides comprehensive information about changes in the system.<\/p>\n<p>Firstly, let&#8217;s start by answering some basic questions:<\/p>\n<p><strong>What is the default trace?<\/strong> The default trace is enabled by default in SQL Server and is a minimum weight trace which consists by default of five trace files ( .trc) located in the SQL Server installation directory. The files are rolled over as time passes.<\/p>\n<p><strong>How do we know that the default trace is running<\/strong>? We can run the following script in order to find out if the default trace is running:<\/p>\n<pre class=\"theme:ssms2012 lang:tsql\">SELECT* FROM sys.configurations WHERE configuration_id = 1568\n<\/pre>\n<p><strong>If it is not enabled, how do we enable it?<\/strong> We can run this script in order to enable the default trace:<\/p>\n<pre class=\"theme:ssms2012 lang:tsql\">sp_configure 'show advanced options', 1;\nGO\nRECONFIGURE; \nGO\nsp_configure 'default trace enabled', 1;\nGO\nRECONFIGURE;\nGO\n<\/pre>\n<p><strong>What is logged in the Default Trace?<\/strong> If we open the Default trace file in Profiler and look at the trace definition we will see that events in 6 categories are captured: Database, Errors and Warnings, Full-Text, Objects, Security Audit and Server. Also, all available columns are selected for every sub-event.<\/p>\n<p class=\"illustration\"><img decoding=\"async\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/imported\/1254-Fig1.jpg\" alt=\"1254-Fig1.jpg\" \/><\/p>\n<p class=\"caption\">Figure 1: This is how the Default trace looks like<\/p>\n<p>So, how can we benefit from each audited category? In the following sections I will explain briefly what each category means, as well as some of the sub-events, and will provide essential scripts for auditing the events in the Default Trace.<\/p>\n<h2>Database Events<\/h2>\n<p>Let&#8217;s start with the first event: the Database. As we can see, the sub-events are pretty much self-explanatory &#8211; the growth and shrinkage of data and log files, together with the changes in mirroring status. It is important to monitor file growths and shrinkages; It would be a vast topic to explain why, but in an nutshell, it is because of possible performance issues. Every time a file is grown or shrunk, SQL Server will halt and wait for the disk system to make the file available again. And halt, in this case, means <strong>halt<\/strong>: no transactions processed until the action is completed.<\/p>\n<p>These are the database events that are monitored:<\/p>\n<ul>\n<li>Data file auto grow<\/li>\n<li>Data file auto shrink<\/li>\n<li>Database mirroring status change<\/li>\n<li>Log file auto grow<\/li>\n<li>Log file auto shrink<\/li>\n<\/ul>\n<p>Here is a script which will list the data file growths and shrinkages:<\/p>\n<pre class=\"theme:ssms2012 lang:tsql\">SELECT\u00a0 TE.name AS [EventName] ,\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 T.DatabaseName ,\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 t.DatabaseID ,\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 t.NTDomainName ,\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 t.ApplicationName ,\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 t.LoginName ,\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 t.SPID ,\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 t.Duration ,\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 t.StartTime ,\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 t.EndTime\nFROM\u00a0\u00a0\u00a0 sys.fn_trace_gettable(CONVERT(VARCHAR(150), ( SELECT TOP 1\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 f.[value]\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 FROM\u00a0\u00a0\u00a0 sys.fn_trace_getinfo(NULL) f\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 WHERE\u00a0\u00a0 f.property = 2\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0)), DEFAULT) T\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 JOIN sys.trace_events TE ON T.EventClass = TE.trace_event_id\nWHERE\u00a0\u00a0 te.name = 'Data File Auto Grow'\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 OR te.name = 'Data File Auto Shrink'\nORDER BY t.StartTime ;\u00a0\n<\/pre>\n<p>The output of the script will not tell you why the database grew, but will show you how long it took to grow. (Be careful about the value of the <strong>Duration<\/strong> column, since it might be in milliseconds or in microseconds, depending on the SQL Server version).<\/p>\n<p>Also, I would recommend extending this query to search for databases which took longer than, say, a second to grow (this is just a guideline).<\/p>\n<p>Here is another query which will return the log growths and log shrinking.<\/p>\n<pre class=\"theme:ssms2012 lang:tsql\">SELECT\u00a0 TE.name AS [EventName] ,\n\u00a0\u00a0\u00a0 \u00a0\u00a0\u00a0\u00a0T.DatabaseName ,\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 t.DatabaseID ,\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 t.NTDomainName ,\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 t.ApplicationName ,\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 t.LoginName ,\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 t.SPID ,\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 t.Duration ,\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 t.StartTime ,\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 t.EndTime\nFROM\u00a0\u00a0\u00a0 sys.fn_trace_gettable(CONVERT(VARCHAR(150), ( SELECT TOP 1\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 f.[value]\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 FROM\u00a0\u00a0\u00a0 sys.fn_trace_getinfo(NULL) f\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 WHERE\u00a0\u00a0 f.property = 2\n\u00a0\u00a0 \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0)), DEFAULT) T\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 JOIN sys.trace_events TE ON T.EventClass = TE.trace_event_id\nWHERE\u00a0\u00a0 te.name = 'Log File Auto Grow'\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 OR te.name = 'Log File Auto Shrink'\nORDER BY t.StartTime ;\u00a0\n<\/pre>\n<p>Also keep in mind that the query will not tell you if your junior DBA has been shrinking the data and log files. In the default trace we can find only the AUTO growth and shrink events and not the ones triggered by the ALTER DATABASE statement.<br \/><br \/><strong>Read also:<br \/><\/strong><a href=\"https:\/\/www.red-gate.com\/simple-talk\/databases\/sql-server\/database-administration-sql-server\/extended-events-as-a-private-investigator\/\">Extended Events as a monitoring tool<\/a><br \/><a href=\"https:\/\/www.red-gate.com\/simple-talk\/databases\/sql-server\/performance-sql-server\/sql-server-deadlocks-by-example\/\">SQL Server deadlocks by example<\/a><br \/><a href=\"https:\/\/www.red-gate.com\/simple-talk\/databases\/sql-server\/database-administration-sql-server\/sql-server-database-growth-and-autogrowth-settings\/\"><span data-sheets-root=\"1\">SQL Server database growth and autogrowth settings<\/span><\/a><\/p>\n<h2>Errors and Warnings<\/h2>\n<p>Now let&#8217;s move on to the next section of the events: the Errors and Warnings. As we can see, there is an abundance of information here.<\/p>\n<p>The <strong>Errorlog<\/strong> sub-event occurs when something is written to the SQL Server event log; <strong>Hash and Sort warnings<\/strong> happen generally when a sort or a hash match operation is spilled to disk (and since the disk subsystem is the slowest, then our queries become much slower.) <strong>Missing column statistics<\/strong> events will occur only when the &#8216;<em>Auto create statistics&#8217;<\/em> option is set to <strong>off<\/strong>. In this case SQL Server indicates that it might have chosen a bad execution plan. The missing join predicate occurs when two tables do not have a join predicate and when both tables have more than one row. This can result in a long running queries or unexpected results.<\/p>\n<p>These categories of errors and warnings are:<\/p>\n<ul>\n<li>Errorlog<\/li>\n<li>Hash warning<\/li>\n<li>Missing Column Statistics<\/li>\n<li>Missing Join Predicate<\/li>\n<li>Sort Warning<\/li>\n<\/ul>\n<p>Here is a script which will outline the errors:<\/p>\n<pre class=\"theme:ssms2012 lang:tsql\">SELECT\u00a0 TE.name AS [EventName] ,\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 T.DatabaseName ,\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 t.DatabaseID ,\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 t.NTDomainName ,\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 t.ApplicationName ,\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 t.LoginName ,\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 t.SPID ,\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 t.StartTime ,\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 t.TextData ,\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 t.Severity ,\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 t.Error\nFROM\u00a0\u00a0\u00a0 sys.fn_trace_gettable(CONVERT(VARCHAR(150), ( SELECT TOP 1\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 f.[value]\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 FROM\u00a0\u00a0\u00a0 sys.fn_trace_getinfo(NULL) f\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 WHERE\u00a0\u00a0 f.property = 2\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 )), DEFAULT) T\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 JOIN sys.trace_events TE ON T.EventClass = TE.trace_event_id\nWHERE\u00a0\u00a0 te.name = 'ErrorLog'\n<\/pre>\n<p>Note that this script has neither\u00a0 <strong>EndTime<\/strong> nor <strong>Duration<\/strong> columns, for obvious reasons.<\/p>\n<p>Here is another script which will outline the <strong>sort<\/strong> and <strong>hash<\/strong> warnings:<\/p>\n<pre class=\"theme:ssms2012 lang:tsql\">SELECT\u00a0 TE.name AS [EventName] ,\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 v.subclass_name ,\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 T.DatabaseName ,\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 t.DatabaseID ,\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 t.NTDomainName ,\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 t.ApplicationName ,\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 t.LoginName ,\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 t.SPID ,\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 t.StartTime\nFROM\u00a0\u00a0\u00a0 sys.fn_trace_gettable(CONVERT(VARCHAR(150), ( SELECT TOP 1\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 f.[value]\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 FROM\u00a0\u00a0\u00a0 sys.fn_trace_getinfo(NULL) f\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 WHERE\u00a0\u00a0 f.property = 2\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 )), DEFAULT) T\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 JOIN sys.trace_events TE ON T.EventClass = TE.trace_event_id\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 JOIN sys.trace_subclass_values v ON v.trace_event_id = TE.trace_event_id\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 AND v.subclass_value = t.EventSubClass\nWHERE\u00a0\u00a0 te.name = 'Hash Warning'\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 OR te.name = 'Sort Warnings'\n... and finally, one more script which outlines the missing statistics and join predicates.\nSELECT\u00a0 TE.name AS [EventName] ,\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 T.DatabaseName ,\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 t.DatabaseID ,\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 t.NTDomainName ,\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 t.ApplicationName ,\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 t.LoginName ,\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 t.SPID ,\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 t.StartTime\nFROM\u00a0\u00a0\u00a0 sys.fn_trace_gettable(CONVERT(VARCHAR(150), ( SELECT TOP 1\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 f.[value]\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 FROM\u00a0\u00a0\u00a0 sys.fn_trace_getinfo(NULL) f\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 WHERE\u00a0\u00a0 f.property = 2\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 )), DEFAULT) T\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 JOIN sys.trace_events TE ON T.EventClass = TE.trace_event_id\nWHERE\u00a0\u00a0 te.name = 'Missing Column Statistics'\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 OR te.name = 'Missing Join Predicate'\n<\/pre>\n<h2>The Full Text Events<\/h2>\n<p>The Full-Text event category shows information about the Full-Text population events: If it is aborted, then you should look into the event log for a more detailed message; the <strong>FT Crawl Started<\/strong> sub-event indicates that the population request has been picked up by the workers. <strong>FT Crawl Stopped<\/strong> indicates either a successful completion or <strong>stop by<\/strong> error.<\/p>\n<p>Full-Text events are&#8230;<\/p>\n<ul>\n<li>FT Crawl Aborted<\/li>\n<li>FT Crawl Started<\/li>\n<li>FT Crawl Stopped<\/li>\n<\/ul>\n<p>Here is a script which will return the Full text events:<\/p>\n<pre class=\"theme:ssms2012 lang:tsql\">SELECT\u00a0 TE.name AS [EventName] ,\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 DB_NAME(t.DatabaseID) AS DatabaseName ,\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 t.DatabaseID ,\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 t.NTDomainName ,\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 t.ApplicationName ,\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 t.LoginName ,\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 t.SPID ,\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 t.StartTime ,\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 t.IsSystem\nFROM\u00a0\u00a0\u00a0 sys.fn_trace_gettable(CONVERT(VARCHAR(150), ( SELECT TOP 1\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 f.[value]\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 FROM\u00a0\u00a0\u00a0 sys.fn_trace_getinfo(NULL) f\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 WHERE\u00a0\u00a0 f.property = 2\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 )), DEFAULT) T\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 JOIN sys.trace_events TE ON T.EventClass = TE.trace_event_id\nWHERE\u00a0\u00a0 te.name = 'FT:Crawl Started'\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 OR te.name = 'FT:Crawl Aborted'\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 OR te.name = 'FT:Crawl Stopped'\n<\/pre>\n<p>Notice that the records in the <strong>DatabaseName<\/strong> column are null, so we have to get the database name from the DB_NAME() function.<\/p>\n<h2>Object events<\/h2>\n<p>Here is where the real detective work starts: the changes of the object. In this category we have altered, created and deleted objects, and this includes anything from index rebuilds, statistics updates, to database deletion.<\/p>\n<p>Object events include:<\/p>\n<ul>\n<li>Object Altered<\/li>\n<li>Object Created<\/li>\n<li>Object Deleted<\/li>\n<\/ul>\n<p>Here is a script which will give you the most recently manipulated objects in your databases.<\/p>\n<pre class=\"theme:ssms2012 lang:tsql\">SELECT\u00a0 TE.name ,\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 v.subclass_name ,\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 DB_NAME(t.DatabaseId) AS DBName ,\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 T.NTDomainName ,\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 t.NTUserName ,\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 t.HostName ,\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 t.ApplicationName ,\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 t.LoginName ,\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 t.Duration ,\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 t.StartTime ,\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 t.ObjectName ,\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 CASE t.ObjectType\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 WHEN 8259 THEN 'Check Constraint'\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 WHEN 8260 THEN 'Default (constraint or standalone)'\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 WHEN 8262 THEN 'Foreign-key Constraint'\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 WHEN 8272 THEN 'Stored Procedure'\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 WHEN 8274 THEN 'Rule'\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 WHEN 8275 THEN 'System Table'\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 WHEN 8276 THEN 'Trigger on Server'\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 WHEN 8277 THEN '(User-defined) Table'\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 WHEN 8278 THEN 'View'\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 WHEN 8280 THEN 'Extended Stored Procedure'\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 WHEN 16724 THEN 'CLR Trigger'\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 WHEN 16964 THEN 'Database'\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 WHEN 16975 THEN 'Object'\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 WHEN 17222 THEN 'FullText Catalog'\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 WHEN 17232 THEN 'CLR Stored Procedure'\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 WHEN 17235 THEN 'Schema'\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 WHEN 17475 THEN 'Credential'\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 WHEN 17491 THEN 'DDL Event'\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 WHEN 17741 THEN 'Management Event'\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 WHEN 17747 THEN 'Security Event'\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 WHEN 17749 THEN 'User Event'\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 WHEN 17985 THEN 'CLR Aggregate Function'\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 WHEN 17993 THEN 'Inline Table-valued SQL Function'\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 WHEN 18000 THEN 'Partition Function'\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 WHEN 18002 THEN 'Replication Filter Procedure'\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 WHEN 18004 THEN 'Table-valued SQL Function'\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 WHEN 18259 THEN 'Server Role'\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 WHEN 18263 THEN 'Microsoft Windows Group'\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 WHEN 19265 THEN 'Asymmetric Key'\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 WHEN 19277 THEN 'Master Key'\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 WHEN 19280 THEN 'Primary Key'\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 WHEN 19283 THEN 'ObfusKey'\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 WHEN 19521 THEN 'Asymmetric Key Login'\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 WHEN 19523 THEN 'Certificate Login'\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 WHEN 19538 THEN 'Role'\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 WHEN 19539 THEN 'SQL Login'\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 WHEN 19543 THEN 'Windows Login'\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 WHEN 20034 THEN 'Remote Service Binding'\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 WHEN 20036 THEN 'Event Notification on Database'\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 WHEN 20037 THEN 'Event Notification'\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 WHEN 20038 THEN 'Scalar SQL Function'\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 WHEN 20047 THEN 'Event Notification on Object'\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 WHEN 20051 THEN 'Synonym'\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 WHEN 20549 THEN 'End Point'\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 WHEN 20801 THEN 'Adhoc Queries which may be cached'\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 WHEN 20816 THEN 'Prepared Queries which may be cached'\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 WHEN 20819 THEN 'Service Broker Service Queue'\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 WHEN 20821 THEN 'Unique Constraint'\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 WHEN 21057 THEN 'Application Role'\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 WHEN 21059 THEN 'Certificate'\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 WHEN 21075 THEN 'Server'\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 WHEN 21076 THEN 'Transact-SQL Trigger'\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 WHEN 21313 THEN 'Assembly'\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 WHEN 21318 THEN 'CLR Scalar Function'\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 WHEN 21321 THEN 'Inline scalar SQL Function'\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 WHEN 21328 THEN 'Partition Scheme'\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 WHEN 21333 THEN 'User'\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 WHEN 21571 THEN 'Service Broker Service Contract'\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 WHEN 21572 THEN 'Trigger on Database'\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 WHEN 21574 THEN 'CLR Table-valued Function'\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 WHEN 21577\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 THEN 'Internal Table (For example, XML Node Table, Queue Table.)'\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 WHEN 21581 THEN 'Service Broker Message Type'\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 WHEN 21586 THEN 'Service Broker Route'\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 WHEN 21587 THEN 'Statistics'\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 WHEN 21825 THEN 'User'\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 WHEN 21827 THEN 'User'\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 WHEN 21831 THEN 'User'\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 WHEN 21843 THEN 'User'\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 WHEN 21847 THEN 'User'\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 WHEN 22099 THEN 'Service Broker Service'\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 WHEN 22601 THEN 'Index'\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 WHEN 22604 THEN 'Certificate Login'\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 WHEN 22611 THEN 'XMLSchema'\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 WHEN 22868 THEN 'Type'\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ELSE 'Hmmm???'\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 END AS ObjectType\nFROM\u00a0\u00a0\u00a0 [fn_trace_gettable](CONVERT(VARCHAR(150), ( SELECT TOP 1\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 value\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 FROM\u00a0\u00a0\u00a0 [fn_trace_getinfo](NULL)\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0WHERE\u00a0\u00a0 [property] = 2\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 )), DEFAULT) T\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 JOIN sys.trace_events TE ON T.EventClass = TE.trace_event_id\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 JOIN sys.trace_subclass_values v ON v.trace_event_id = TE.trace_event_id\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 AND v.subclass_value = t.EventSubClass\nWHERE\u00a0\u00a0 TE.name IN ( 'Object:Created', 'Object:Deleted', 'Object:Altered' )\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 -- filter statistics created by SQL server\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 AND t.ObjectType NOT IN ( 21587 )\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 -- filter tempdb objects\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 AND DatabaseID &lt;&gt; 2\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 -- get only events in the past 24 hours\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 AND StartTime &gt; DATEADD(HH, -24, GETDATE())\nORDERBY t.StartTime DESC ;\n<\/pre>\n<p>Keep in mind that SQL Server by default has five trace files, 20 MB each and there is no known supported method of changing this. If you have a busy system, the trace files may roll over far too fast (even within hours) and you may not be able to catch some of the changes. This article will not discuss in detail any workarounds for efficient SQL Server tracing, instead I will do this in a separate article later on.<\/p>\n<h2>Security Audit Events<\/h2>\n<p>Another part of the default trace is the Security Audit. As you can see from the event list below, this is one of the richest parts of the default trace. In general, what this event group tells us is what significant security events are occurring in our system.<\/p>\n<p>Security events include&#8230;<\/p>\n<ul>\n<li>Audit Add DB user event<\/li>\n<li>Audit Add login to server role event<\/li>\n<li>Audit Add Member to DB role event<\/li>\n<li>Audit Add Role event<\/li>\n<li>Audit Add login event<\/li>\n<li>Audit Backup\/Restore event<\/li>\n<li>Audit Change Database owner<\/li>\n<li>Audit DBCC event<\/li>\n<li>Audit Database Scope GDR event (Grant, Deny, Revoke)<\/li>\n<li>Audit Login Change Property event<\/li>\n<li>Audit Login Failed<\/li>\n<li>Audit Login GDR event<\/li>\n<li>Audit Schema Object GDR event<\/li>\n<li>Audit Schema Object Take Ownership<\/li>\n<li>Audit Server Starts and Stops<\/li>\n<\/ul>\n<p>Let&#8217;s take it one step at a time and:<\/p>\n<ul>\n<li>create a SQL Server login<\/li>\n<li>assign read permissions to this user in one of our databases.<\/li>\n<\/ul>\n<p>By running the following query we will be able to track what users have been created on our SQL Server instance:<\/p>\n<pre class=\"theme:ssms2012 lang:tsql\">SELECT\u00a0 TE.name AS [EventName] ,\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 v.subclass_name ,\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 T.DatabaseName ,\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 t.DatabaseID ,\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 t.NTDomainName ,\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 t.ApplicationName ,\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 t.LoginName ,\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 t.SPID ,\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 t.StartTime ,\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 t.RoleName ,\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 t.TargetUserName ,\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 t.TargetLoginName ,\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 t.SessionLoginName\nFROM\u00a0\u00a0\u00a0 sys.fn_trace_gettable(CONVERT(VARCHAR(150), ( SELECT TOP 1\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 f.[value]\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 FROM\u00a0 \u00a0\u00a0sys.fn_trace_getinfo(NULL) f\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 WHERE\u00a0\u00a0 f.property = 2\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 )), DEFAULT) T\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 JOIN sys.trace_events TE ON T.EventClass = TE.trace_event_id\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 JOIN sys.trace_subclass_values v ON v.trace_event_id = TE.trace_event_id\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 AND v.subclass_value = t.EventSubClass\nWHERE\u00a0\u00a0 te.name IN ( 'Audit Addlogin Event', 'Audit Add DB User Event',\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 'Audit Add Member to DB Role Event' )\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 AND v.subclass_name IN ( 'add', 'Grant database access' )\n<\/pre>\n<p>Here is how the result of the query looks like after we have created one login with giving it read permission to one database:<\/p>\n<p class=\"illustration\"><img decoding=\"async\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/imported\/1254-Fig2.jpg\" alt=\"1254-Fig2.jpg\" \/><\/p>\n<p>As we can see, the first row is announcing the creation of the login in the master database, together with the creator (<strong>SessionLoginName<\/strong> column) and the create user (<strong>TargetLoginName<\/strong> column).<\/p>\n<p>The next two rows are as follows: creating the database user and granting it database access, and last &#8211; adding the database user to a DB role.<\/p>\n<p>Keep in mind that if you add the user to more than one role and if you give the login access to more than one database, then you will see several rows noting every event in your default trace.<\/p>\n<p>Now let&#8217;s audit the dropped users and logins by running the following query:<\/p>\n<pre class=\"theme:ssms2012 lang:tsql\">SELECT\u00a0 TE.name AS [EventName] ,\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 v.subclass_name ,\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 T.DatabaseName ,\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 t.DatabaseID ,\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 t.NTDomainName ,\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 t.ApplicationName ,\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 t.LoginName ,\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 t.SPID ,\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 t.StartTime ,\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 t.RoleName ,\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 t.TargetUserName ,\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 t.TargetLoginName ,\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 t.SessionLoginName\nFROM\u00a0\u00a0\u00a0 sys.fn_trace_gettable(CONVERT(VARCHAR(150), ( SELECT TOP 1\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 f.[value]\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 FROM\u00a0\u00a0\u00a0 sys.fn_trace_getinfo(NULL) f\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 WHERE\u00a0\u00a0 f.property = 2\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 )), DEFAULT) T\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 JOIN sys.trace_events TE ON T.EventClass = TE.trace_event_id\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 JOIN sys.trace_subclass_values v ON v.trace_event_id = TE.trace_event_id\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 AND v.subclass_value = t.EventSubClass\nWHERE\u00a0\u00a0 te.name IN ( 'Audit Addlogin Event', 'Audit Add DB User Event',\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 'Audit Add Member to DB Role Event' )\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 AND v.subclass_name IN ( 'Drop', 'Revoke database access' )\n<\/pre>\n<p>As you can see, the event name is the same for both creating and dropping logins: i.e. Audit Addlogin Event, however the subclass column value is what defines the difference, ie. In the case of creation of a login the subclass would be &#8216;Add&#8217; and in the case of deletion it would be &#8216;Drop&#8217;.<\/p>\n<p>In fact, if we drop the database user and the SQL login we created earlier, this query will return two rows &#8211; one for each event together with the dropped user and login names and the login name of the user who deleted the user and the login.<\/p>\n<p>The following query will give us all the failed logins contained in our default trace file:<\/p>\n<pre class=\"theme:ssms2012 lang:tsql\">SELECT\u00a0 TE.name AS [EventName] ,\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 v.subclass_name ,\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 T.DatabaseName ,\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 t.DatabaseID ,\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 t.NTDomainName ,\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 t.ApplicationName ,\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 t.LoginName ,\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 t.SPID ,\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 t.StartTime ,\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 t.SessionLoginName\nFROM\u00a0\u00a0\u00a0 sys.fn_trace_gettable(CONVERT(VARCHAR(150), ( SELECT TOP 1\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 f.[value]\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 FROM\u00a0\u00a0\u00a0 sys.fn_trace_getinfo(NULL) f\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 WHERE\u00a0\u00a0 f.property = 2\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 )), DEFAULT) T\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 JOIN sys.trace_events TE ON T.EventClass = TE.trace_event_id\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 JOIN sys.trace_subclass_values v ON v.trace_event_id = TE.trace_event_id\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 AND v.subclass_value = t.EventSubClass\nWHERE\u00a0\u00a0 te.name IN ( 'Audit Login Failed' )\n<\/pre>\n<p>There are quite a few events in the Security Audit class and for the sake of compactness of this article I will turn your attention only to one more event, namely to the &#8216;Audit Server Starts and Stops&#8217;.<\/p>\n<p>The following query will give you only the server start event:<\/p>\n<pre class=\"theme:ssms2012 lang:tsql\">SELECT\u00a0 TE.name AS [EventName] ,\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 v.subclass_name ,\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 T.DatabaseName ,\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 t.DatabaseID ,\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 t.NTDomainName ,\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 t.ApplicationName ,\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 t.LoginName ,\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 t.SPID ,\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 t.StartTime ,\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 t.SessionLoginName\nFROM\u00a0\u00a0\u00a0 sys.fn_trace_gettable(CONVERT(VARCHAR(150), ( SELECT TOP 1\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 f.[value]\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 FROM\u00a0\u00a0\u00a0 sys.fn_trace_getinfo(NULL) f\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 WHERE\u00a0\u00a0 f.property = 2\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 )), DEFAULT) T\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 JOIN sys.trace_events TE ON T.EventClass = TE.trace_event_id\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 JOIN sys.trace_subclass_values v ON v.trace_event_id = TE.trace_event_id\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 AND v.subclass_value = t.EventSubClass\nWHERE\u00a0\u00a0 te.name IN ( 'Audit Server Starts and Stops' )\n<\/pre>\n<p>Yes, you read it correctly: the above query will return <u>only<\/u> the <strong>Server Start<\/strong> event, and never the <strong>Server Stop<\/strong> event. Here is the explanation: as I mentioned earlier, SQL Server&#8217;s default trace consists of five trace files in total, which are 20 MB each. These five trace files are rotated (&#8216;refurbrished&#8217; or &#8216;recycled&#8217;, if you like) upon several conditions: when the instance starts or when the file size reaches 20 MB. Now, let&#8217;s think about this for a second: the queries I have listed so far in this article are returning the results only from the current trace file, i.e. the most recent one. Further, since the default trace file is rolled over every time the instance starts, this means that the event indicating the Server Stop will remain in the previous default trace file. Put simply, after the SQL Service restarts, our current default trace file will have the <strong>Server Start<\/strong> event as a first row. If you really wish to know when your SQL Server instance was stopped, you will need to include at least the contents of the previous file, but in fact we can include the contents of the other four default trace files to our result set. We can do this by changing the way we call sys.fn_trace_gettable so that it appends all default trace files<strong>.<\/strong> This function accepts 2 parameters &#8211; file location and name and number of files; if we pass as the first parameter the file location and the name of the oldest default trace file, then the sysfn_trace_gettable will append the newest ones, as long as we specify the appropriate value for the second parameter (the number of files). If we specify the newest file as a parameter to the function (as it is the case in all scripts in this article) then the older files will not be appended. As the filename contains the index of the file and they increment as each new file is created, it is easy to calculate the name of the oldest file.<\/p>\n<p>To find the exact file location of the default trace files, you just need to execute the following query:<\/p>\n<pre class=\"theme:ssms2012 lang:tsql\">SELECT\u00a0 REVERSE(SUBSTRING(REVERSE(path), CHARINDEX('\\', REVERSE(path)), 256)) AS DefaultTraceLocation\nFROM\u00a0\u00a0\u00a0 sys.traces\nWHERE\u00a0\u00a0 is_default = 1\n<\/pre>\n<h2>Server Memory Change Events<\/h2>\n<p>And now, let&#8217;s move on to the last event class in our default trace: the Server class. It contains only one event &#8211; Server Memory Change.<\/p>\n<p>The following query will tell us when the memory use has changed:<\/p>\n<pre class=\"theme:ssms2012 lang:tsql\">SELECT\u00a0 TE.name AS [EventName] ,\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 v.subclass_name ,\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 t.IsSystem\nFROM\u00a0\u00a0\u00a0 sys.fn_trace_gettable(CONVERT(VARCHAR(150), ( SELECT TOP 1\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 f.[value]\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 FROM\u00a0\u00a0\u00a0 sys.fn_trace_getinfo(NULL) f\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 WHERE\u00a0\u00a0 f.property = 2\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 )), DEFAULT) T\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 JOIN sys.trace_events TE ON T.EventClass = TE.trace_event_id\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 JOIN sys.trace_subclass_values v ON v.trace_event_id = TE.trace_event_id\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \u00a0\u00a0\u00a0\u00a0\u00a0AND v.subclass_value = t.EventSubClass\nWHERE\u00a0\u00a0 te.name IN ( 'Server Memory Change' )\n<\/pre>\n<p>The event subclass indicates if the memory has increased or decreased.<\/p>\n<h2>Conclusion<\/h2>\n<p>The default trace is a very powerful way to examine the health and the security of your SQL Server instance. There are several pitfalls to keep in mind &#8211; mainly related to file rollovers and size limitations, but with some programming the workarounds are not impossible. It is important to remember that the queries presented in this article will return the result from the single most recent default trace file. Depending on how busy the SQL Server instance is, the files may roll over way too fast for a DBA to catch all significant events; therefore, some automation is needed.<br \/><strong>Read also:\u00a0<\/strong><a href=\"https:\/\/www.red-gate.com\/simple-talk\/databases\/sql-server\/performance-sql-server\/sql-compilations-sec-is-not-what-you-think-it-is\/\"><span data-sheets-root=\"1\">SQL compilations\/sec is not what you think it is<\/span><\/a><\/p>\n<\/div>\n\n\n<section id=\"my-first-block-block_51ef1904f03bd6e3bd9d7732591c9b31\" class=\"my-first-block alignwide\">\n    <div class=\"bg-brand-600 text-base-white py-5xl px-4xl rounded-sm bg-gradient-to-r from-brand-600 to-brand-500 red\">\n        <div class=\"gap-4xl items-start md:items-center flex flex-col md:flex-row justify-between\">\n            <div class=\"flex-1 col-span-10 lg:col-span-7\">\n                <h3 class=\"mt-0 font-display mb-2 text-display-sm\">Fast, reliable and consistent SQL Server development&#8230;<\/h3>\n                <div class=\"child:last-of-type:mb-0\">\n                                            &#8230;with SQL Toolbelt Essentials. 10 ingeniously simple tools for accelerating development, reducing risk, and standardizing workflows.                                    <\/div>\n            <\/div>\n                            <a href=\"https:\/\/www.red-gate.com\/products\/sql-toolbelt-essentials\/\" class=\"btn btn--secondary btn--lg\">Learn more &amp; try for free<\/a>\n                    <\/div>\n    <\/div>\n<\/section>\n\n\n<section id=\"faq\" class=\"faq-block my-5xl\">\n    <h2>FAQs: The Default Trace in SQL Server<\/h2>\n\n                        <h3 class=\"mt-4xl\">1. How do you read the default trace in SQL Server?<\/h3>\n            <div class=\"faq-answer\">\n                <p>Query the default trace using the fn_trace_gettable function with the trace file path. First, find the current trace file by querying SELECT path FROM sys.traces WHERE is_default = 1. Then pass that path to SELECT * FROM fn_trace_gettable(@path, default) and join with sys.trace_events to translate event IDs into readable names. You can filter by EventClass, DatabaseName, LoginName, and StartTime to find specific events.<\/p>\n            <\/div>\n                    <h3 class=\"mt-4xl\">2. What events does the SQL Server default trace capture?<\/h3>\n            <div class=\"faq-answer\">\n                <p>The default trace captures events in six categories: Database events (data\/log file auto-growth and auto-shrink, mirroring status changes), Errors and Warnings (hash and sort warnings, missing column statistics, missing join predicates), Full-Text operations, Object events (CREATE, ALTER, DROP for any schema object), Security Audit events (login changes, permission grants\/revokes, audit schema changes), and Server events (memory and server lifecycle changes).<\/p>\n            <\/div>\n                    <h3 class=\"mt-4xl\">3. Should I use the default trace or Extended Events in SQL Server?<\/h3>\n            <div class=\"faq-answer\">\n                <p>Use the default trace for quick investigations when you need to check \u201cwhat changed\u201d without any setup &#8211; it\u2019s already running. Use Extended Events for ongoing, targeted monitoring of specific events with filtering, aggregation, and lower overhead. The default trace is being deprecated in favor of Extended Events, so new monitoring workflows should use Extended Events. But for ad-hoc historical investigation, the default trace is immediately available and often sufficient.<\/p>\n            <\/div>\n            <\/section>\n","protected":false},"excerpt":{"rendered":"<p>Complete guide to the SQL Server default trace. Covers database events, security auditing, object changes, and error tracking with ready-to-use T-SQL scripts for each event category.&hellip;<\/p>\n","protected":false},"author":221902,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[143529],"tags":[5966,4206,4619,4150,4151],"coauthors":[11305],"class_list":["post-1103","post","type-post","status-publish","format-standard","hentry","category-performance-sql-server","tag-monitor","tag-performance","tag-security","tag-sql","tag-sql-server"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/1103","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/users\/221902"}],"replies":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/comments?post=1103"}],"version-history":[{"count":9,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/1103\/revisions"}],"predecessor-version":[{"id":109350,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/1103\/revisions\/109350"}],"wp:attachment":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media?parent=1103"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/categories?post=1103"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/tags?post=1103"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/coauthors?post=1103"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}