I am running a shell script that runs insert, update, delete queries on a table in an SQL Server DB. Is there a way to get a history of all transactions that happened on the table to validate that they occurred as expected. That is the number of updates, inserts and deletes that happened. And any errors that could have occurred.

  • 1
    Your goal is not clear. Does "number of updates" mean the actual number of update statements executed or the number of rows affect by update statements. I suspect that this goal may not end with any useful information. As example, say your code deletes all rows in a table that are prior to the first of the current month when run. How will you validate? Does "1 delete successful" or "356 rows deleted" have a useful meaning?
    – SMor
    Apr 26, 2021 at 15:58
  • number of rows affected is more useful Apr 26, 2021 at 20:21

3 Answers 3


Here's some out-of-the-box features of SQL Server that can help you accomplish your goal. You'll need to proactively enable / set one of them up before they collect the data you need.

For reference:

  1. Triggers - Fire whenever data changes in a table, can implement logic similar to being in the context of a stored procedure.

  2. Temporal Tables - System versioned copies of the user defined tables that track changes.

  3. Change Tracking - Automated tracking of changes for your user defined tables.

  4. Change Data Capture - Keeps track of DML changes to the specified user defined tables.

  5. Audit - Automatically tracks a multitude of actions at the server level.


First of all I should mention that if transaction is applied, you may expect correct result. It is C in ACID.

Maybe transaction log is the feature that you are looking for. Please find a quick guide how to interact with it here.


You can try enabling tracing:

INSERT, UPDATE, DELETE operations will be saved in the file. I note that a lot of things that you don't need will be saved in the file. This is, after all, a diognastic tool.

Please note that the MSSQL user must have write access to this directory (C:\Temp\SampleTrace)

DECLARE @RC int, @TraceID int, @on BIT  
EXEC @rc = sp_trace_create @TraceID output, 0, N'C:\Temp\SampleTrace'  
-- Select the return code to see if the trace creation was successful.  
SELECT RC = @RC, TraceID = @TraceID  
-- Set the events and data columns you need to capture.  
SELECT @on = 1  
-- 10 is RPC:Completed event. 1 is TextData column.   
EXEC sp_trace_setevent @TraceID, 10, 1, @on   
-- 13 is SQL:BatchStarting, 11 is LoginName  
EXEC sp_trace_setevent @TraceID, 13, 11, @on   
-- 13 is SQL:BatchStarting, 14 is StartTime  
EXEC sp_trace_setevent @TraceID, 13, 14, @on   
-- 12 is SQL:BatchCompleted, 15 is EndTime  
EXEC sp_trace_setevent @TraceID, 12, 15, @on   
-- 13 is SQL:BatchStarting, 1 is TextData  
EXEC sp_trace_setevent @TraceID, 13, 1, @on   
-- Set any filter. Not provided in this example  
--EXEC sp_trace_setfilter 1, 10, 0, 6, N'%Profiler%'  
-- Start Trace (status 1 = start)  
EXEC @RC = sp_trace_setstatus @TraceID, 1  

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.