Finding a Transaction in the log for a particular user

As a DBA, this is a frequently asked question from the developers “When was a certain insert/update/delete performed on a specific table?” Or, It is simply required to know the time of the particular transaction so that it can be rolled back or restored to a state prior to the transaction. Now there are 2 ways to do this, first is rather simple, if the relevant log is still available, meaning that the log has not been flushed. The other is a bit time taking scenario when the available logs do not hold such information. Today I will deal with the former scenario and will talk about the latter in upcoming blogpost.

For the first scenario, this can be done using the fn_dblog, and needs two pieces of information, the user and the rough time that the transaction occurred. The user can’t be used to search in the log directly, but every LOP_BEGIN_XACT  log record contains the SID of who ran the transaction. The SID can be obtained from the username using the SUSER_SID function.

For instance, on my test SQL Server instance:

SELECT SUSER_SID (‘SQL2012\SKARKI’) AS [SID];

GO

SID
-----------------------------------------------------------------
0x0105000000000005150000008E888D4129BB677CAA278B76E8030000
Now that we have our SID, we can use it as a filter for fn_dblog, like so:
SELECT
    [Current LSN],
    [Operation],
    [Transaction ID],
    [Begin Time],
    LEFT([Description],40) ;AS[Description]
FROM
    fn_dblog(NULL,NULL)
WHERE
    [Transaction SID]=SUSER_SID('SQL2012\SKARKI');
GO
snap1
Obviously the transactions above are a sample. You can imagine the case of lots of transactions spread out over a few hours or even over a day and to narrow it down to the transaction you want, you could look through the list manually for the rough time or specify a time range on the SELECT using predicates on the Begin Time column in the fn_dblog output as shown below:
SELECT
    [Current LSN],
    [Operation],
    [Transaction ID],
    [Begin Time],
    LEFT([Description],40)AS[Description]
FROM
    fn_dblog(NULL,NULL)
WHERE
    [Transaction SID]=SUSER_SID('SQL2012\SKARKI')
AND([Begin Time]>'2015/06/03 11:18:15'AND[Begin Time]<'2015/06/03 11:18:25');
GO
snap2
And if you knew what the operation was, you could narrow it down by the Description too. Now that you know the exact time frame for the transaction that occurred, you can easily restore the tables/objects to the point in time before the changes occurred. I shall deal about how to restore the objects to the point in time from available backups in the next blog post and I will also talk about another function that will help us track the time frame of particular transactions that occurred in the database when we don’t have the needed logs with us.
Feel free to comment if you think this post was helpful or if you think this post needs some updates or changes from your end.
Advertisements
This entry was posted in ms sql, Troubleshooting, Uncategorized and tagged , , , , , , , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s