Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I created a SQL Server audit and when I view the audit logs I only see today’s audit. I am saving it to a file and I want to store audit for one year. Is there a way to store database and server audit for one year? Does SQL Server store audits for only a day?

CREATE SERVER AUDIT [Audit-20130618-142022] TO FILE 
(
  FILEPATH = N'C:\Documents\Audit\Audit' ,
  MAXSIZE = 0 MB ,
  MAX_ROLLOVER_FILES = 2147483647 ,
  RESERVE_DISK_SPACE = OFF 
) 
WITH 
( 
  QUEUE_DELAY = 1000 ,
  ON_FAILURE = CONTINUE 
);
share|improve this question
1  
What's the definition (DDL) of your server audit? If you could post your CREATE SERVER AUDIT command that would be helpful (feel free to make file paths and sensitive information anonymous). –  Thomas Stringer Jun 18 '13 at 18:13
    
I selected unlimited for the max file size in the GUI. When I'm generating the script it's setting the maxsize to 0. Is there something wrong with setting maxsize to unlimited? –  user20907 Jun 18 '13 at 18:26
    
I was able to create the audit using the above script. It didn't give any error with the 0 MB file size –  user20907 Jun 18 '13 at 18:52
    
Even though the documentation says Specifying a value lower than 2 MB will raise the error MSG_MAXSIZE_TOO_SMALL., I tried using 0 MB and it does work -- it's interpreted as UNLIMITED. –  Jon Seigel Jun 18 '13 at 19:39
    
@Jon yep, I've been trying to add this comment to the documentation, but keep getting Error occurred while saving your data: "I found this quite unintuitive. If I create a server audit with MAXSIZE = UNLIMITED, the DDL replaces UNLIMITED with 0 MB (as does SSMS if you use the UI to apply or script it out). Following the documentation, this should raise an error, as it's less than the 2 MB minimum. The docs should list this equivalence somewhere." –  Aaron Bertrand Jun 18 '13 at 20:28
show 4 more comments

2 Answers

There is no way to specify the time-span for which audit-files are kept. They are kept based on size and number of rollover files. The old ones will be deleted then. If rollover = 0 and you restart the audit every day, you would only ever have 1 file for example. If you want an "advanced archive mechnanism" you have to store the files elsewhere, i.e. in a special Datawarehouse. (recommended)

share|improve this answer
add comment

In SQL Server 2012, the Create Audit dialog enables to specify the audit files size and number. You cannot specify the time the times are saved, but the file size and number might be enough to accomplish what you're looking for

Maximum rollover files - the number of files kept in the system. When the maximum number is reached, the new files overwrite the oldest ones. The default value is unlimited

Maximum files - the number of files kept in the system. When the maximum number is reached, the old files will not be overwritten, and storing new audit information will fail

Maximum file size (MB) sets the size of the target file. When the specified size is reached, a new file is created. The default value is unlimited

enter image description here

MSDN - CREATE SERVER AUDIT (Transact-SQL)

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.