Define an action that deletes all rows of a table

Question

There is a SQL Server 2012 database that is used by three different applications. In this database, there is a table containing the rows ~ 500 thousand. And for some mysterious reason, this table is empty from time to time. I think this is possibly caused by:

• Delete request without where clause
• Loop delete request is a thing of the past

I am trying to find the cause of this problem by looking at the code, but without joy. I need an alternative strategy. I think I can use triggers to determine that / why all rows are deleted, but I'm not sure how to do this. So:

• Can triggers be used to check if a query is trying to delete all rows?
• Can I use triggers to register the problem request and the application that causes this request ?
• Can I use triggers to record such actions in a text file / database table / email?
• Is there a better way?

Answer 1

You can use the Advanced event to monitor your system. Here is the simplest screenshot.

A simple policy can track operators to delete and truncate . When these events rise, the details are written to the file.

A screen with detailed information is displayed here (you can configure the script to collect more data) collected for the delete statement.

The script is used here, change the path to the output file

CREATE EVENT SESSION [CheckDelete] ON SERVER ADD EVENT sqlserver.sql_statement_completed(SET collect_statement=(1) ACTION(sqlserver.client_connection_id,sqlserver.client_hostname) WHERE ([sqlserver].[like_i_sql_unicode_string]([statement],N'%delete%') OR [sqlserver].[like_i_sql_unicode_string]([statement],N'%truncate%'))) ADD TARGET package0.event_file(SET filename=N'C:\temp\CheckDelete.xel',max_file_size=(50)) WITH (MAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=30 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=OFF,STARTUP_STATE=OFF) GO 

Answer 2

This is an opportunity that can help you. It creates a trigger on Table1 , which sends an email when the DELETE process contains more than 100 entries. I would modify the message to include some useful data, for example:

• Process ID ( @@SPID )
• Host ( HOST_NAME() )
• Application Name ( APP_NAME() )
• And maybe the whole request

---

 CREATE TRIGGER Table1MassDeleteTrigger ON dbo.Activities FOR DELETE AS DECLARE @DeleteCount INT = (SELECT COUNT(*) FROM deleted) IF(@DeleteCount > 100) EXEC msdb.dbo.sp_send_dbmail @profile_name = 'MailProfileName', @recipients = ' [e-mail] ', @body = 'Something is deleting all your data!', @subject = 'Oops!';