MS SQL Express - Find when a Query was run

Question

I have an ecommerce website in .NET and using MS SQL Server Express, Version info:

Microsoft SQL Server 2014 - 12.0.4100.1 (X64) Apr 20 2015 17:29:27
Copyright (c) Microsoft Corporation Express Edition (64-bit) on Windows NT 6.3 (Build 9600: ) (Hypervisor)

There is a DELETE query running from my code and I can not figure out what is triggering it. (My entire system shows one place that has that Delete code, but that can not be the problem.)

I need to find out from the database when the Delete is running so I can try to use that Data to help me solve my problem.

Even if I can't find going backwards, I would like to install/configure something to help me find it in the future, as it is a recurring problem

• Scott Hodgin - Retired: I have used triggers to help in these situations - I usually create a trigger to email the timestamp (and other important information from the inserted or deleted virtual tables). It helps me identify the time period of the action and relevant data associated with the event.
• Scott Hodgin - Retired: Look at this post about setting up SQL Mail in SQL Express
• Yoni: @ScottHodgin How do I create a trigger to do this? I don't need email, I could use a trigger and write to another table the relevant data

Answer 1

Here is an example of creating a delete trigger to capture the results of the delete

--demo setup
DROP TABLE IF EXISTS TestDelete;
DROP TABLE IF EXISTS TestDeleteSave;
--create a TestDelete table and a TestDeleteSave
--the TestDeleteSave table will hold the contents of deleted rows
--from TestDelete when the trigger is fired
CREATE TABLE TestDelete (Id int, Description varchar(50));
CREATE TABLE TestDeleteSave (Id int, Description varchar(50));
go
--insert sample row into TestDelete
INSERT INTO TestDelete (
 Id
 ,Description
 )
VALUES (
 1
 ,'Test Description'
 )
go
--create a delete trigger on the TestDelete table.
--the 'deleted' virtual table will contain the rows that were deleted
--during a transaction
CREATE TRIGGER [dbo].[TestDelete_Delete] ON [dbo].[TestDelete]
AFTER DELETE
AS
BEGIN
 SET NOCOUNT ON;
INSERT INTO TestDeleteSave
SELECT *
FROM deleted
END;
GO
ALTER TABLE [dbo].[TestDelete] ENABLE TRIGGER [TestDelete_Delete]
GO
--now, delete a row from the TestDelete table which will fire the delete trigger
delete from TestDelete where id = 1
--now, select affected rows from the TestDeleteSave table
select * from TestDeleteSave

Answer 2

In addition to Scott's excellent idea, you can also do ad-hoc tracing by running the Profiler, or logging a Trace or Extended Events Session to capture detailed information with the DELETE query, when it was executed, and who executed it. Hopefully between these options and Scott's idea, you're able to solve your problem.

Answer 3

Instead of modifying the database with triggers, I'd suggest using Extended Events to capture the query calls. You just have to make sure you use some filters to limit the info being returned to just DELETE statements and to just the database in question. Also, add an action to get the user name. Something like this would work:

CREATE EVENT SESSION DeleteStatements
ON SERVER
 ADD EVENT sqlserver.sql_batch_completed
 (ACTION
 (
 sqlserver.username
 )
 WHERE (
 sqlserver.equal_i_sql_unicode_string(sqlserver.database_name, N'AdventureWorks')
 AND sqlserver.like_i_sql_unicode_string(batch_text, N'%DELETE%')
 )
 );

Answer 4

Here's an example of an Extended Events Session as mentioned by J.D.:

CREATE EVENT SESSION [CaptureDelete]
 ON SERVER
 ADD EVENT sqlserver.sql_batch_starting(
 ACTION (sqlserver.client_app_name,
 sqlserver.client_hostname,
 sqlserver.database_name,
 sqlserver.nt_username,
 sqlserver.server_principal_name,
 sqlserver.sql_text)
 WHERE ([sqlserver].[like_i_sql_unicode_string]([sqlserver].[sql_text], N'%DELETE%YourTable%'))
 )
 ADD TARGET package0.event_file
 (SET
 filename = N'C:\SomeFolder\CaptureDelete.xel',
 max_file_size = (1024),
 max_rollover_files = (2)
 )
 WITH (
 MAX_MEMORY = 2048 KB,
 EVENT_RETENTION_MODE = ALLOW_MULTIPLE_EVENT_LOSS,
 MAX_DISPATCH_LATENCY = 3 SECONDS,
 MAX_EVENT_SIZE = 0 KB,
 MEMORY_PARTITION_MODE = NONE,
 TRACK_CAUSALITY = OFF,
 STARTUP_STATE = OFF
 );
GO

After you create it you can Watch Live Data to see what's happening in real time or you can query what was captured later by querying the results.