June 23, 2008 at 2:07 pm
I have seen several entries for this error but they have been contained to non-system databases. I discovered today that one of our servers is throwing this error on the DBCC CheckDB of the MSDB database. It looks like the server was configured as a SQLSERVER 2005 and then SQL SERVER 2000 databases were restored to it and upgraded to 2005. At that point in time the MSDB database began to throw this error and there is no backup for it. How do I fix the MSDB database so that this error stops?
Thanks!
June 24, 2008 at 12:36 am
Could you post the entire error message please? The title gets cut short.
No backup? Why not?
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
June 24, 2008 at 6:45 am
SQL SERVER LOG:
The entire error is as follows:
Date6/24/2008 8:30:12 AM
LogSQL Server (Current - 6/24/2008 8:30:00 AM)
Sourcespid60
Message
SQL Server detected a logical consistency-based I/O error: torn page (expected signature: 0x0; actual signature: 0x9dc8cfec). It occurred during a read of page (1:152) in database ID 4 at offset 0x00000000130000 in file 'D:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\MSDBData.mdf'. Additional messages in the SQL Server error log or system event log may provide more detail. This is a severe error condition that threatens database integrity and must be corrected immediately. Complete a full database consistency check (DBCC CHECKDB). This error can be caused by many factors; for more information, see SQL Server Books Online.
ERROR LOG:
Date6/24/2008 8:30:12 AM
LogSQL Agent (Current - 6/24/2008 8:30:00 AM)
Message
[298] SQLServer Error: 824, SQL Server detected a logical consistency-based I/O error: torn page (expected signature: 0x0; actual signature: 0x9dc8cfec). It occurred during a read of page (1:152) in database ID 4 at offset 0x00000000130000 in file 'D:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\MSDBData.mdf'. Additional messages in the SQL Server error log or system event log may provide more detail. This is a severe error condition that threatens database integrity and must be corrected immediately. Complete a full database consistency check (DBCC CHECKDB). This error can be caused by many factors; for more information, see SQL Server Books Online. [SQLSTATE HY000] (ConnExecuteCachableOp)
--Why no backup?
The DBA that set this box up is no longer with the company and the documentation does not point to why there is no backup. When the server was originally set up the msdb database immediately started to give this error so my guess is they never could get a clean backup. I will add that there is nothing in the msdb database that is important for us on this box as currently there are no alerts and no jobs running on this box.
Thank you
June 24, 2008 at 7:00 am
Can you run a checkDB on it and post the errors you get?
DBCC CHECKDB ('MSDB') WITH NO_Infomsgs
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
June 24, 2008 at 7:35 am
When I run the command above I get the following:
Msg 8921, Level 16, State 1, Line 1
Check terminated. A failure was detected while collecting facts. Possibly tempdb out of space or a system table is inconsistent. Check previous errors.
Msg 824, Level 24, State 2, Line 1
SQL Server detected a logical consistency-based I/O error: torn page (expected signature: 0x0; actual signature: 0x9dc8cfec). It occurred during a read of page (1:152) in database ID 9 at offset 0x00000000130000 in file 'D:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\MSDBData.mdf:MSSQL_DBCC9'. Additional messages in the SQL Server error log or system event log may provide more detail. This is a severe error condition that threatens database integrity and must be corrected immediately. Complete a full database consistency check (DBCC CHECKDB). This error can be caused by many factors; for more information, see SQL Server Books Online.
I did verify that the tempdb database is not out of space.
June 24, 2008 at 7:38 am
Ah. I thought that might be the case. The corruption's in the system tables in MSDB and checkDB can't repair corruption in the system tables.
I know there's a way to rebuild MSDB from the installation media, I don't recall offhand how to do it though.
If no one else has answered by tomorrow morning (my time) I'll see if I can dig up the way to go about 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
June 24, 2008 at 8:00 am
Rebuild MSDB, but does that also delete your jobs and any DTS package information/Alerts and Mail information stored in the MSDB?
Check to see if you have enough Space in the Drive where your MSDB and TEMPDB are located.
can you run DBCC CHECKALLOC (msdb)
and also DBCC updateusage (msdb)
then run DBCC CHECKDB (msdb)
What is the compatibility level for MSDB DB(rather all system databases)
You can restore a copy of your MSDB backup if available.
Which service Pack do you have on your SQL Server.
you should backup your system and User Databases as a rule of thumb.
When you joined in the Previous DBA place, you should have gone through the DBA Activities and check your Maintenance Plans first thing....
Maninder
www.dbanation.com
June 24, 2008 at 11:35 am
I did run each of the CHECKDB commands that you listed above and consistent for all is the corrupt file message.
Below is the specific message for Checkalloc. The results for the other commands are in earlier posts.
I don't have a backup because there never was a clean backup made of this server when it was created. I absolutely agree that backups should be made on servers and all databases and that is now true for all of our servers. This is the one outstanding issue that I still have since taking over the DBA tasks.
I believe, based on earlier posts, that I need a method to rebuild my msdb database. Is it possible to use the backup from another server since I don't have anything in the corrupt msdb database. I have another server with an msdb database with no jobs in it and my standard alerts?
SQL SERVER 2005 Service Pack 2
Thanks for the help!
DBCC results for 'msdb'.
Msg 8921, Level 16, State 1, Line 1
Check terminated. A failure was detected while collecting facts. Possibly tempdb out of space or a system table is inconsistent. Check previous errors.
CHECKALLOC found 0 allocation errors and 0 consistency errors in database 'msdb'.
Msg 824, Level 24, State 2, Line 1
SQL Server detected a logical consistency-based I/O error: torn page (expected signature: 0x0; actual signature: 0x9dc8cfec). It occurred during a read of page (1:152) in database ID 9 at offset 0x00000000130000 in file 'D:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\MSDBData.mdf:MSSQL_DBCC9'. Additional messages in the SQL Server error log or system event log may provide more detail. This is a severe error condition that threatens database integrity and must be corrected immediately. Complete a full database consistency check (DBCC CHECKDB). This error can be caused by many factors; for more information, see SQL Server Books Online.
June 25, 2008 at 1:57 am
Here's a step-by-step guide to recreating the MSDB database from the installation media.
http://blogs.msdn.com/sqlserverstorageengine/archive/2006/06/06/619304.aspx
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
June 25, 2008 at 8:26 am
This step by step guide worked perfect for me.
I have a working copy of MSDB and a good backup!
Thanks for all the help my problem is now solved!
June 25, 2008 at 11:17 am
Glad to help.
Now plase make sure that all of your databases, including the system ones (master, model, msdb) get regularly backed up.
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 1, 2008 at 7:43 am
did you only fix the msdb database or were you able to find out the cause of the corruption ?
else you may run into it again ... i have my doubts the cause is this:
It looks like the server was configured as a SQLSERVER 2005 and then SQL SERVER 2000 databases were restored to it and upgraded to 2005.
if it happens again after, for example, an power failure then you may have some raidcontroller without battery for example ...
somehow sql server was interrupted while writing the page for that msdb database ...
also if you have sql 2005 i would immediatly use crc checking. i didn't have corruptions in sql 2005 yet so not much experience with that box yet. so i dont know if the crc check comes first or the torn page check ...
March 25, 2009 at 9:52 am
Actually, you CAN repair MSDB in SQL 2005. I have had two tables in MSDB become corrupted in the past 3 weeks (I am struggling to find a root cause). But, I was able to repair MSDB without causing any downtime or production issue for other databases with the following:
USE [master]
GO
ALTER DATABASE [msdb] SET RESTRICTED_USER WITH ROLLBACK IMMEDIATE
GO
ALTER DATABASE [msdb] SET RESTRICTED_USER
GO
DBCC CHECKDB (msdb, REPAIR_ALLOW_DATA_LOSS)
GO
ALTER DATABASE [msdb] SET MULTI_USER
This way, you will not lose all your job history and SSIS packages that are stored in MSDB (although, I have not rebuilt the database from the installation media before, so I don't know much about that process).
Now, my issues were with dbo.backupfilegroup first, then dbo.backupmediafamily today. I am going to continue to search for root-cause.
UPDATED: I have updated this post. After learning a lot more about what SINGLE_USER really does, and having been locked out of a database once before, I now use RESTRICTED_USER instead. This way, only a person with sysadmin access can actually interact with the database, instead of the first person that manages to get connected.
ALSO, it is probably a lot better to restart the instance into the master-only "emergency mode", and restore your copy of MSDB from a known good backup, rather than doing the above fix. If you are extremely confident about your ability to recover specific data, then you can test this in a development environment.
March 25, 2009 at 9:59 am
Just be very careful when repairing msdb - and make sure you know what repair deleted (remember that repair is basically "delete what's broken and fix up all the links"). For instance, if you have a script that will generate your restore statements in the event of disaster recovery, if repair deletes some records from one of the backup history tables, it may break your script and your restores won't work properly. (Uh-oh - I feel a blog post coming on)
Check your I/O subsystem - what corruptions are you seeing? Can you post the output from
DBCC CHECKDB (msdb) WITH ALL_ERRORMSGS, NO_INFOMSGS
when you next get corruption.
Paul Randal
CEO, SQLskills.com: Check out SQLskills online training!
Blog:www.SQLskills.com/blogs/paul Twitter: @PaulRandal
SQL MVP, Microsoft RD, Contributing Editor of TechNet Magazine
Author of DBCC CHECKDB/repair (and other Storage Engine) code of SQL Server 2005
March 25, 2009 at 10:13 am
See Is running repair on msdb safe?
Paul Randal
CEO, SQLskills.com: Check out SQLskills online training!
Blog:www.SQLskills.com/blogs/paul Twitter: @PaulRandal
SQL MVP, Microsoft RD, Contributing Editor of TechNet Magazine
Author of DBCC CHECKDB/repair (and other Storage Engine) code of SQL Server 2005
Viewing 15 posts - 1 through 15 (of 26 total)
You must be logged in to reply to this topic. Login to reply