Product:
Microsoft SQL server 2019
Issue:
How do i log if a table is deleted or truncated in SQL server databases?
Solution:
Create a audit log file with this SQL command:
USE master
CREATE SERVER AUDIT [Truncate_Audit]
TO FILE
(FILEPATH = N'c:\temp\logs'
,MAXSIZE = 1 GB
,MAX_ROLLOVER_FILES = 20
,RESERVE_DISK_SPACE = OFF
)
WITH
(QUEUE_DELAY = 1000
,ON_FAILURE = CONTINUE
)
ALTER SERVER AUDIT [Truncate_Audit] WITH (STATE = ON)
Then to make all database that start with letter A to be monitor run below SQL command:
DECLARE @command varchar(500)
SELECT @command = 'IF ''?'' LIKE ''A%'' BEGIN USE ?
EXEC(''Create database audit specification [DatabaseAuditSpecification-Truncate]
FOR SERVER AUDIT [Truncate_Audit]
ADD (Delete on database::[?] by [dbo])
WITH (STATE=ON)'')END'
EXEC sp_MSforeachDB @command
Now, do some add and delete of a table in your database to create some action to log.
Then to see the log enter this SQL command:
SELECT event_time, succeeded, object_id, object_name,session_server_principal_name,server_principal_name,server_instance_name,database_name,statement,file_name,audit_file_offset
FROM fn_get_audit_File ('c:\temp\logs\Truncate_Audit_*.sqlaudit',null,null) order by event_time DESC
You have to adjust the SQL code for your needs.
More Information:
SQL Server auditing with Server and Database audit specifications
https://docs.microsoft.com/en-us/sql/relational-databases/security/auditing/create-a-server-audit-and-database-audit-specification?view=sql-server-ver15
https://www.sqlshack.com/various-techniques-to-audit-sql-server-databases/
https://sqlship.wordpress.com/2011/06/30/how-to-capture-delete-and-truncate-t-sql-using-database-audit-specification/
File manager show that only a part of the disk is free space.
Files are not removed from windows until the recycle bin is empty, so if you try to clean a hard disk, you need to empty the recycle bin also when you are done deleting files.
