Issue with excessive error messages in SQL Server Log during the reading of Server Audit session (.sqlaudit) files

Question

Hello,

I am encountering an issue with a high volume of error messages in the SQL Server Log while reading from an active Server Audit session. This problem occurs in SQL Server 2019 and later versions.

I have set up database auditing using Server Audit technology. To read data from newly created .sqlaudit session files associated with an active Server Audit database session, I am utilizing a PowerShell script that leverages the Microsoft.SqlServer.XEvent.Linq library.

While the data appears to be read correctly, I noticed an excessive number of error messages in the SQL Server Log:

[ERROR] Log file 'Full path to .sqlaudit file' cannot be deleted. Last error code from CreateFile is 32.

This issue is puzzling, as I don’t encounter this error when performing the same actions on SQL Server 2017.

Please find attached SQL scripts to set up the Server Audit and its respective Database Audit Specification for testing, as well as the script to generate multiple .sqlaudit session files and the PowerShell script used to read data from those files.

Steps to Reproduce the Issue:

1. Use SQL Server 2019, or modify the PowerShell script to reference the correct SQL Server shared path for your version, ensuring the necessary assemblies for reading Server Audit session files are loaded:

• [Code line 11] $sharedPath = "C:\Program Files\Microsoft SQL Server\150\Shared" # Adjust based on SQL tool version

1. Execute the SQL script 'CreateTestDB' to create the test database and table.
2. Modify and run the SQL script 'SQLAUDIT_SERVER_AUDIT' to create the Server Audit session.
3. Modify and run the SQL script 'SQLAUDIT_DB_LEVEL_SPECIFICATION' to create the Database Audit Specification for the Server Audit in step 3.
4. Run the SQL script 'TestQuery' to generate multiple .sqlaudit session files. During its ('TestQuery') execution, run the PowerShell script 'ReadSessionFiles_SQLAudit' to read data from the .sqlaudit session files.
5. Check the SQL Server Log; you should soon see frequent error messages indicating: [ERROR] Log file 'Full path to .sqlaudit file' cannot be deleted. Last error code from CreateFile is 32.

Is there a way to resolve this issue of excessive error messages in the SQL Server Log?

Answer 1

Hi,

I have enabled audit on SQL server 2022 with given scripts but got no error in SQL Server logs.

You may try following to investigate:

The error code 32 typically indicates that the file is being used by another process, which is why it cannot be deleted. Here are a few steps you can take to resolve this issue:

1. Check for Open Handles: Ensure that no other process is using the .sqlaudit file. You can use tools like Process Explorer to identify which process might be holding the file open.
2. SQL Server Permissions: Verify that SQL Server has the necessary permissions to delete the file. Sometimes, insufficient permissions can cause this issue.
3. Audit Configuration: Review your SQL Server audit configuration. If you have set a maximum number of files without enabling rollover, SQL Server might not be able to delete old files. Consider using the MAX_ROLLOVER_FILES option instead of MAX_FILES1.
4. Manual Deletion: If the file is not in use and permissions are correct, try manually deleting the file after stopping the SQL Server service temporarily.

If these steps don’t resolve the issue, you might need to look into more specific configurations or potential bugs related to your SQL Server version2.

Answer 2

Hi @Aleksandar Miljkovic (amiljkov) ,

Thank you for your reaching out and welcome to Microsoft Q&A!

Looking at the MSDN page for CREATE SERVER AUDIT when you set MAX_Files, SQL will not roll over to the first file when the limit it reached, nor does it remove files.

If you re-create your audit and replace max_files with max_rollover_files, when it reaches the end of file #4, it will start over on file #1.

Before doing this, please pay attention to the following things:

1. Find out what your required retention policy is for the audit files
2. Verify that you can meet the requirements in #1 with the max_ setting you end up with

Regardless of which option you choose, nowhere in the page for create server audit does it indicate that SQL removes files (most likely so MS doesn't get involved in a "lost auditing file" issue.)

Feel free to share your issues here if you have any concerns!

Best regards,

Lucy Chen

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

https://docs.microsoft.com/en-us/answers/support/email-notifications