Ambience
asked on
The LSN (70:729:1) passed to log scan in database 'bpl' is invalid
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
@filename2 = N'd:\newdatafiles\bpl_2000
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.
I am using the follwing syntax to attach the database
EXEC sp_attach_db @dbname = N'<bpl>',
@filename1 = N'd:\newdatafiles\bpl_2000
@filename2 = N'd:\newdatafiles\bpl_2000
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.
ASKER
Angelllll
Ok fine. Thanks,
But can you tell me what is the possible reason for such kind of error.
regds
Ok fine. Thanks,
But can you tell me what is the possible reason for such kind of error.
regds
ASKER CERTIFIED SOLUTION
![Avatar of Guy Hengel [angelIII / a3]](https://filedb.experts-exchange.com/files/public/2013/11/25/ddbe0ea6-aa6e-4279-b781-57b735dd3ea0.jpeg){kind=small}
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Angelllll
Ok fine. Thanks,
But can you tell me what is the possible reason for such kind of error.
regds
Ok fine. Thanks,
But can you tell me what is the possible reason for such kind of error.
regds
A way that may avoid this is to issue a checkpoint command prior to taking the copy of the files
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.
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.
ASKER
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
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
ASKER
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
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
CHeers