SQL Server – View Delete Statements in the Transaction Log

sql serversql-server-2008-r2transaction-log

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

Best Answer

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

Related Solutions

Sql-server – Transaction Log Backups on SQL Server

One can attempt to shrink a log file at any time, regardless of recovery model.

The amount it shrinks will only go back to just after the last in-use virtual log file (VLF). VLFs are logical regions of a physical log file. You can use the DBCC LOGINFO command to dump a list of all the VLFs for the database-in-context. In the Status column, 0 means unused, and 2 means in-use. A shrink operation will only reclaim contiguous VLFs at the end of the physical file which have status 0.

The difference between recovery models is the mechanism that maintains the status of each VLF. In FULL recovery, VLFs can't be overwritten at least until they've been backed up in a log backup.

Just a note that switching to SIMPLE and then back to FULL usually isn't a good idea, and should only be used as a very last resort in exceptional circumstances. Doing this will break the log chain (which can be restarted with a full or differential backup) and lose the ability to recover to a point-in-time within the log records that were dropped on the floor.

Sql-server – How does the transaction log record a transaction

I could not put this as comment so here it is for you. It is not compete answer to your question because your question can either be correctly answered by person working in Microsoft or Paul Randal(I guess). All I can say for all paractical purposes every information is logged.

You can read the contents of log file using undocumented command

select * from fn_dblog(Null,Null)

If you run it you can see lot of information related to database, pages,extents,locks ect. But it would be difficult for you to extract information from it as it requires a level of expertise to decipher the output.

If you read Books Online document SQL Server Transaction Log architecture and Management it says

Many types of operations are recorded in the transaction log. These operations include:

•The start and end of each transaction.

•Every data modification (insert, update, or delete). This includes changes by system stored procedures or data definition language (DDL) statements to any table, including system tables.

•Every extent and page allocation or deallocation.

•Creating or dropping a table or index.

Log records for data modifications record either the logical operation performed or they record the before and after images of the modified data. The before image is a copy of the data before the operation is performed; the after image is a copy of the data after the operation has been performed.

AFAIK there is no information about query which was fired but the changes which query made is written in transaction log. Changes made to page, extents , locks that were taken, resources that were locked.

Best Answer

Related Solutions

Sql-server – Transaction Log Backups on SQL Server

Sql-server – How does the transaction log record a transaction

Related Question