I often see questions where people want to know if a certain thing happened, or when it happened, or who performed the action. In a lot of cases, SQL Server just doesn't track this information on its own. For example:
- Who last executed stored procedure
dbo.MyProcedure
? - Who updated the
salary
column in thedbo.Employees
table? - Who last queried the
dbo.Orders
table from Management Studio?
But there are several other events that SQL Server does track temporarily by default, and can natively answer questions about, such as:
- When was the last time an auto-grow happened in the AdventureWorks database, and how long did it take?
- Who deleted the
dbo.EmployeeAuditData
table and when? - How many memory-related errors have happened today?
How do I get this information, and how long does it stay available?
Best Answer
There is quite a bit of valuable information that SQL Server tracks for you by default. Since SQL Server 2005 there has been a "default trace" that runs in the background, and since SQL Server 2008 there has been an Extended Events session automatically running, called
system_health
.You can also find certain information from the SQL Server error log, the SQL Server Agent log, Windows event logs, and additional logging from things like SQL Server Audit, Management Data Warehouse, Event Notifications, DML Triggers, DDL Triggers, SCOM / System Center, your own server-side traces or Extended Events sessions, or third-party monitoring solutions (like those made by SentryOne). You can also optionally enable a so-called "Blackbox trace" to assist in troubleshooting.
But for this post I'm going to focus the scope on things that are generally enabled most everywhere: the default trace, Extended Events sessions, and the error log.
Default Trace
The default trace is usually running on most systems, unless you have disabled it using
sp_configure
. As long as it is enabled, this can be a rich source of valuable information. The following lists the trace events that are captured:You can get into more detail by joining to
sys.trace_columns
to see which events come with which data, but I'm going to skip that for now, since you can see what you have when you actually query the trace data for specific events. These are the events that are available on my system (you should run the query on yours to be sure they match, though this is still the same set of events through SQL Server 2019 CTP 2.4):Note that the default trace uses rollover files and so the data available to you will only go back so far - the date range of available data depends on how many of the above events are being captured and at what frequency. If you want to ensure that you keep a longer history, you can set up a job that periodically archives away the currently inactive files associated with the trace.
Examples
In the question I asked a couple of questions that I have found. Here are example queries for pulling that specific information from the default trace.
This query will pull all of the AutoGrow events in the AdventureWorks database, for both log and data files, that are still in the default trace log files:
This will return any
DROP
events for an object namedEmployeeAuditData
. If you want to make sure that it only detectsDROP
events for tables, you can add a filter:ObjectType = 8277
(the full list is documented here). If you want to restrict the search space to a specific database, you can add a filter:DatabaseName = N'db_name'
.There is a complication here, and it is very edge case but thought it prudent to mention anyway. If you use multiple schemas and may have the same object name in multiple schemas, you won't be able to tell which one this is (unless its counterpart(s) still exist). There is an outside case that UserA might have dropped SchemaB.Tablename while UserB might have dropped SchemaA.Tablename. The default trace does not track the schema of the object (nor does it capture
TextData
for this event), and theObjectID
included in the trace is not useful for a direct match (because the object was dropped and no longer exists). Including that column in the output in this case might be useful to cross-reference against any copies of the table with the same name that still exist, but if the system is in this much disarray (or if all such copies have been deleted) there still might not be a reliable way to guess at which copy of the table was dropped by whom.Extended Events
The following was the list of data you could cull from the
system_health
session in SQL Server 2008 and 2008 R2 (that list is more complete in modern versions):From Use the system_health event session (MSDN), the list is somewhat expanded in SQL Server 2012 (and remains the same for SQL Server 2014):
In SQL Server 2016, two more events are captured:
KILL
command.(The documentation hasn't been updated yet, but I blogged about how I discover these and other changes.)
To get the more cryptic configuration applicable for your specific version, you can always run the following query directly, but you'll have to interpret the names and parse the predicates to match up to the more natural language lists above:
If you are using Availability Groups, there are also two new sessions that you will find running:
AlwaysOn_failover
andAlwaysOn_health
. You can see the data they collect with the following query:These event sessions use ring buffer targets to store the data, so - like the buffer pool and the plan cache - older events will get phased out, so you won't necessarily be able to pull events from the date range you want.
Example
In the question I posed this fictitious question:
Here is a sample (and probably not very efficient) query that can pull this information from the
system_health
session:(This example borrows loosely from Amit Banerjee's introductory blog post on the
system_health
session.)For more information on Extended Events (including many examples where you can query for specific data), see this 31-part blog series by Jonathan Kehayias:
https://www.sqlskills.com/blogs/jonathan/an-xevent-a-day-31-days-of-extended-events/
Error Log
SQL Server by default keeps the current plus 6 most recent error log files (but you can change this). Plenty of information is stored there, including startup information (how many cores are in use, whether lock pages in memory is set, authentication mode, etc) as well as errors and other scenarios severe enough to be documented (and not captured elsewhere). One recent example was someone looking for when a database was taken offline. You can determine this by scanning through each of the most recent 7 error logs for the text
Setting database option OFFLINE
:I covered some other details in this recent answer, and there is also some good background information at toadworld and also in the official documentation.
One group of "errors" the error log does track by default - and can make important information fall off the tail much quicker - is every successful backup message. You can prevent these from filling up the error log with noise by enabling trace flag 3226.