I am using sql server 2005 enterprise edition (x64) with sp1
Periodically, the sql server is crashing. Before crashing i am getting these messages in the error log
1) During undoing of a logged operation in database 'msdb', an error occurred at log record ID (). Typically, the specific failure is logged previously as an error in the Windows Event Log service. Restore the database or file from a backup, or repair the database.
2) Could not find an entry for table or index with partition ID xxxxxx in database 2. This error can occur if a stored procedure references a dropped table, or metadata is corrupted. Drop and re-create the stored procedure, or execute DBCC CHECKDB.
3) Unable to drop worktable with partition ID xxxxx after repeated attempts. Worktable is marked for deferred drop. This is an informational message only. No user action is required.
4) Error occurred while attempting to drop worktable with partition ID xxxxx.
5) SQL Server must shut down in order to recover a database (database ID 1). The database is either a user database that could not be shut down or a system database. Restart SQL Server. If the database fails to recover after another startup, repair or restore the database.
6)The log for database 'msdb' is not available. Check the event log for related error messages. Resolve any errors and restart the database.
Usually i get (2),(3),(4) (5) error message. But last time i also got (1) and (6)
Why msdb database is mentioned in error messages? Whats wrong with msdb dattabase?
Can someone tell me how to fix this issue?
I have seen this hotfix -
http://support.microsoft.com/kb/916086
( FIX: Errors may be generated in the tempdb database when you create and then drop many temporary tables in SQL Server 2005 BUG #: 518 sql )
Should i apply this hotfix? or should apply SP2? I checked SP2 bug list but i dont see anywhere about above bug. Does that mean it is not included in SP2?
Has anyone experienced such an issue? How you have resolved it?
Please any help would be apprecited.
Thanks
Database id:1 is master, :2 is tempdb. msdb database id should be 4.
It's a good thing to apply that hotfix. Also, please post the sql log. It gives us a lot more info than windows event log.
|||Thanks so much for the reply.
All the above errors are from sql log only. I looked at the event log and it shows the same error messages.
Also that hotfix says-
This hotfix may receive additional testing. Therefore, if you are not severely affected by this problem, we recommend that you wait for the next SQL Server 2005 service pack that contains this hotfix.
So is it safe to apply that hotfix? and if not does sp2 includes this hotfix bug?
Also wondering why the error message showing msdb database?
Thanks
|||
All hotfixes have that disclaimer. Basically, hotfixes are released to customers who truly need the fix before a formal release of a service pack (which they could wait for a long time for).
Sql2k5 sp2 does have this fix included. You can tell by looking at the build number (hotfix: 1533 and sp2: 3042).
The "missing log for msdb" error implies your sqlserver can't get to the log file (*.ldf) which implies a storage problem. Please check system event log for any indication of hardware failure.
sql