Using SQL Server Standard Trace to Audit Events (fn_trace_gettable)

Views: 1.460
This post is the 7 part of 15 in the series. Security and Audit
Reading Time: 5 minutes

Hello people,
Good afternoon!

In this post, I will demonstrate how to get important information about various events that occur in the SQL Server instance without having to enable any controls or auditing, just by reading the information already collected by the standard SQL Server trace.

Although I have already talked about this in some other posts, I decided to close this subject and make a post with several more examples of use.

The standard SQL trace collects 34 different types of events, which can be manually generated by tools such as SQL Server Profiler or with Transact-SQL queries.

The examples below show how to get information from different types of events, such as Shrink, AutoGrow, Automatic Statistics Update, Backup / Restore, DBCC, No Column Statistics, among others.

An important point to note is that this default SQL Server trace runs in the background by default, collecting only a few events and therefore has minimal impact on the SQL Server instance.

By enabling a trace using SQL Server Profiler, which displays real-time results through the interface, you can end up with impacts such as performance degradation in the environment, especially if you don't filter out just the events you want to observe and what criteria will be set for you. identify the sessions that will be part of Trace.

Map default SQL Server

Normally, we have running on our SQL Server instance only the default trace, which is enabled by default, although in some environments you may find more than one trace running if any DBA creates a custom trace.

Listing active traces on instance

To list the active traces in the instance, you can use the query below:

SQL Server - Traces

Identifying the default trace

Although the default trace is usually the trace with ID = 1, this is not always the case. Therefore, I recommend using the query below to identify the default trace:

Listing the default trace events

As noted above, the default SQL Server trace collects different 34 events. To identify the full list of events, simply execute this query:

SQL Server - fn_trace_gettable EventClass

Enabling Default Trace (Already Enabled After Installation)

To enable SQL Server's default trace is quite simple, just run sp_configure:

Disabling Default Trace

To disable SQL Server default tracing is as simple as enabling. Just run sp_configure:

Identifying the events

A simple way to identify events that occurred in the instance is by running the query below:

SQL Server - Trace Events Ocurred

Identifying Autogrowth Events

One of the most important events that we can observe with the default trace is Autogrowth events, which occur when this option is enabled in the database and it reaches full size, needing to allocate more disk space.

SQL Server - Traces Autogrowth

Identifying Disk Shrink Events

Another really cool event to monitor is the Disk Shrink event, which occurs when the database is allocating more space than it is using and the DBA reallocates the allocated space, resulting in free filesystem space.

SQL Server - Traces Shrink

Identifying When DBCC Commands were Executed on Instance

Very useful audit to identify who executed or when a DBCC command was executed on instance

SQL Server - Trace DBCC

Identifying when backups were performed

Another very interesting audit for DBA, which makes it possible to identify the date and who ran the backups on the instance (which can also be found in the msdb.dbo.backupset table).

SQL Server - Trace Backup

Identifying When Backups Were Restored

Interesting audit that allows you to identify the date and which user performed a restore on the instance (which can also be queried in the msdb.dbo.restorehistory, msdb.dbo.restorefile and msdb.dbo.restorefilegroup tables)

SQL Server - Traces Restore

These are just a few examples of what the default trace returns to us. There is the extensive list of events for you to explore!
Good luck, hug and until the next post!