What event information can I get by default from SQL Server?

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 the dbo.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?

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 - which I am definitely a big fan of doing. As long as it is enabled, this can be a rich source of valuable information, though in modern versions of SQL Server, much of it is noise and/or is already captured elsewhere. The following lists the trace events that are captured:

DECLARE @TraceID INT;

SELECT @TraceID = id FROM sys.traces WHERE is_default = 1;

SELECT t.EventID, e.name as Event_Description
  FROM sys.fn_trace_geteventinfo(@TraceID) t
  JOIN sys.trace_events e ON t.eventID = e.trace_event_id
  GROUP BY t.EventID, e.name;

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):
EventID Event_Description
------- ----------------------------------------------
18 Audit Server Starts And Stops
20 Audit Login Failed
22 ErrorLog
46 Object:Created
47 Object:Deleted
55 Hash Warning
69 Sort Warnings
79 Missing Column Statistics
80 Missing Join Predicate
81 Server Memory Change
92 Data File Auto Grow
93 Log File Auto Grow
94 Data File Auto Shrink
95 Log File Auto Shrink
102 Audit Database Scope GDR Event
103 Audit Schema Object GDR Event
104 Audit Addlogin Event
105 Audit Login GDR Event
106 Audit Login Change Property Event
108 Audit Add Login to Server Role Event
109 Audit Add DB User Event
110 Audit Add Member to DB Role Event
111 Audit Add Role Event
115 Audit Backup/Restore Event
116 Audit DBCC Event
117 Audit Change Audit Event
152 Audit Change Database Owner
153 Audit Schema Object Take Ownership Event
155 FT:Crawl Started
156 FT:Crawl Stopped
164 Object:Altered
167 Database Mirroring State Change
175 Audit Server Alter Trace Event
218 Plan Guide Unsuccessful


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.

Question: When was the last time an auto-grow happened in the AdventureWorks database, and how long did it take?

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:

DECLARE @path NVARCHAR(260);

SELECT 
   @path = REVERSE(SUBSTRING(REVERSE([path]), 
   CHARINDEX(CHAR(92), REVERSE([path])), 260)) + N'log.trc'
FROM    sys.traces
WHERE   is_default = 1;

SELECT 
   DatabaseName,
   [FileName],
   SPID,
   Duration,
   StartTime,
   EndTime,
   FileType = CASE EventClass WHEN 92 THEN 'Data' ELSE 'Log' END
FROM sys.fn_trace_gettable(@path, DEFAULT)
WHERE EventClass IN (92,93)
AND DatabaseName = N'AdventureWorks'
ORDER BY StartTime DESC;

Question: Who deleted the dbo.EmployeeAuditData table and when?

This will return any DROP events for an object named EmployeeAuditData. If you want to make sure that it only detects DROP 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'.

```
DECLARE @path NVARCHAR(260);

SELECT
@path = REVERSE(SUBSTRING(

StackExchange Database Administrators Q#48052, answer score: 68