Solved

The LSN (70:729:1) passed to log scan in database 'bpl' is invalid

Posted on 2002-07-17
8
1,432 Views
Last Modified: 2008-03-17
I have a .dbf file and a .ldf file and I am trying to restore the database in some other system.

I am using the follwing syntax to attach the database

EXEC sp_attach_db @dbname = N'<bpl>',
   @filename1 = N'd:\newdatafiles\bpl_2000_data.mdf',
   @filename2 = N'd:\newdatafiles\bpl_2000_log.ldf'

When I am doing so I am getting the following error
---
Server: Msg 9003, Level 20, State 1, Line 1
The LSN (70:729:1) passed to log scan in database 'bpl' is invalid.
Connection Broken
---

When I attach only the .dbf file. The database is created and the working fine. But I am getting error while attaching the .ldf file. What might be the problem.
0
Comment
Question by:Ambience
8 Comments
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 7159060
I guess that either the database was not at all or not properly shut down when copying the files. Please enure that the source sql server was stopped when copying the files, or at least the database detached (sp_detach_db) from the server before starting the copy.
CHeers
0
 

Author Comment

by:Ambience
ID: 7159191
Angelllll
Ok fine. Thanks,
But can you tell me what is the possible reason for such kind of error.
regds
0
 
LVL 142

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 100 total points
ID: 7159212
if the .mdf and .ldf there are differences in the internal numbering of transactions:
* the data.mdf is copied to bck.mdf
* a transaction occurs (written to the data.mdf and the data.ldf)
* the data.ldf is copied to bck.ldf

Then the bck.mdf does not know about this new transaction which is in bck.ldf, and rejects to take this log file.

CHeers
0
 

Author Comment

by:Ambience
ID: 7159467
Angelllll
Ok fine. Thanks,
But can you tell me what is the possible reason for such kind of error.
regds
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 4

Expert Comment

by:TheSpirit
ID: 7160880
A way that may avoid this is to issue a checkpoint command prior to taking the copy of the files
0
 
LVL 9

Expert Comment

by:miron
ID: 7161291
sql server communicates with the rest of world using protective cushion of transaction log,
each sql server operation results in a transaction record being generated and logged into
transaction log. Each transaction is assigned unique LSN, sort of an index into array.
At every check point all transactions passed "commit" are written into data file with the
last transaction LSN ( offset id ) that is "in flight" being marked as last known transaction
in data file, as well as in Transaction file. Apparently for some reason LSN
marked as last known in Transaction File and Data File got out of synch.

What might have caused it is hard to guess. To avoid this condition use 'backup database' command.
Backup, if created successfully is guaranteed against similar mishaps.

Cheers

P. S.
--The LSN (70:729:1)
in your case was assigned to transaction log file while its offset id was not registered in the data file.
looking at the next couple of words in the error message
--passed to log scan in database 'bpl'
I read that during attach( ment ) database is actually going through a hidden "restore"
operation in somewhat reduced form, and one of the phases of this restore is re-scan of
existing transaction log file.
--is invalid.
means that the data file has no indication of such transaction existence
( remember, its LSN is not within known LSN(s) to data file )

what caused it - sadden electric current shutdown or hard drive capacity bottleneck,
or developer copying live database files using low level file access routines, hard to say.
What is apparent is a few transactions were lost with corrupted log file.

0
 

Author Comment

by:Ambience
ID: 7161311
Miron,
Thanks for the detailed description. It made me realize what exactly happens. I have taken a backup after detaching the database and everything worked fine. But your explanation was real great.
Thanks Alot
0
 

Author Comment

by:Ambience
ID: 7161317
Miron,
Thanks for the detailed description. It made me realize what exactly happens. I have taken a backup after detaching the database and everything worked fine. But your explanation was real great.
Thanks Alot
0

Featured Post

[Webinar] Disaster Recovery and Cloud Management

Learn from Unigma and CloudBerry industry veterans which providers are best for certain use cases and how to lower cloud costs, how to grow your Managed Services practice in IaaS clouds, and how to utilize public cloud for Disaster Recovery

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

863 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

24 Experts available now in Live!

Get 1:1 Help Now