Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
2  
I have the same issue running 2008R2 10.50.4000. –  Nic Jan 24 '13 at 0:23
3  
Unless you are doing it for educational purpose (it is undocumented), Paul has a bold caution - Jonathan just found out from a customer system that uses this extensively that every time fn_dump_dblog is called, it creates a new hidden SQLOS scheduler and up to three threads, which will not go away (and will not be reused) until a server restart. –  Kin Jun 16 at 21:38
1  
@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. –  Karthick Jul 19 at 11:08

1 Answer 1

You can try the steps as explained here. On the other hand, there is a SQL Server auditing tool ApexSQL Log that can help you in finding the relevant LSN via GUI and without T-SQL use

Disclaimer: I work as a Product Support Engineer at ApexSQL

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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