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.

Answer 9

Late answer, but I hope it’s useful:

As others already mentioned there is no default way for viewing the transaction log in SQL Server. I suggest using third party tools like ApexSQL Log or SQL Log Rescue (free but sql server 2000 only) for this.

Additionally, you may want to try to view the transaction log manually, by using undocumented DBCC LOG and fn_dblog functions (already described in this thread)

Answer 10

Hi

I recommended you to try SQL log analyzer that can help you to view and analyze the sql server transaction log file. For more information visit: http://www.log-analyzer.sqldatarecovery.org/

Answer 11

The fn_dblog() function is very helpful in such situation but it will only provide you the detail about your transaction but cannot be helpful in recovery.

Answer 12

You can use fn_dblog to examine the transaction log to look for activities such as page splits or objects (such as tables or indexes) being dropped. To know more about this function take a look on here: 

https://logicalread.com/sql-server-dbcc-log-command-tl01/#.Whe2Y0qWbIU

http://www.sqlserverlogexplorer.com/reading-sql-server-transaction-logs/