Over a million developers have joined DZone.
{{announcement.body}}
{{announcement.title}}

SQL Audit Not Showing Full SQL Statement

DZone's Guide to

SQL Audit Not Showing Full SQL Statement

If you've come up against the issue of SQL Audit truncating of SQL statements, then this post might have the answer you need.

· Database Zone ·
Free Resource

Running out of memory? Learn how Redis Enterprise enables large dataset analysis with the highest throughput and lowest latency while reducing costs over 75%! 

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, which 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.

Running out of memory? Never run out of memory with Redis Enterprise databaseStart your free trial today.

Topics:
database ,sql server ,auditing ,ms sql

Published at DZone with permission of

Opinions expressed by DZone contributors are their own.

{{ parent.title || parent.header.title}}

{{ parent.tldr }}

{{ parent.urlSource.name }}