SQL Service won''t start - corrupt model db?
-
Hi All,
I tried posting this message yesterday but it didn't appear on the forum so I am posting again.(Apologies if this is now duplicated).
I have a problem with one of our SQL servers running SQL 2000 sp3 on Windows 2000 Server sp3.
The SQL service will not start, or should I say it starts and then stops about 10 seconds later.
I tried starting the service from the command line in single user mode using sqlservr -c -m but this still does not start the service, I also get the following error:
2005-02-28 15:09:56.75 spid3 Skipping startup of clean database id 7
2005-02-28 15:09:56.75 spid3 Skipping startup of clean database id 8
2005-02-28 15:09:56.75 spid7 Starting up database 'msdb'.
2005-02-28 15:09:56.79 spid8 Starting up database 'pubs'.
2005-02-28 15:09:56.79 spid9 Starting up database 'Northwind'.
2005-02-28 15:09:56.95 spid5 Error: 9003, Severity: 20, State: 1
2005-02-28 15:09:56.95 spid5 The LSN (4:240:1) passed to log scan in database 'model' is invalid..
Top part of message trimmed as this only shows the usual startup info.
The problem I have that it appears that only the user database on the server has ever been backed up, the system databases have not been backed up.
Can anyone suggest what the problem/resolution is to this, the only reference to the error I get on Microsoft is related to sync problems between the data and log files and that should have been fixed by SP3 that this server is running.
Any ideas or suggestions?
Thanks
James
-
Hai,
When recovery occurs SQL has to scan through the log looking for these updates as well as committing new updates that might be occurring on other databases.
Additonally to check on recovery progress you could set trace flag 3412 which writes an entry to the errorlog when each transaction is rolled forward or back.
If a database will not recover and you do NOT have a backup then you can use the following trace flags to bypass recovery. If you use these then the database/data may not be in a consistent state, but if you have no other choice then use them and then immediately transfer out (using bcp or transfer tools) all the objects you require.
3607 Skips automatic recovery for all databases.
3608 Skips automatic recovery for all databases except the master database.
If the database is still unavailable - marked as suspect - then issue the following command to put the database into emergency mode (you'll need to allow updates first). You can then go into the database (no need to restart SQL) and extract out the data.
UPDATE master..sysdatabases SET status=-32768 WHERE name='<dbname>'
If all else fails, or you are unsure what to do, then don't hesitate to place a call with Microsoft Product Support Services (PSS). Ofcourse you need to give them the output of sqldiag
If you have solved them please update
Helen
--------------------------------
Are you a born again. He is Jehova Jirah unto me -
Hi Helen,
Thanks for your reply, the problem I have is that I cannot issue any kind of SQL statements to the database as I cannot get the SQL service to start.
The problem I have with the server is related to the model database which means that as the service starts it cannot create the tempdb as model provides the template for all new databases and is therefore required for the service to start.
I suppose what I want to know is if there is a way to recreate the model database on this server without having to uninstall/reinstall? All other databases on this server are fine so if I can replace the model database in some way I can get the service to start again and attempt to recover any required info from the old model database.
Hope this makes sense
Thanks
James
-
Hi James,
What I meant was try connecting from command prompt with the mentioned trace flag
Helen
--------------------------------
Are you a born again. He is Jehova Jirah unto me -
If you 're still using the default modeldb, you can copy another model mdf and ldf file from an other server ( dev ? ) to the datafilelocations of your server. First rename the current ones for later testing
I hope this helps out.
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
-
If you can start SQL Server in minimal mode from the command line (-f) then use the script that is on the install CD to recreate the model DB. Problem here is that model is being used to create tempdb as well.
-
I thought that for Windows 2000 you need Service Pack 4....
-
No you do not need WIN 2K SP4, SP3 works.
As for your dilemma, either copy model.mdf/ldf files from another server as mentioned above or rebuild model and you should be fine.
RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."
-
euhm ... just slipped into my attention ...
... keep in mind your model-mdf/ldf have to be with the same charset as the old one or you can have some nasty things coming ....
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
-
Thanks for all the advice.
Can someone tell me if it is possible to find out the character set that the SQL server was installed with if SQL isn't running then I can copy across a matching model database.
I didn't setup the server in the first place and don't normally do the admin for the machine so don't know how its setup.
Thanks
James
-
start SQL Server in minimal mode from the command line (-f)
use Query Analyser :
use master
go
exec sp_helpsort
go
didn't run it over here
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply