locked
SQL Audit Files(.sqlaudit) Roll Over not Working RRS feed

  • Question

  • Hi All,

    By default audit feature is enabled in the sql server 2014 . We have given the below configured values ,

    no of files to roll over -> 10 files

    maximum size ->1oomb

    As per the audit feature once 10 files are reached the 11 th file will get generated and the 10 files gets deleted,but in our case the old files are not getting deleted . So we face space issues frequently in the drive where audit is running. Please let me know if this can be solved. Also the old files cannot be deleted unless sql service is stopped. We have tried dropping / disabling audit feature also but no luck .



    • Edited by Vishalsurya Wednesday, September 20, 2017 8:40 AM
    Wednesday, September 20, 2017 8:39 AM

All replies

  • Hi Vishalsurya,

    >> As per the audit feature once 10 files are reached the 11 th file will get generated and the 10 files gets deleted,but in our case the old files are not getting deleted.

    The behavior is well documented in the White paper: ‘When the active audit file reaches the maximum size specified, it automatically rolls over to a new file. This process continues up to the maximum number of rollover files specified by the MAX_ROLLOVER_FILES argument. When or if the maximum number of rollover files is reached, SQL Server begins deleting an existing audit file for every new one created, starting with the oldest first. Note that if the delete operation fails for whatever reason, the audit silently continues’.

    In this case, I’d suggest you make sure your SQL Server service account has full control permission on the folder that audit files are located.

    If you have any other questions, please let me know.

    Regards,
    Lin


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.


    Wednesday, September 20, 2017 2:28 PM
  • Hi,

    The SQL Service account has full permissions to the folder , Even though the files are not getting deleted. Please advice

    Wednesday, September 20, 2017 2:57 PM
  • Hi Vishalsurya,

    Try disable/enable the audit session see if it removes the file.

    If you have any other questions, please let me know.

    Regards,
    Lin

    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, September 20, 2017 4:14 PM