How do I view the transaction log in SQL Server 2008?

Question

Hello,

I want to know how to view all the transactions taken during a particular period of time. I know there is a log file, ending with .ldf, created for each database. But how do I view this file?
Is there any tool in the SQL Server studio that can enable me to view the transactions for a given time period?

The reason for me wanting to view the log file is that, last week during a power outage, certain amount of data was not written. And one my friend had also messed up some of the data (unfortunately, she doesn't remember what she did).

Thanks in advance.

Answer 1

There is no way to read the transaction log file using SQL Server. However, there are 3rd party tools (e.g Quest Log Reader) that should help you achieve this.

---

every day is a school day

Answer 2

Are there any free tools, just for the viewing purpose? I saw a lot of tools, but all too expensive. Quest Log Reader is priced at 4-digits. I found one but only works for SQL server 2000.

Answer 3

Are there any free tools, just for the viewing purpose? I saw a lot of tools, but all too expensive. Quest Log Reader is priced at 4-digits. I found one but only works for SQL server 2000.

Well, you can use undocumented

DBCC LOG(<databasename >, <type of output>)

<type of output>

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.


You can also use fn_dblog  Even though usage is cumbersome it can still give you some good information.

Answer 4

I have been using APex sql log http://www.apexsql.com/sql_tools_log.asp and fn_dblog undocumented function for this purpose.

---

Thanks, Leks

Answer 5

OR Red Gate also have some tools but like apex sql you will have to pay for it.

Answer 6

I found the tool from Red Gate for free. Unfortunately, its only for sql server 2000. Is there a simpler way than using fn_dblog?
How do I use fn_dblog? (sorry for this stupid question, but I have to ask it)

Answer 7

Hi,

 It enables you to read from you transaction log which contains very valuable information about stuff that is happening in your database.

select

 

* from fn_dblog (null,null) ..


EXAMPLE:

SELECT

 

*

FROM

 

::fn_dblog(NULL, NULL)

WHERE

 

operation = 'LOP_DELETE_SPLIT'

---

Thanks, Leks

Answer 8

Hello,

I want to know how to view all the transactions taken during a particular period of time. I know there is a log file, ending with .ldf, created for each database. But how do I view this file?
Is there any tool in the SQL Server studio that can enable me to view the transactions for a given time period?

The reason for me wanting to view the log file is that, last week during a power outage, certain amount of data was not written. And one my friend had also messed up some of the data (unfortunately, she doesn't remember what she did).

Thanks in advance.

I am facing the same issue.  The undocumented command does not helps to read what queries where run on a database. 

Only third party tools have the intelligence to read the sql log files which can be interpreted in the desired way.  The cost of such tools is high.  However, that may probably be due to the fact that the company may beaware that there is no other alternative for SQL professionals.