SQL Server Data Management: Monitoring Delete and Truncate Operations

Tracking data deletions or truncations in SQL tables can be a challenging task, as it becomes difficult to identify the responsible party. While delete triggers offer a solution by capturing deleted data information, including host or user details using the HOST_NAME() SQL function, they fall short in recording truncated information due to the absence of data logs in log files. To address this limitation, SQL Server has introduced an auditing feature that enables comprehensive monitoring of activities at both server and table levels. In this blog post, we will describe how to capture delete, and truncate data in user-defined tables. Step 1: Connect to SQL Server, go to the Audits section, and create a new audit following the instructions below.

<<please click on each image to expand it>>

Step 2: the Create Audit window will open and provide the information as below and click ok.

• Audit name: Provide a name for the audit information file.
• On audit log failure: Always select "Continue" to allow SQL statements to proceed even if the audit log fails.
• Audit destination: Based on your project requirements, choose the appropriate property. For this demonstration, I have selected the commonly used "File" property.
• Path: Select the location to save the audit log file if you have chosen the file option.
• Audit file max limit: Choose the maximum rollover files with the "Unlimited" option if you want to store all audit files. Alternatively, select the maximum number of files and specify how many files the audit file should retain. Once the maximum number of files is reached, older files will be automatically purged.
• Maximum file size: Specify the maximum size for each file.
• Reserve disk space: Depending on your project requirements, you can check this option

Step 3: Once the audit is created, it will be in a disabled state by default. Right-click on it and select "Enable" to start storing the audit information

Step 4: Open the database on which you want to audit the delete and truncate statements, then access the New Database Audit Specification as shown below.

Step 5: The Create Database Audit Specification window will open, where you need to provide the required information as shown below. Click "OK" to proceed

• Name: Provide a name for this audit log information.
• Audit: Choose the audit file that we created in Step 2.
• Audit Action Type: In the dropdown menu, select the "DELETE" command as it captures both delete and truncate information on the database tables.
• Object Class: Select "DATABASE" as we want this audit to cover the entire database.
• Object Name: Click the three dots ellipsis, browse the object names, and in the browse window, select the database on which we need this audit.
• Principal Name: Click the three dots ellipsis, browse the object names, and select the "dbo" schema if all your database tables are mapped to the "dbo" schema alone. If you have other schemas in the database, select them as well based on your project requirements.

Step 6: Now, perform some delete and truncate operations on tables within the selected database.

My Tables and their underlying data:

After truncating and deleting the data, the tables look something like this

Step 7: Now let's see the log file in the specified location and try to access it through SQL commands. Audit File:

Log data in the Audit File:

So, this way, we can audit not just the DELETE and TRUNCATE commands in the database, but we also have support for other commands at both the database level and server level. Please try it yourself, and if you have any doubts or need assistance, feel free to leave a comment

Thanks for reading,.. 🙏