确定删除表中所有行的操作 [英] Identify the action that is deleting all rows in a table

问题描述

有三个不同应用程序使用的SQL Server 2012数据库.在该数据库中，有一个包含约50万行的表，由于某种神秘的原因，该表有时会被清空.我认为这可能是由于:

There is SQL Server 2012 database that is used by three different applications. In that database there is a table that contains ~500k rows and for some mysterious reason this table gets emptied every now and then. I think this is possibly caused by:

• 不带where子句的删除查询
• 循环删除查询

我正在尝试通过查看代码来查找此问题的原因，但并不高兴.我需要替代策略.我认为我可以使用触发器来检测什么/为什么所有行都被删除，但是我不确定该怎么做.所以:

I am trying to locate the cause of this issue by reviewing code but no joy. I need an alternate strategy. I think I can use triggers to detect what/why all rows get deleted but I am not sure how to go about this. So:

• 我可以使用触发器来检查查询是否试图删除所有行吗?
• 我可以使用触发器记录有问题的查询和发出该查询的应用程序吗?
• 我可以使用触发器将此类操作记录到文本文件/数据库表/电子邮件中吗?
• 有更好的方法吗?

• Can I use triggers to check if a query is attempting to delete all rows?
• Can I use triggers to log the problematic query and the application that issues that query?
• Can I use triggers to log such actions into a text file/database table/email?
• Is there a better way?

推荐答案

您可以使用扩展事件来监视系统. 这里是一个简单的屏幕快照.

You can use Extended Events to monitor your system. Here a simple screen shot where are.

一个简单的策略可以监视删除和截断语句. 引发此事件时，会将详细信息写入文件.

A simple policy can monitor for delete and truncate statements. When this events are raised details are written into file.

在此屏幕上显示了为delete语句收集的详细信息(您可以配置脚本以收集更多数据).

Here a screen with details (you can configure the script to collect more data) collected for delete statement.

在此使用脚本，修改输出文件路径

Here the script used, modify the output file path

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

这篇关于确定删除表中所有行的操作的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！