Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations gkittelson on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

"LSN . . . is invalid" 1

Status
Not open for further replies.
Ralph8

Ralph8

IS-IT--Management
Mar 15, 2003
168
US
I detached a DB named SEM4 that was working well and substituted another test version which attached OK and my testing, etc. went well. I am using SQL Server 7 from VS 6 Enterprise.

Now I cannot re attach the original version. This is critical because it contains quite a bit that is not backed up. Shame on me!

All attempts to attach thru code in the program and thru the Query Analyzer trying both single and multi file attach give the same result: Error -2147212501 in Microsoft SQL-DMO[ODBC SQL State:HY000] Reason: [Microsoft][ODBC SQL Server driver][SQL Server] The LSN [795:169:1] passed to log scan in database 'SEM4' is invalid.

I do not believe the DB is corrupted as I have another copy of the same DB that was "saved" a day prior to this happening and it gives the same result when I try to attach it. (This has worked well in the past and I thought this to be a sufficient back up for my purposes).

I do not even know what the LSN is and I can't find documentation on it.

Can any one help? I am supposed to hit the road in the morning to customers who have been promised the results of what I am doing.

TIA

Ralph D. Meredith
 
Sort by date Sort by votes
Jelf

Thanks for the ref. I have registered with sqlservercentral and I have tried the procedure thru step 6.

My result for: select Name, Status from Sysdatabases where name = 'SEM4', was (0 row(s) affected) and the status field was blank. SEM4 is the database named in step 2.

Don't know where to go from here. Guess I will resume by browsing their FAQs.

TIA

Ralph D. Meredith
 
Upvote 0 Downvote
I have browsed all the FAQs I can find and I have tried the various "substitution" techniques for attaching that have worked in the past, even though this is different. Still coming back to the "LSN . . . invalid".

Any one have any other ideas?


TIA

Ralph D. Meredith
 
Upvote 0 Downvote
Have you tried attaching JUST the .mdf file and ignoring the log file(s)?

(sp_attach_single_file_db)

-SQLBill
 
Upvote 0 Downvote
Yes. I have tried sp_attach_single_file_db, sp_attach_db with both the .mdf and .ldf files and my program has some code in it that has executed attaches and detaches flawlessly. All of these give the same
"LSN . . invalid" message I quoted in my original post above.

TIA

Ralph D. Meredith
 
Upvote 0 Downvote
Ralph ...

How large is the DB. I beleive we can get this puppy online and get the info we need out of it. Are there many tables and such???

Thanks

J. Kusch
 
Upvote 0 Downvote
OK ... lets give this a try ...

We are going to "try" and trick SQL Server into attaching this DB. Worst case senerio, we will put it into Recovery Mode and push out as much data and DB objects from your downed DB to another Temp DB. Then you may be able to recreate the DB from there. So ... in say that ... ( I am going to use SEM4 as an example DB name!!!)

1. Rename your MDF and LDF of the troubled DB. Rename them to somthing like SEM4_BAD.MDF & SEM4_BAD.LDF

2. In Enterprise Manager, Create a new DB using the same name as the old DB (SEM4??). Make sure the MDF and LDF are being created in the exact same file location where the old/renamed DB is.

3. Stop SQL Server serverice.

4. DELETE the newly created SEM4.MDF and SEM4.LDF

5. RENAME ONLY the old MDF (SEM4_BAD.MDF) back to it's orginal name (SEM4.MDF).

6. Start SQL Server

7. Open Enterprise Manager - the database SEM4 DB should in "suspect" mode.

OK ... We have her attached ... Lets see what we can do. I have written a FAQ, faq962-4210, that you may now take a look at. It will relay to you a step-wise progression on how to try to get the DB out of Suspect-Mode and if it is unable to get it out of suspect mode, it will put it into Recovery Mode so that you may get as much data and DB objects out as possible.

Let us know how this goes and if you need any other assistance. Hope this helps some!!!

Thanks

J. Kusch
 
Upvote 0 Downvote
I have completed the seven steps itemized above. SEM4 is attached and "Suspect".

I am now on step 2 of the FAQ. All the archives are the same. There are only two things out of the ordinary that I can see:

Early on is a line that says "failed to get sqlservr image range". I'm wondering if that even applies to this situation?

Near the end it tries to open SEM4 and gives a similar message to what we were getting previously on the failed attach attempts:

"The LSN(795:169:1) passed to log scan in database 'SEM4' is invalid"

Error 9003 Severity 20 State:4

That is followed by:

"Database 'SEM4'(Database ID 7) couldn't recover"

"Error 3414 Severity 21 State:1"

I do not know what to do with this. The "current" version and the "saved" copies of SEM4 give the same indications. One of the "Saves" was used as a template to create the "Current" version and they were working well before I detached the "Current" and attached a test DB, which actually came from a customer for me to do some alpha testing on some new changes. (What a sentence!)

Could something have happened in SQL Server 7 rather than the SEM4 DB itself?






TIA

Ralph D. Meredith
 
Upvote 0 Downvote
Are you able to proceed and get the database in Recovery Mode??? Before doing this, I would stop SQL Server once again and copy, if possible, the corrupt MDF/LDF to another area for safe keeping. Once that is done, go thru the process of getting the DB attached in Suspect mode and see if the steps to get it in Recovery mode work.

Thanks

J. Kusch
 
Upvote 0 Downvote
I went thru the FAQ procedures, thru step 12 and each step appeared to work OK. The actual execution in step 12 was shorter, timewise, than I expected.

PROBLEM: The new DB, DevSEM4, is empty. No tables, no views, no SPs.

What could I have done wrong?

TIA

Ralph D. Meredith
 
Upvote 0 Downvote
Update:
I assume I failed to get the "Check" checked on the first run.

Then I hung up with "Objects don't exist in destination"
I checked the "Create destinations" box and went a lot further. It was actually transferring data when it hung up on "Invalid Column Name" for a couple of columns.

I am currently browsing listings of my tables to see if I can drop a table and make it work.

More later.

TIA

Ralph D. Meredith
 
Upvote 0 Downvote
It now appears that I have a pretty good looking DevSEM4 in spite of a couple of "Invalid column Names".

NOW, pardon me for being gun shy at this point, but what should I now do with SEM4[Emergency]? Delete it?

And can I then change the DevSEM4 to SEM4 for testing? It is in a separate folder.

I really appreciate all the help. PLEASE walk me thru this last step to prove that I am "there".

TIA

Ralph D. Meredith
 
Upvote 0 Downvote
I have the DevSEM4 DB files in a folder also named DevSEM4. The files are named SEM4.mdf and SEM4_log.ldf. Content of the tables looks pretty good. The .ldf seems much larger than normal.

The interesting thing now is that, if I rename the files in the DevSEM4 folder, all the databases for this server (Named 'Ralph') disappear.

I have deleted the SEM4[Emergency] DB with the idea of creating a new SEM4 DB that uses the files in the DevSEM4 folder. This required renaming those files during the New SEM4 creation process and that is when I discovered that all the DBs go away when these files are renamed. They re appear when the original names are restored.

The original files in MSSQL7 are still intact (Master, tempDB, a small DB of mine called SEMActivation, etc.).

I'm wondering if I can create a new SEM4 DB and copy files, views, etc. into it from the DevSEM4 DB.

Not sure how to deal with the situation described above.


TIA

Ralph D. Meredith
 
Upvote 0 Downvote
Correct ... delete it once you get everything out that you can.

Thanks

J. Kusch
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

CodeWell
  • Locked
  • Question
SQL Server 2017 Install Issue - Database Not in Configuration Manager
Replies
0
Views
399
CodeWell
CodeWell
intel233
  • Locked
  • Question
Backup/Restore DB from SQL issues
Replies
2
Views
157
intel233
intel233
TheBugSlayer
  • Locked
  • Question
Database Mail - Failed to notify "XYZ" via email.
Replies
5
Views
158
TheBugSlayer
TheBugSlayer
TheBugSlayer
  • Locked
  • Question
What causer the "[Microsoft][ODBC SQL Server Driver]Communication link failure" error?
Replies
2
Views
350
TheBugSlayer
TheBugSlayer
djj55
  • Locked
  • Question
Why is "ProgramName" .Net
Replies
2
Views
74
djj55
djj55

Part and Inventory Search

Sponsor

Back
Top