1

I can't figure out why this join isn't working to return the table name when querying the transaction log file.

SELECT [Transaction ID], 
    [Current LSN], 
    PartitionId, 
    sp.object_id,   
    so.[name], 
    [Operation],  
    [Context],
    [AllocUnitName],
    [Begin Time],
    [End Time],
    [Transaction SID],
    [Num Elements] ,
    [RowLog Contents 0],
    [RowLog Contents 1],
    [RowLog Contents 2],
    [RowLog Contents 3]
 FROM fn_dump_dblog(NULL,NULL,'DISK',1
    ,'D:\Users\Chad\SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Backup\OTIS_LogBackup_2016-02-02_13-36-21.bak'
    ,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL
    ,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL
    ,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL
    ,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL
    ,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL
    ,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL
    ,NULL,NULL,NULL,NULL) as logF
    LEFT OUTER JOIN sys.partitions sp ON sp.partition_id = logF.PartitionId 
    LEFT OUTER JOIN sys.objects so on so.object_id = sp.object_id
WHERE
    [Transaction ID] = '0000:00d15747';

And here are the results. You can see the partition Id is populated:

enter image description here

And when I execute this query to get the table name, it works using that partition id found in the prior results:

SELECT so.* 
FROM sys.objects so
INNER JOIN sys.partitions sp on so.object_id = sp.object_id
WHERE sp.partition_id = 72057594059489280

enter image description here

So why can't it find the table name when I join in the original statement above?

3
  • @ChadRichardson - thanks for accepting my answer - although we'd still be interested in knowing why you are doing this. There may be a simpler way depending on what you are really wanting. – Hannah Vernon Feb 2 '16 at 22:53
  • @Max Vernon I had an issue where a dB update was done by mistake and we found out about it within 10 minutes of it happening. As such I wanted to find the transaction in the log to get the exact date and time it occurred so we could restore to the second before as to not lose any more transactions than necessary. – crichavin Feb 3 '16 at 3:23
  • Ahhh... "oh, I'm in production!" – Hannah Vernon Feb 3 '16 at 5:02
2

You need to ensure you are running the query in the context of the correct database.

Since system views such as sys.objects are context sensitive, they only return rows related to the current database.

For instance, running this code in master would only show tables where the PartitionId exists in master, and those table names would likely be incorrect.

Assuming your database name is OTIS you could work around this by adding the database name into your join clause, such as JOIN OTIS.sys.partitions and JOIN OTIS.sys.objects.

Since you've hard-coded the name of the log file itself, something like this should work more reliably:

SELECT [Transaction ID], 
    [Current LSN], 
    PartitionId, 
    sp.object_id,   
    so.[name], 
    [Operation],  
    [Context],
    [AllocUnitName],
    [Begin Time],
    [End Time],
    [Transaction SID],
    [Num Elements] ,
    [RowLog Contents 0],
    [RowLog Contents 1],
    [RowLog Contents 2],
    [RowLog Contents 3]
 FROM fn_dump_dblog(NULL,NULL,'DISK',1
    ,'D:\Users\Chad\SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Backup\OTIS_LogBackup_2016-02-02_13-36-21.bak'
    ,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL
    ,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL
    ,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL
    ,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL
    ,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL
    ,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL
    ,NULL,NULL,NULL,NULL) as logF
    LEFT OUTER JOIN OTIS.sys.partitions sp ON sp.partition_id = logF.PartitionId 
    LEFT OUTER JOIN OTIS.sys.objects so on so.object_id = sp.object_id
WHERE
    [Transaction ID] = '0000:00d15747';

Alternately, you could simply place USE OTIS; prior to your select statement, so it executes first.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.