locked
Viewing SQL Audit Logs RRS feed

  • Question

  • I am new at the SQL auditing feature. I have played around with it, and I have one problem that I hope someone can help me with. When I first turn on the SQL audit and the audit file is small I can view the log on my local machine with the log viewer in SSMS. However, as the log files continue to be created and grow, the log file viewer no longer works. It sits there for several minutes saying "initializing log file #1", then just comes back 0 records processed. Sometimes it comes back with an OutOfMemoryException. I have the SQL audit set to go to files, and the files are set to be 250 MB each.

    Is there some better way to look at the SQL audit log files generated, or is there some way to make the log file viewer actually work once the log files begin to grow? I know I can use Transact-SQL statements, but I was hoping to use the log file viewer or some other GUI viewer. Another issue I'm concerned about is being able to archive the log files to another location and having the ability to look at them.

    Thank you.

    Wednesday, March 5, 2014 6:01 PM

All replies

  • Its looks like bug.

    http://connect.microsoft.com/SQLServer/feedback/details/709364/sql-server-audit-logs-do-not-display

    http://thomaslarock.com/2012/10/viewing-sql-server-2008-r2-audit-logs-using-ssms-2012/

    Use sys.fn_get_audit_file() system function to see the data that you already doing.

     http://msdn.microsoft.com/en-us/library/cc280765.aspx

    -Prashanth

    • Proposed as answer by Sofiya Li Thursday, March 6, 2014 10:08 AM
    Wednesday, March 5, 2014 6:28 PM
  • Thanks for your reply. I don't believe those links apply to my case because we are not using SQL 2012. My management tools and server are 2008 R2, which the second link says is supposed to work fine.
    Wednesday, March 5, 2014 6:40 PM
  • According to the old jungle sayings: If the GUI doesn't handle it (as in this case, apparently because of the size of the audit file), then skip the GUI. Use sys.fn_get_audit_file() as suggested...

    Tibor Karaszi, SQL Server MVP | web | blog

    • Proposed as answer by Sofiya Li Thursday, March 6, 2014 10:08 AM
    Thursday, March 6, 2014 7:50 AM
  • As I mentioned in my original post, I know I can always use the T-SQL statement. I tried that , and that is not the route I'd prefer to use. I was hoping someone else had seen this problem and had a workaround to be able to use the log viewer or some other 3rd party log viewer.
    Thursday, March 6, 2014 1:46 PM