Tuesday, January 29, 2013

Can we get historical select/insert/update/delete queries from Sql Server Transaction Log?

The simplest answer is NO. In recent times i had some misunderstanding about the transaction log in Sql Server, i thought we could use it to get the select/insert/update/delete queries we ran on sql server.

After a bit of research on the internet i found out that we can not use transaction log to get those historical queries. The purpose of the transaction log is to use in the recovery process if the database is corrupted and you need to restore the database. This can include rolling back transactions to a certain time, or to roll forward transactions from a full backup restoration.

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.

We can use sql server cache to get the historical queries. My previous post shows how we can get historical T-SQL queries from sql server cache.

You can use below query to read the transaction log.

DBCC LOG(databasename, typeofoutput)

where typeofoutput:

0: Return only the minimum of information for each operation -- the operation, its context and the transaction ID. (Default) 
1: As 0, but also retrieve any flags and the log record length. 
2: As 1, but also retrieve the object name, index name, page ID and slot ID. 
3: Full informational dump of each operation. 
4: As 3 but includes a hex dump of the current transaction log row.

No comments:
Write comments
Recommended Posts × +