This month’s T-SQL Tuesday (#71) is hosted by Sebastian Meine (b/t). He has picked an interesting security subject. SQL Server Audits. Fortunately this is one I have a bit of experience with. It’s one of my favorite new features if only because it’s so simple to use. There is however one big gotcha.

A while back I was asked to set up an audit for the first time. I set it up with 10 rotating files each 500mb in size. 5GB didn’t seem like an unreasonable amount of space given this was my first time and I wasn’t sure what type of volume to expect.

Now, here is an important quote from the BOL on CREATE SERVER AUDIT:

This parameter is evaluated whenever the audit restarts (which can happen when the instance of the Database Engine restarts or when the audit is turned off and then on again) or when a new file is needed because the MAXSIZE has been reached. When MAX_ROLLOVER_FILES is evaluated, if the number of files exceeds the MAX_ROLLOVER_FILES setting, the oldest file is deleted. ….. Only one file is automatically deleted when MAX_ROLLOVER_FILES setting is evaluated,

Only one file is automatically deleted Remember that part for later.

I set up a view against sys.fn_get_audit_file so our reporting software could pull the audit data to process and report on. Then a little while later I went back to double checked my files and see how much space I was actually using and adjust if needed.

I was shocked to notice there were ~15 500mb files in the directory! Well, boy was I glad I’d checked. That kind of growth could quickly fill up my drive. It took me a little while to track down the problem but here were some of the things I found:

• The users had set up the reporting software to pull data from the view every 5 minutes. Because of the volume of data it was taking a large percentage of that 5 minutes to get the data back.
• While the query was running the audit files were locked.
• Only one file is automatically deleted at a time. (You remembered that right?)

So what happens if the file is in use when the system trys to delete it? Nothing. The delete fails silently. We now have 11 files. The next time a delete is attempted it may succeed. It may not. If it does, then great. If not then we now have 12 files. And on and on.

Well the first thing that we did was cut the automated pull down to once an hour and put a where clause on the query so it wouldn’t take so long (and hopefully wouldn’t hit the whole set of files). Next I put a separate process in place to keep an eye on the drive space and notify me if it was running low.

The moral of the story is if you are going to be pulling your audit data on a regular (short period) basis then you had best also keep an eye on the audit files.

Note: This was on SQL 2008 R2 and I have not tested it against SQL 2014 but I’d be very surprised if it has changed.

Filed under: Auditing, Microsoft SQL Server, Problem Resolution, SQLServerPedia Syndication, T-SQL Tuesday Tagged: Auditing, microsoft sql server, T-SQL Tuesday