Get Table Name from Transaction Log query

Question

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:

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

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

Answer 1

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.