Last DML operation

04 September,2012 by Jack Vamvas

Question : How can I  find the last DML operation on a SQL Server database?

Answer:  Firstly , ask yourself why you need to find the last DML operation. The answer may influence the technique used. For example, is  to troubleshoot a suspected row DELETE or are you troubleshooting a SSIS workflow task?

A combination of techniques may be used – a then construct a picture of the change.

Methods to get the Last DML operation

 1)       Is Change Data Capture (CDC) enabled? The CDC captures both the DML change and the actual data changed. Before SQL Server 2008 , a DBA required custom development using triggers, schema changes and stored procedure to capture changes. This provided some challenges to the DBA , such as having to track source table schema changes.  

2)       Do you have Change Tracking enabled?  Change Tracking is available. If enabled, applications can query changes to data in a database. A typical question may be “what rows have changed?” .

The difference between CDC and Change Tracking is that CDC captures both the DML occurrence and the actual data changed, whereas Change Tracking captures only the DML occurrence change.

3)        Find the tables you think are most commonly used. Do they have a timestamp column? Are there any other clues , such as Logon ID – which may indicate who and when the change was last made.

4)       Analyse the Security section in Event Viewer. If Active Directory Authenticated security is used – it may be easier to track the Logon account to a specific individual. It can be  difficult to estimate when a change occurred and then map to a specific logon through the Security Logs. Event Viewer security logs grow quickly on a busy Production SQL Server.

5)       Read  the Transaction Log with the  undocumented function fn_dblog().

Read More

How to request SQL Server troubleshooting

SQL Server – default trace FAQ

SQL Server – Audit Backup\Restore Event with SQL Default trace

Author: Jack Vamvas (


Verify your Comment

Previewing your Comment

This is only a preview. Your comment has not yet been posted.

Your comment could not be posted. Error type:
Your comment has been posted. Post another comment

The letters and numbers you entered did not match the image. Please try again.

As a final step before posting your comment, enter the letters and numbers you see in the image below. This prevents automated programs from posting comments.

Having trouble reading this image? View an alternate.


Post a comment on Last DML operation | SQL Server Performance Tuning | SQL Server DBA:Everything | FAQ | Contact|Copyright & Disclaimer