how Reading the transaction log in SQL Server

Question

how Reading the transaction log in SQL Server

Ozra Hasannejad 21

Some data is missing in our warehouse. To check the reason, we need to check the log for that time. We will be very happy if you help us in this field.

Seeya Xi-MSFT 16,586 Reputation points

2022-12-20T06:27:51.287+00:00

Hi @Ozra Hasannejad ,

We have not received a response from you. Did the reply could help you? If the response helped, do "Accept Answer". If it doesn't work, please let us know the progress. By doing so, it will benefit all community members who are having this similar issue. Your contribution is highly appreciated.

Best regards,
Seeya
Seeya Xi-MSFT 16,586 Reputation points

2022-12-21T05:39:03.347+00:00

Hi @Ozra Hasannejad ,

In order to benefit all community members who are having this similar issue, please choose an answer to accept for the answer you think is useful to you. Your contribution is highly appreciated.

Best regards,
Seeya

4 answers

Your answer

Seeya Xi-MSFT 16,586 Reputation points

2022-12-20T06:27:51.287+00:00

Hi @Ozra Hasannejad ,

We have not received a response from you. Did the reply could help you? If the response helped, do "Accept Answer". If it doesn't work, please let us know the progress. By doing so, it will benefit all community members who are having this similar issue. Your contribution is highly appreciated.

Best regards,
Seeya
Seeya Xi-MSFT 16,586 Reputation points

2022-12-21T05:39:03.347+00:00

Hi @Ozra Hasannejad ,

In order to benefit all community members who are having this similar issue, please choose an answer to accept for the answer you think is useful to you. Your contribution is highly appreciated.

Best regards,
Seeya

Answer 1

Hi,

The transaction log file is not intended for direct reading by users but for internal use. What you are asking is a level 500 actions (internals) and it is not officially supported.

With that said, it can be done using an undocumented functions/commands like DBCC log, fn_dblog(), fn_dump_dblog(), fn_full_dblog()

You can find several posts in my blog about using these functions.

If you are registered to the PASS Summit then I highly recommend to watch the recording to my session. This was exactly the topic I spoke about.

You can see the promo of the session here: https://www.facebook.com/ronen.ariely/videos/793222385150305

To watch the recording and download all the demo code you can follow this:

(1) Check the list of speakers speakers: https://passdatacommunitysummit.com/speakers/all-speakers
(2) Search: Ronen
(3) Click the speaker name to see the session information

I think that only people that were registered to the vent can download it.

----------

Ronen Ariely
Personal Site | Blog | Facebook | Linkedin

Answer 2

Erland Sommarskog 121.4K MVP Volunteer Moderator

There is no documented way to use the transaction log for this.. There are however two undocumented functions available:

sys.fn_dblog which reads from the active portion of the transaction log.
sys.fn_dump_dblog which reads from the transaction log backups.

Assuming that you backup the transaction log every 15 minutes or so, it's the latter one you need to use.

None of these are easy to use and you need to have the time and patience to decipher the information.

I am not going to give any examples here, because I have not used them very much myself. Nor will I give any links, because you can use Google just as well as I can. Except that you should read https://www.sqlskills.com/blogs/paul/using-fn_dblog-fn_dump_dblog-and-restoring-with-stopbeforemark-to-an-lsn/, because there is an important caveat about sys.fn_dump_dblog.

There are also commercial tools available, or at least one: https://www.apexsql.com/sql-tools-log/. I have not used it myself, and I cannot vouch for it. (There have been others in the past, but I don't know if they are still in business.)

If all you want to do is to restore the data, restore an old backup of the database, and apply transaction logs WITH STANDBY to get read-only access of the database at that point in time, but you can still apply more log backups.

Ronen Ariely 15,206 Reputation points

2022-12-17T11:20:16.623+00:00

Hi,

You should really watch my session at PASS Summit Erland

There are however two undocumented functions available:

There are four options (as I mentioned in my answer) and not two and using fn_dblog and fn_dump_dblog is very old. The better and improved way is using the newer function fn_full_dblog which was added in SQL Server 2017 and was totally changed and improved even more in version SQL Server 2019. As much as I tested there is no change in SQL Server 2022 which is the same as in 2019
Erland Sommarskog 121.4K Reputation points MVP Volunteer Moderator

2022-12-17T11:26:29.877+00:00

As it sometimes happens, when I started to compose my answer, yours did not display and probably you had not submitted yours, as it took me some time to research my answer. I had to google around to find the name of fn_dump_dblog. I did not know about the newer function.

In any case, we are in complete agreement that this is an advanced operation.

And the real answer is really: you should have had auditing in place.
Ronen Ariely 15,206 Reputation points

2022-12-17T11:32:34.647+00:00

Here are some feedback my session got at the PASS Summit :-)

Great session.

,This was, by far, the best session I attended today! Super great information presented in a straightforward fashion!!

,This was a hot topic for me. Sadly time for the presentation was very short and so that not for all my raised questions was the time to become answered. But I think what I have learned can safe me a lot of pain in the furture.

,Excellent presentation - very interesting.
I really liked that you closed down low level questions. Those are not appropriate in a level 400-500 session,One of the best session this year content wise. Thank You!

,Great session and great find. The info presented has the potential of being a great resource for discovering what might have happened within your SQL Server. Thanks for sharing this.

,Very interesting, but complex and difficult. It was sometimes a bit hard to follow your logic. :)

, Ronen, thank you for that presentation. Deep dive will merit me going back and re-watching the presentation but I am excited to learn this and get this under my belt as well. I've been looking for something like this. Glad you did this session.

:-)

Answer 3

Hi @Ozra Hasannejad ,

I've used SQL Apex before and it's really a great program. The main advantage of it is that any operation can be seen visually.
Also, you can refer to this blog:
https://www.sqlshack.com/how-to-read-a-sql-server-transaction-log/

Best regards,
Seeya

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 4

Peter Groft 106

Right-click SQL Server Logs, point to View, and then click either SQL Server Log or SQL Server and Windows Log.
Expand SQL Server Logs, right-click any log file, and then click View SQL Server Log. You can also double-click any log file.

Hope This Works,
Peter

Ronen Ariely 15,206 Reputation points

2022-12-24T20:49:05.057+00:00

You confuse the error log and the transaction log :-)

check other response for more information and check this doc about what a transaction log file is: https://learn.microsoft.com/sql/relational-databases/logs/the-transaction-log-sql-server?view=sql-server-ver16&WT.mc_id=DP-MVP-5001699

Share via

how Reading the transaction log in SQL Server

4 answers

Here are some feedback my session got at the PASS Summit :-)

Your answer