Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Is it possible to view delete statements that have recently occurred in the Transaction Log?

share|improve this question
    
The statements themselves aren't logged to the transaction log. What recovery model is your database in? This answer might be useful –  Martin Smith May 28 '12 at 10:01
add comment

3 Answers 3

up vote 6 down vote accepted

you wont find the exact scripts that were executed on sql.(in the transaction log)

A transaction log is a file that contains information regarding every change that has been made to the database. This includes data modifications (transactions), database modifications, and backup/restore events.

The primary purpose the transaction log is to provide a method to be able to restore a database to a point-in-time when necessary. This can include rolling back transactions to a certain time, or to roll forward transactions from a full backup restoration.

to know more about Transaction log http://www.sqlservercentral.com/articles/Design+and+Theory/63350/

here is a script to show you recent ran delete queries

SELECT deqs.last_execution_time AS [Time], dest.TEXT AS [Query]
FROM sys.dm_exec_query_stats AS deqs
CROSS APPLY sys.dm_exec_sql_text(deqs.sql_handle) AS dest
where dest.TEXT like '%Delete%from%'
ORDER BY deqs.last_execution_time DESC
share|improve this answer
    
FYI: FROM is an optional keyword in a DELETE statement, so I wouldn't include it in the matching predicate here. –  Jon Seigel May 29 '12 at 2:12
    
True the From in delete statement is optional, i kept it just to limit the results of my query. but yeah you are right –  AmmarR May 29 '12 at 5:10
add comment

You can view the individual rows that were deleted by looking for LOP_DELETE_ROWS operations still in the log:

select * from fn_dblog(NULL, NULL) 
where Operation = 'LOP_DELETE_ROWS'

If the log was recycled (in simple recovery model) or truncated by backup (in full or bulk recovery model) then you will only be able to see the log operations still available in the log.

Understanding the log operations is quite complex, you need to be aware of things like undo or compensating operations to make sense of some log patterns you can encounter, but straight forward committed DELETEs are fairly easy to comprehend.

share|improve this answer
add comment

There are various visual tools available on the net.

If you want to read the transaction log on SQL Server 2000 you might want to check SQL Rescue since it’s free.

However, if you are using SQL Server 2005 or later, or if you need more details and the ability to read transaction logs on an x64 system then you’ll need ApexSQL Log for this.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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