Ever wondered how to read the transaction log for a database? There is an undocumented SQL function sys.fn_dblog which may help you to read T-Log except for the truncated transaction details. We can use this function effectively for point in time recovery at a LSN level.
First lets see how the typical output of the function, I have run the function on the AdventureWorks2012 DB,
select *
from sys.fn_dblog(NULL,NULL)
Note:-The 2 parameters for are sys.fn_dblog StartLSN and EndLSN if you want to see the operation between specific LSN range. Default NULL, NULL will read the entire T-Log.
There you go you can see a CurrentLSN Column, Operations Column, TransactionID, PreviousLSN column etc, I am not going to discuss all columns in detail but we will see the use of some of them in our exercise today.
As you can see in the Operations column you can see operation like INSERT(LOP_INSERT_ROWS), Begin transaction(LOP_BEGIN_XACT), CheckpointEnd(LOP_END_CKPT)etc. Lets carry out an example and see how can we interpret these details.
Lets Create a Table and see what happens in the Log,
CREATE TABLE
Test (a int)
GO
select [Current LSN],Operation,Context,[Transaction ID],[Previous
LSN],[Transaction Name],AllocUnitName,[Begin Time] from sys.fn_dblog(NULL,NULL) ORDER BY [Transaction
ID]
As you can see there is a BEGIN TRAN event(LOP_BEGIN_XACT) with the corresponding Transaction name column as "CREATE TABLE", this is followed by set of INSERT and UPDATE operation(LOP_INSERT_ROWS and LOP_MODIFY_ROW) on various system tables(Refer AllocUnitName Column on the pic) for the new table creation.
Next I insert values to the table,
INSERT INTO
Test Values(1)
GO
INSERT INTO
Test Values(2)
GO
INSERT INTO
Test Values(3)
GO
select [Current LSN],Operation,Context,[Transaction ID],[Previous
LSN],[Transaction Name],AllocUnitName,[Begin Time],[End
Time] from sys.fn_dblog(NULL,NULL) ORDER BY [Transaction ID]
As you can see we have 3 LOP_BEGIN_XACT,LOP_INSERT_ROWS,LOP_COMMIT_XACT set of operation for the 3 row insert and some page allocation tasks. You can also see we have start time for Begin TRAN and a End Time for the Commit Tran. If you see the Context column for the INSERT operation you can see LCK_HEAP indicating inserting rows to a heap table.
Next I ran UPDATE,
UPDATE Test SET
a = 5 where a = 1
As expected you can see LOP_MODIFY_ROW on a LCX_HEAP for the UPDATE statement. Next I ran DELETE,
DELETE from
Test where a =
5
Next I ran,
DROP TABLE
Test
As in CREATE Table statement DROP Table has LOP_BEGIN_XACT,LOP_LOCK_XACT operation followed by updating system tables operations and finally Commit operation LOP_COMMIT_XACT.
Finally Lets see how a rollback looks like in the T-Log,
BEGIN TRAN
INSERT INTO
Test Values(1)
ROLLBACK
There was a INSERT operation then on Rollback there was a DELETE operation of the inserted row and finally Abort Transaction operation(LOP_ABORT_XACT).
Now that we have seen how we can interpret the sys.fn_dblog output now we can see how we can put it to use,
For example if an user DELETED multiple row by mistake and does not know what rows he deleted and comes to you for help and want to restore the database to a state exactly before the data deletion. We usually go back to restoring the backups but if you have multiple users using the DB how will you go to the exact state before the DELETE was issues that's where sys.fn_dblog can be very useful. You can just read the out of the Log around the time the delete was issued then note the CurrentLSN on the LOP_BEGIN_XACT operation for the Delete then Restore backup and Transaction log backup with STOPBEFOREMARK as the CurrentLSN on the LOP_BEGIN_XACT operation.
For assumption lets say my LOP_BEGIN_XACT is '0000002c:000000a9:001e' just prefix lsn:0x to the current LSN Value on LOP_BEGIN_XACT operation in our case it will be lsn:0x0000002c:000000a9:001e.
Now Run RESTORE LOG with STOPBEFOREMARK,
RESTORE LOG
[AdventureWorks2012]
FROM DISK = 'H:\DBBkp\Log\AdventureWorks2012_bkp25.trn'
WITH STOPBEFOREMARK =
'lsn:0x0000002c:000000a9:001e',
RECOVERY;
This should exactly get us back to a state exactly before the DELETE statement issued.
replica rolex orologi, combining elegant style and cutting-edge technology, a variety of styles of replica rolex datejust orologi, the pointer walks between your exclusive taste style.
ReplyDeleteExcellent post. I was always checking this blog, and I’m impressed! Extremely useful info specially the last part, I care for such information a lot. I was exploring this particular info for a long time. Thanks to this blog my exploration has ended. log splitter
ReplyDeleteI exactly got what you mean, thanks for posting. And, I am too much happy to find this website on the world of Google. taxi naar rotterdam airport
ReplyDeleteHi there! Nice stuff, do keep me posted when you post again something like this! 메이저사이트
ReplyDeletei am always looking for some free stuffs over the internet. there are also some companies which gives free samples. 먹튀검증
ReplyDeleteMerely a smiling visitant here to share the love (:, btw outstanding style. 먹튀검증
ReplyDeleteThe writer has outdone himself this time. It is not at all enough; the website is also utmost perfect. I will never forget to visit your site again and again. DSW Near me
ReplyDeleteWonderful article. Fascinating to read. I love to read such an excellent article. Thanks! It has made my task more and extra easy. Keep rocking. Adobe Photoshop Free Download
ReplyDeleteThanks for the blog filled with so many information. Stopping by your blog helped me to get what I was looking for. Now my task has become as easy as ABC. 토토사이트
ReplyDeleteHi there! Nice stuff, do keep me posted when you post again something like this! 먹튀검증업체
ReplyDeletenice post, keep up with this interesting work. It really is good to know that this topic is being covered also on this web site so cheers for taking time to discuss this! 먹튀검증
ReplyDeleteHi there! Nice stuff, do keep me posted when you post again something like this! 먹튀검증
ReplyDeleteExcellent article. Very interesting to read. I really love to read such a nice article. Thanks! keep rocking. 토토사이트
ReplyDeleteEfficiently written information. It will be profitable to anybody who utilizes it, counting me. Keep up the good work. For certain I will review out more posts day in and day out. 대전마사지
ReplyDeleteI read that Post and got it fine and informative. 바둑이게임I read that Post and got it fine and informative. 바둑이게임
ReplyDeleteI think this is an informative post and it is very beneficial and knowledgeable. Therefore, I would like to thank you for the endeavors that you have made in writing this article. All the content is absolutely well-researched. Thanks... 토토사이트
ReplyDeleteGreat site! I really love the way it is simple on my eyes it is. I am thinking about how I may be told at whatever point another post has been made. I have bought in to your RSS which may work? Have an incredible day! what is a ppm
ReplyDelete