July 25, 2011 at 7:56 am
Hi,
This is a SQL Server 2005 instance running on Windows 2003. It seems that yesterday there was a server crash that caused a reboot. After this reboot the sql server is not longer available. Trying to start the service I see this error logged.
2011-07-25 08:53:35.82 spid9s Error: 5172, Severity: 16, State: 15.
2011-07-25 08:53:35.82 spid9s The header for file 'D:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\modellog.ldf' is not a valid database file header. The PageAudit property is incorrect.
2011-07-25 08:53:35.82 spid9s File activation failure. The physical file name "D:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\modellog.ldf" may be incorrect.
2011-07-25 08:53:35.82 spid9s The log cannot be rebuilt because the database was not cleanly shut down.
2011-07-25 08:53:35.82 spid9s Error: 945, Severity: 14, State: 2.
2011-07-25 08:53:35.82 spid9s Database 'model' cannot be opened due to inaccessible files or insufficient memory or disk space. See the SQL Server errorlog for details.
2011-07-25 08:53:35.82 spid9s Could not create tempdb. You may not have enough disk space available. Free additional disk space by deleting other files on the tempdb drive and then restart SQL Server. Check for additional errors in the event log that may indicate why the tempdb files could not be initialized.
2011-07-25 08:53:35.82 spid9s SQL Trace was stopped due to server shutdown. Trace ID = '1'. This is an informational message only; no user action is required.
Disks are full of space so this is not the problem.
Can you help me to troubleshoot this?
Thanks!
July 25, 2011 at 8:04 am
Your model database has become corrupt. You'll need to start SQL with a traceflag (can't recall offhand which one, I'll look it up for you) so that SQL only brings master online, then you'll need to restore a backup of model.
Also check for hardware-related errors, this didn't just randomly happen, something in the hardware (probably IO subsystem) caused it.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 25, 2011 at 8:35 am
Thanks a lot GilaMonster. I'll be checking on this
July 25, 2011 at 9:54 am
TRaceflag 3608
Start the SQL Server from the command line in single user, restricted more and with that traceflag
SQLServr.exe -m -f -T3608
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 25, 2011 at 3:18 pm
GilaMonster (7/25/2011)
TRaceflag 3608Start the SQL Server from the command line in single user, restricted more and with that traceflag
SQLServr.exe -m -f -T3608
I don't believe that will work Gail. Only the master database can be restored in single user mode.
As its the model database which is a prerequisite for SQL to actually be able to start we are into a rebuild of master here (and hence model and msdb as well), and then restores of the system databases.
Alternatively (and preferably in my opinion) is to copy the model database files over from another instance at the same version, or do what I do and slide into place the flat file copies of the relevant system database files I always keep on the server (and backed up to tape)
---------------------------------------------------------------------
July 25, 2011 at 4:35 pm
george sibbald (7/25/2011)
GilaMonster (7/25/2011)
TRaceflag 3608Start the SQL Server from the command line in single user, restricted more and with that traceflag
SQLServr.exe -m -f -T3608
I don't believe that will work Gail. Only the master database can be restored in single user mode.
As its the model database which is a prerequisite for SQL to actually be able to start we are into a rebuild of master here (and hence model and msdb as well), and then restores of the system databases.
Interestingly enough, you're right, but for the wrong reasons...
Those options allow SQL to start, even if the model database is completely toast. I did try
2011-07-26 00:52:40.91 Server Warning: The server instance was started using minimal configuration startup option (-f). Starting an instance of SQL Server with minimal configuration places the server in single-user mode automatically. After the server has been started with minimal configuration, you should change the appropriate server option value or values, stop, and then restart the server.
2011-07-26 00:52:40.97 spid7s Warning ******************
2011-07-26 00:52:40.97 spid7s SQL Server started in single-user mode. This an informational message only. No user action is required.
2011-07-26 00:52:40.97 spid7s Recovering only master database because traceflag 3608 was specified. This is an informational message only. No user action is required.
2011-07-26 00:52:40.98 spid7s Starting up database 'master'.
2011-07-26 00:52:41.38 Server SQL Server is now ready for client connections. This is an informational message; no user action is required.
You can connect and run simple queries.
However restoring a backup needs TempDB (for whatever reason) and starting that up requires starting model, which fails.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 25, 2011 at 4:38 pm
Two options then:
1) rebuild master (it's an option on the installer), then restore the backup of master and msdb and, if you had any custom stuff, model.
2) Get a copy of model.mdf and model.ldf (both, not just one) from another instance of SQL that is EXACTLY the same version and patch level and replace the damaged model.mdf and model.ldf with those files. Then you can restore a backup of model if you had any custom stuff in there.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 26, 2011 at 8:11 am
GilaMonster (7/25/2011)
george sibbald (7/25/2011)
GilaMonster (7/25/2011)
TRaceflag 3608Start the SQL Server from the command line in single user, restricted more and with that traceflag
SQLServr.exe -m -f -T3608
I don't believe that will work Gail. Only the master database can be restored in single user mode.
As its the model database which is a prerequisite for SQL to actually be able to start we are into a rebuild of master here (and hence model and msdb as well), and then restores of the system databases.
Interestingly enough, you're right, but for the wrong reasons...
Those options allow SQL to start, even if the model database is completely toast. I did try
[
no, I was right for the right reasons...:-)
I'm just a lazy typer late at night and need to clarify.
without the model database, SQL will not FULLY start to a point where the model database or any other database except master can be restored. You can only start SQL at all in master recovery only mode with flag -T3608, which is still not good enough to be able to restore model, just run queries that act in master such as detach\attach and alter database modify file.
Therefore we have a catch 22 - you need to start SQL to restore the model database, but without the model database you cannot start SQL (enough).
This is something I don't think BOL makes clear. So we are left with the rebuildmaster or another good reason to keep flat file copies of your system database files.
As Eric Morecambe once said - I'm playing all the right notes, just not necessarily in the right order.
---------------------------------------------------------------------
July 26, 2011 at 8:25 am
Ok, I thought you were saying the server wouldn't even start.
george sibbald (7/26/2011)
without the model database, SQL will not FULLY start to a point where the model database or any other database except master can be restored. You can only start SQL at all in master recovery only mode with flag -T3608, which is still not good enough to be able to restore model, just run queries that act in master such as detach\attach and alter database modify file.
I suspect it would work if restore didn't need TempDB for some reason (still trying to figure out why). The restore triggers a clear of TempDB, which starts model, which shuts the server down. 🙁
It may be by design, it may be an oversight, no idea which at the moment.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 26, 2011 at 8:57 am
Interesting. I have certainly never come across any technical information that alludes to this behaviour. To my mind the documentation leads you to think the restore of model would be possible.`
---------------------------------------------------------------------
July 26, 2011 at 8:59 am
george sibbald (7/26/2011)
To my mind the documentation leads you to think the restore of model would be possible.`
Exactly why I thought it would work...
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply