'More than 10 000 entries in sql server audit log

I have an audit which tracks all queries for a certain database. Because there are a lot of queries which are run by sql server itself the log is quite big. When I view the audit log (right click on name of audit - View Audit Logs) I only ever see 10,000 records.

view audit log

The audit is configured like this:

audit configuration

Is there a way to view more than 10,000 records at once? I'd like to export all records to a file.

sql-serversql-server-2012


Solution 1:[1]

According to the documentation:

The number of rows that are displayed for audit logs can be configured on the SQL Server Object Explorer/Commands page of the Tools/Options dialog box.

Setting it to 0 returns all rows.

enter image description here

Solution 2:[2]

You can use the fn_xe_file_target_read_file TVF to query audit and other extended event file target data. Just specify the audit file path, including wildcards:

SELECT *
FROM (N'K:\SQL Server 2012\Data_DB*.sqlaudit',DEFAULT,DEFAULT,DEFAULT);

Solution 3:[3]

Perhaps sys.fn_get_audit_file (Transact-SQL) - SQL Server | Microsoft Docs?

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 jpw
Solution 2 Dan Guzman
Solution 3 Bert Van Landeghem

Related Questions