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.
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().