MSSQLSERVER service fails to start 2

[Diffy1]

Hi

The service fails with Event ID 17052:

Error: 9003, Severity: 20, State: 1
The LSN (4:316:1) passed to log scan in database 'model' is invalid.

Tried a repair on the software via Add/Remove but just brings up a fatal error. The version is reporting as 8.00.760.

The SQL engine is only serving one application so just woindering if a uninstall/re-install would do the trick. If so what version would version would I be looking at. SQL novice so any help would be much appreciated!!

 

[ptheriault]

It looks like something happend to the log file for you model database. Try this...
re-name your current model.mdf and model.ldf files.

Copy another model.mdf and model.ldf from another server and try to restart.

- Paul
- If at first you don't succeed, find out if the loser gets anything.

 

[Catadmin]

To follow Paul's advice, you'll have to remember 2 things.

1) You have to have SQL Server stopped and offline to do the renaming. If you don't, you might make the problem worse.

2) Try to get the model DB from a server that has the same setup as your current "problem" server. Such as a Dev copy or something.

However, I would really recommend, before trying the above technique, restoring the model DB from the backup you hopefully made (you did remember to backup your sysdbs on this server, right?) to a sandbox server and copying it over once you stop SQL Server.



Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"

 

[Diffy1]

Hi Thanks for the post!

1. Forgive my ignorance but the only log file available was modellog.ldf - presume that this is the file that Paul kindly mentioned. Stooped the MSSQL service and renamed the model.mdb and modellog.ldf and copied in these files from another SQL server (different path though) and the same problem occurs.

2. This has been dumped on my lap a bit so guess what - no backups!

3. Probably another stupid question here but I was looking to uninstall my version and install MS SQL server 2000 SP4 and once the server is running look at re-attaching the application db manually. Presume that this would be OK?

Many thanks for our help.

 

[Catadmin]

FYI: Don't know if you have it, but here's the link to see all the versions and what Service Pack they indicate:

http://www.sqlteam.com/item.asp?ItemID=8318

I don't recommend uninstalling and reinstalling unless you're having major problems. It looks like you are, though, so take SQL Server offline and copy all the database files, including MSDB and Master (just in case. But you don't need TempDB or Model since it's unfixable) to another server or network share. Then do your uninstall & reinstall, copy back the db files and reattach them. The MSDB will keep all your jobs & job history. Only redo the master if you absolutely have to because that's a pain to restore.

Set up a plan to backup Master, Model, MSDB at least once a month (I do mine every two weeks because we do make frequent job changes, etc).

You might want to have your server team do a full windows backup right before you do this, though, just as a CYA.

And, in case your customers don't know SQL is down, warn them that it'll be down for the day. It might not take you that long to get it fixed, but better to overestimate your window and finish early than to underestimate and run into problems.



Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"

 

[Diffy1]

OK will do - thanks for taking the time to post - will let you know how I get on!

 

[Diffy1]

As mentioned new to SQL so was wondering if anyone can kindly help with the basics of attaching an existing database. I have the syntax (and database locations) just need to know where you run the following command from:

sp_attach_db [ @dbname = ] 'dbname'
, [ @filename1 = ] 'filename_n' [ ,...16 ]

Thanks in advanced!

 

[ptheriault]

run it from Query Analyzer in SQL Server 2000.
OR SQL Server Management Studio in SQL Server 2005.

- Paul
- If at first you don't succeed, find out if the loser gets anything.