Error 9003 on SQL Server 2000

Hi guys,

I am having a little trouble with a SQL Server 2000 database and I would like some help. After failure and recovery of a hard disk my database is marked as suspect and I cannot get it to work again. I have tryed a few things such as copying the database and the log files to another computer and then running the attach command which returns the following "Error 9003: The LSN(12:32:1) passed to log scan in database 'dbName' is invalid.".

Upon getting this error I have tryed with failure attaching a single file, I have also tryed to create a new database with the same name in the same location as the original, then replacing the new files with my old ones, restarting the SQL Server and getting the database into Emergency Mode hopping to extract the data through DTS or BCP Utility, but I cannot access the objects nor the data.

I have also tryed to create a new database with the same name and replace only the ".mdf" file with my old one, but failed.

I would be gratefull for any help.

Manuel.pereira@.ricmade.ptit looks quite nasty, I wonder if it's possible to survive severe drive failure without performing full restore of db. But couple of thoughts: How many log files were originally in your db?, all other databases (system) are ok after drive recovery? By setting your db into emergency mode, you mean setting status column master..sysdatabases to -32768? Didn't work? After drive recovery, what were error messages in SQL Server log after startup concerning db in question? mojza|||Hi Mojza,

I only have one log file on my database, unfortunately I have another 2 databases with the same error. When I say that I set the database into emergency mode I mean setting the status column of master.sysdatabases to -32768, but this did not work, so I suspect that the .mdf file is damaged.

After I started up SQL I did get the following error messages.

Starting up database 'rjp'.
Error: 9003, Severity: 20, State: 6
The LSN (18611:16:1) passed to log scan in database 'rjp' is invalid.|||According to MS documentation, 9003 error should mean that your mdf and ldf files are out of syn. And the solution is usually attaching single file using sp_attach_db or sp_attach_single_file_db Sps to let SQL Server create a new log file for you. But you've said it didn't work. If it's not possible to put db into emergency mode, I guess there's corruption somewhere in data file header (just my opinion) and you have only two options left: go back to your last good backups or try to contact MS Support with uncertain results.
mojza

just for the sake of completness if you run dbcc dbrecover ('your_db')
command, the result is probably the same as during server startup?|||I get the same error message when I run the command dbcc dbrecover. Also when I try to attach a single file mdf, I get the error message 1813: Could not open new database 'db_name'. Create Database is Aborted. Device activation error, the physical file name 'log path' may be incorrect.

In general you are able to execute this comand and a log file will be created for you, but not with this database.|||The net is that one should not rely on SQL Server providing a silver bullet to recover from hard disk failures. It is a risky proposition.

Instead, plan for the failures by implementing RAID, Backups, Clusters, Log Shipping, or any other tools you are provided.

Expect and be prepared for failures. Understand your options, propose at least three options to the business unit, and let them decide. DO NOT MAKE THAT CHOICE FOR THEM or you will be chasing your tail for a long, long time.

In a land and time far, far away, where I claim client-server was invented, we had business users giving us servers for which we were accountable for providing health and welfare. Before the next budget cycle, I proposed to our GM, who got it approved by The Man, to have the business units budget for MB of data vs. hardware - we buy the hardware and we'll ensure recovery based on the following:
The cocktail weenies option was a database for which you can afford to lose a day's worth of data and accept a day of downtime - it cost $1 per MB.
To get no more than an hour loss of data and to be up w/in 4 hours of failure, that costs $2 per MB and was called the shrimp option.
And the caviar option was no more than 5 minutes data loss and you're up and running w/in 20 minutes. That'll be $10 per MB, thank you very much.

I let them choose, they'd transfer their budgets, and everyone lived happily ever after. For example, the DBAs on my team who had to go through the trouble of spending weekends ensuring the caviar option worked would receive a weekend, all expenses paid vacation, for them and their significant other following the harrowing experience. We spent the cavair cash in a way that made our users happier.

This also gave us the opportunity to consolidate our servers by about 50%. So, we bought less hardware and had happier customers.

Many stories to tell, some of them true, like running our most intense application on Beta NT and Beta SQL Server (their ship dates were synchronized) and delivering for our caviar clients. We hid beer under the floor tiles in the data center as a means to get us through some of our worse nights. Nothing like doing a Delete after sipping on a few Red Hooks.

The End.|||I'll assume that .MDF files are OK, so if that's the case, - there are 3 ways that accomplish the same thing (BTW, it does not matter how many LDF files there were originally, sp_attach_single_file_db will create only 1 new LDF file). For all three you need to delete or rename the original LDF files before you proceed (I recommend to move them to a safe location if none of the three methods will work). All three methods require the database to be in suspect mode, so don't mix the steps, and if you already changed the status, - make sure you pick up from there:

1. sp_configure 'allow', 1
reconfigure with override
go
update master.dbo.sysdatabases set status = status | 32768 where name = '<your_db>'
go
dbcc rebuild_log('<your_db>','c:\<your_db_log>.ldf')
go
alter database <your_db> set multi_user
go
sp_configure 'allow', 0
reconfigure with override
go

2. dbcc detachdb(<your_db>)
go
create database [<your_db>] on (filename='c:\<your_db>.mdf') for attach
go
if exists (select 1 from master.dbo.sysobjects where name = 'sp_removedbreplication') exec sp_removedbreplication '<your_db>'
go

3. ...Well, after all this - the third is the lazy man approach. SQL is actually trying to be nice to you, even if you put it to run on something that you shouldn't have ;) In short, after you renamed/deleted/moved the log devices while the databases were showing to be in Suspect mode, - just restart the service, and enjoy the show :D|||Do you think it's possible that .mdf file is ok and attach db or attach single file doesn't work? Have you come across this situation? I just try to collect experience from more knowledgable ones. mojza|||I'll assume that .MDF files are OK, so if that's the case, - there are 3 ways that accomplish the same thing (BTW, it does not matter how many LDF files there were originally, sp_attach_single_file_db will create only 1 new LDF file). For all three you need to delete or rename the original LDF files before you proceed (I recommend to move them to a safe location if none of the three methods will work). All three methods require the database to be in suspect mode, so don't mix the steps, and if you already changed the status, - make sure you pick up from there:

1. sp_configure 'allow', 1
reconfigure with override
go
update master.dbo.sysdatabases set status = status | 32768 where name = '<your_db>'
go
dbcc rebuild_log('<your_db>','c:\<your_db_log>.ldf')
go
alter database <your_db> set multi_user
go
sp_configure 'allow', 0
reconfigure with override
go

2. dbcc detachdb(<your_db>)
go
create database [<your_db>] on (filename='c:\<your_db>.mdf') for attach
go
if exists (select 1 from master.dbo.sysobjects where name = 'sp_removedbreplication') exec sp_removedbreplication '<your_db>'
go

3. ...Well, after all this - the third is the lazy man approach. SQL is actually trying to be nice to you, even if you put it to run on something that you shouldn't have ;) In short, after you renamed/deleted/moved the log devices while the databases were showing to be in Suspect mode, - just restart the service, and enjoy the show :D
I have tryed all three methods but it still does not work. I get the following error when I run the command dbcc rebuild_log

Server: Msg 3624, Level 20, State 1, Line 1

Location: filemgr.cpp:1890
Expression: fcb->GetSize () < fileSize
SPID: 52
Process ID: 3472

Connection Broken

I believe that the mdf file is damaged and that is why I cannot attach it as a single file. Do you know if there is any tool to export the data from the mdf file without being attached to SQL?