SQL Audit Not Showing Full SQL Statement

I noticed some MSDN forum posts regarding SQL Audit not showing the full SQL statement. To the end user, it appears that SQL Audit is truncating of SQL statements. I decided to write a quick post to help clear up the confusion for large SQL statements and how they appear in SQL Audit.

No, it’s not a bug, but yes, the statements may appear trimmed. Let me explain.

First, let’s set up an audit that will capture a large statement. How large is large?

The fn_get_audit_file documentation defines large as: “too large to fit in the write buffer“. This is referring to the size of the ‘statement’ column, defined as NVARCHAR(4000). For any statement larger than that it will need to be broken into distinct lines, identified by the sequence_number column.

Let’s see what this looks like in action.

I will create the Server Audit first that outputs to a flat file on my laptop:

 

 

I have also included the code for you here:

CREATE SERVER AUDIT [LargeTextAudit]
TO FILE 
(       FILEPATH = N'C:\TeamData\AuditLogs\'
        ,MAXSIZE = 0 MB
        ,MAX_ROLLOVER_FILES = 2147483647
        ,RESERVE_DISK_SPACE = OFF
)
WITH
(       QUEUE_DELAY = 1000
        ,ON_FAILURE = CONTINUE
)
ALTER SERVER AUDIT [LargeTextAudit] WITH (STATE = ON)
GO

OK, next we create a Database Audit Specification. I will capture any SELECT statement executed against the sysobjects table:

 

 

And here is the code for that database specification:

CREATE DATABASE AUDIT SPECIFICATION [LargeTextAudit]
FOR SERVER AUDIT [LargeTextAudit]
ADD (SELECT ON OBJECT::[sys].[sysobjects] BY [dbo])
WITH (STATE = ON)
GO

Now we need to write a statement that will be larger than 8k. I will use dynamic SQL for this task. Here’s the sample code that I’ve used for…a long time. Now get off my lawn:

DECLARE @LongString VARCHAR(8000)
        , @Replicate VARCHAR(8000)
        , @From VARCHAR(8000)

SELECT @LongString='SELECT TOP 1 name,'''
        ,@Replicate=replicate('SQLRockstar',9000)
        ,@From=''' FROM sysobjects'

exec(@LongString+@Replicate+@From)

After enabling the database specification and the server audit, execute the code. Then open the audit log viewer:

 

 

We can see there are three rows for this one statement. You can also see the sequence number column to the right in the output window. And it is also in the text box below.

If you are using the fn_get_audit_file function, the sequence number is there, too.

I hope this clears up the confusion for SQL Audit and showing large SQL statements. If you happen to be in Antwerp for Techorama later this month, I have a session on SQL Audit you might be interested in attending. We’ll talk about SQL Audit for both Earthed and Cloud versions of SQL Server.