Sql-server – Why can’t I read the transaction log backup file using fn_dump_dblog

restoresql serversql-server-2008sql-server-2008-r2transaction-log

I'm using this blog on sqlskills.com as a guide to practice finding the relevant LSN in a transaction log backup for point in time recovery.

After backing up the transaction log, I attempt to read it with:

SELECT COUNT(*) FROM fn_dump_dblog (
NULL, NULL, 'DISK', 1, 'D:\TEMP\test_tlog2.bak',
DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT);

I get the following error:

Msg 615, Level 21, State 1, Line 1
Could not find database ID 0, name '0'. The database may be offline. Wait a few minutes and try again.

The database id isn't 0, it isn't offline, I don't see any params I'm missing or changes to the function. I can query the database and I can view the tlog backup file from within SSMS backup using the contents option, shows correct media set 1.

Microsoft SQL Server 2008 R2 (SP2) – 10.50.4263.0 (X64) Aug 23 2012 15:56:56 Copyright (c) Microsoft Corporation Enterprise Edition (64-bit) on Windows NT 6.1 (Build 7601: Service Pack 1) (Hypervisor)

Am I missing something fundamental?

Best Answer

@Alan Please check once by disabling the audit.

That server has an AUDIT on a database. If the audit is enabled, you will get that error. If you disable the audit,query will run fine.

Related Question