March 30, 2010 at 7:25 am
Hello All,
I am having below error when I run a checkdb on MSDB database.
Msg 824, Level 24, State 2, Line 1
SQL Server detected a logical consistency-based I/O error: incorrect pageid (expected 1:31; actual 1:63). It occurred during a read of page (1:31) in database ID 21 at offset 0x0000000003e000 in file 'D:\MSSQL.1\MSSQL\DATA\MSDBData.mdf:MSSQL_DBCC21'. 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 have ran:
DBCC CHECKDB ('msdb') WITH NO_INFOMSGS, ALL_ERRORMSGS;
GO
Also tried using the last option checkdb with allow_data_loss but the same error !
Is the last backup my option ?
Is there a way to find if the last backup is also not corrupted ?
We also ran a health-check Hardware Drive Test/ disk check on server but everytihing looks fine :hehe:
Thanks,
\\K
______________________________________________________________________________________________________________________________________________________________________________________
HTH !
Kin
MCTS : 2005, 2008
Active SQL Server Community Contributor 🙂
March 30, 2010 at 7:47 am
Hi,
May be you can restore the MSDB from last backup with a different name on to any Test instance and run CHECKDB to make sure it does not have any consistency errors.
[font="Verdana"]Renuka__[/font]
March 30, 2010 at 8:53 am
Interesting... The error is in the snapshot that checkDB creates and uses...
I've referred this to a corruption expert for assistance. It may be necessary to restore from your last good backup, though I'd wait for a second opinion before going ahead.
Renuka's idea for checking the backup is a good one. You can restore it as a user database on a test server and check 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
March 30, 2010 at 9:33 am
GilaMonster (3/30/2010)
Interesting... The error is in the snapshot that checkDB creates and uses...I've referred this to a corruption expert for assistance. It may be necessary to restore from your last good backup, though I'd wait for a second opinion before going ahead.
Renuka's idea for checking the backup is a good one. You can restore it as a user database on a test server and check there.
Thanks Renuka and Gail !
Gail, I will wait for your update on the post.
Thanks much,
\\K
______________________________________________________________________________________________________________________________________________________________________________________
HTH !
Kin
MCTS : 2005, 2008
Active SQL Server Community Contributor 🙂
March 30, 2010 at 10:34 am
Consistency error on the snapshot file itself doesn't in any way mean the actual database itself has corruptions - but it does mean the I/O subsystem that msdb is on has corruption issues (as the hidden snapshot that CHECKDB uses is created in the same location as the database itself).
Try running CHECKDB using WITH TABLOCK so it doesn't create a snapshot.
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 30, 2010 at 10:53 am
Paul Randal (3/30/2010)
Consistency error on the snapshot file itself doesn't in any way mean the actual database itself has corruptions - but it does mean the I/O subsystem that msdb is on has corruption issues (as the hidden snapshot that CHECKDB uses is created in the same location as the database itself).Try running CHECKDB using WITH TABLOCK so it doesn't create a snapshot.
Thanks Paul !
But but it does mean the I/O subsystem that msdb is on has corruption issues
We already did a hardware diagnostic check and there are no issues reported.
Thanks,
\\K
______________________________________________________________________________________________________________________________________________________________________________________
HTH !
Kin
MCTS : 2005, 2008
Active SQL Server Community Contributor 🙂
March 30, 2010 at 10:53 am
Paul Randal (3/30/2010)
Consistency error on the snapshot file itself doesn't in any way mean the actual database itself has corruptions - but it does mean the I/O subsystem that msdb is on has corruption issues (as the hidden snapshot that CHECKDB uses is created in the same location as the database itself).Try running CHECKDB using WITH TABLOCK so it doesn't create a snapshot.
Thanks Paul !
But but it does mean the I/O subsystem that msdb is on has corruption issues
We already did a hardware diagnostic check and there are no issues reported.
Thanks,
\\K
PS: When I post back, it is posted twice ... dont know why :hehe:
______________________________________________________________________________________________________________________________________________________________________________________
HTH !
Kin
MCTS : 2005, 2008
Active SQL Server Community Contributor 🙂
March 30, 2010 at 11:31 am
Regardless of what the h/w diagnostics show, you do have a transient corruption problem with your I/O subsystem. That's the only these errors can occur, and to prove these problems, that's why we put the errors into 2005.
Hardware vendors' diagnostics don't adequately stress I/O subsystems to expose such problems - you need to run SQLIOSIM to expose these. Don't ignore this issue because of what the diagnostics tell you.
Thanks
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 30, 2010 at 12:07 pm
Paul Randal (3/30/2010)
Regardless of what the h/w diagnostics show, you do have a transient corruption problem with your I/O subsystem. That's the only these errors can occur, and to prove these problems, that's why we put the errors into 2005.Hardware vendors' diagnostics don't adequately stress I/O subsystems to expose such problems - you need to run SQLIOSIM to expose these. Don't ignore this issue because of what the diagnostics tell you.
Thanks
Thanks Paul !
Will talk with the IT guys to run SQLIOSIM. Also, I restored msdb to a different server as msdb_test and ran a dbcc checkdb 'msdb_test' and below is the result :
DBCC results for 'msdb_test'.
Service Broker Msg 9675, State 1: Message Types analyzed: 16.
Service Broker Msg 9676, State 1: Service Contracts analyzed: 7.
Service Broker Msg 9667, State 1: Services analyzed: 5.
Service Broker Msg 9668, State 1: Service Queues analyzed: 5.
Service Broker Msg 9669, State 1: Conversation Endpoints analyzed: 0.
Service Broker Msg 9674, State 1: Conversation Groups analyzed: 0.
Service Broker Msg 9670, State 1: Remote Service Bindings analyzed: 0.
DBCC results for 'sys.sysrowsetcolumns'.
There are 1444 rows in 15 pages for object "sys.sysrowsetcolumns".
DBCC results for 'sys.sysrowsets'.
There are 209 rows in 2 pages for object "sys.sysrowsets".
DBCC results for 'sysallocunits'.
There are 243 rows in 3 pages for object "sysallocunits".
DBCC results for 'sys.sysfiles1'.
There are 2 rows in 1 pages for object "sys.sysfiles1".
DBCC results for 'sys.syshobtcolumns'.
There are 1444 rows in 16 pages for object "sys.syshobtcolumns".
DBCC results for 'sys.syshobts'.
There are 209 rows in 2 pages for object "sys.syshobts".
DBCC results for 'sys.sysftinds'.
There are 0 rows in 0 pages for object "sys.sysftinds".
DBCC results for 'sys.sysserefs'.
There are 243 rows in 1 pages for object "sys.sysserefs".
DBCC results for 'sys.sysowners'.
There are 23 rows in 1 pages for object "sys.sysowners".
DBCC results for 'sys.sysprivs'.
There are 304 rows in 3 pages for object "sys.sysprivs".
DBCC results for 'sys.sysschobjs'.
There are 676 rows in 14 pages for object "sys.sysschobjs".
DBCC results for 'sys.syscolpars'.
There are 2862 rows in 68 pages for object "sys.syscolpars".
DBCC results for 'sys.sysnsobjs'.
There are 1 rows in 1 pages for object "sys.sysnsobjs".
DBCC results for 'sys.syscerts'.
There are 2 rows in 1 pages for object "sys.syscerts".
DBCC results for 'sys.sysxprops'.
There are 0 rows in 0 pages for object "sys.sysxprops".
DBCC results for 'sys.sysscalartypes'.
There are 27 rows in 1 pages for object "sys.sysscalartypes".
DBCC results for 'sys.systypedsubobjs'.
There are 0 rows in 0 pages for object "sys.systypedsubobjs".
DBCC results for 'sys.sysidxstats'.
There are 368 rows in 6 pages for object "sys.sysidxstats".
DBCC results for 'sys.sysiscols'.
There are 520 rows in 5 pages for object "sys.sysiscols".
DBCC results for 'sys.sysbinobjs'.
There are 28 rows in 1 pages for object "sys.sysbinobjs".
DBCC results for 'sys.sysobjvalues'.
There are 837 rows in 404 pages for object "sys.sysobjvalues".
DBCC results for 'sys.sysclsobjs'.
There are 23 rows in 1 pages for object "sys.sysclsobjs".
DBCC results for 'sys.sysrowsetrefs'.
There are 0 rows in 0 pages for object "sys.sysrowsetrefs".
DBCC results for 'sys.sysremsvcbinds'.
There are 0 rows in 0 pages for object "sys.sysremsvcbinds".
DBCC results for 'sys.sysxmitqueue'.
There are 0 rows in 0 pages for object "sys.sysxmitqueue".
DBCC results for 'sys.sysrts'.
There are 1 rows in 1 pages for object "sys.sysrts".
DBCC results for 'sys.sysconvgroup'.
There are 0 rows in 1 pages for object "sys.sysconvgroup".
DBCC results for 'sys.sysdesend'.
There are 0 rows in 1 pages for object "sys.sysdesend".
DBCC results for 'sys.sysdercv'.
There are 0 rows in 1 pages for object "sys.sysdercv".
DBCC results for 'sys.syssingleobjrefs'.
There are 274 rows in 2 pages for object "sys.syssingleobjrefs".
DBCC results for 'sys.sysmultiobjrefs'.
There are 2483 rows in 19 pages for object "sys.sysmultiobjrefs".
DBCC results for 'sys.sysdbfiles'.
There are 2 rows in 1 pages for object "sys.sysdbfiles".
DBCC results for 'sys.sysguidrefs'.
There are 0 rows in 0 pages for object "sys.sysguidrefs".
DBCC results for 'sys.sysqnames'.
There are 91 rows in 1 pages for object "sys.sysqnames".
DBCC results for 'sys.sysxmlcomponent'.
There are 93 rows in 1 pages for object "sys.sysxmlcomponent".
DBCC results for 'sys.sysxmlfacet'.
There are 97 rows in 1 pages for object "sys.sysxmlfacet".
DBCC results for 'sys.sysxmlplacement'.
There are 17 rows in 1 pages for object "sys.sysxmlplacement".
DBCC results for 'sys.sysobjkeycrypts'.
There are 618 rows in 23 pages for object "sys.sysobjkeycrypts".
DBCC results for 'sys.sysasymkeys'.
There are 0 rows in 0 pages for object "sys.sysasymkeys".
DBCC results for 'sys.syssqlguides'.
There are 0 rows in 0 pages for object "sys.syssqlguides".
DBCC results for 'sys.sysbinsubobjs'.
There are 0 rows in 0 pages for object "sys.sysbinsubobjs".
DBCC results for 'syssubsystems'.
There are 11 rows in 1 pages for object "syssubsystems".
DBCC results for 'sysproxysubsystem'.
There are 0 rows in 0 pages for object "sysproxysubsystem".
DBCC results for 'restorefilegroup'.
There are 458 rows in 4 pages for object "restorefilegroup".
DBCC results for 'sysproxylogin'.
There are 0 rows in 0 pages for object "sysproxylogin".
DBCC results for 'logmarkhistory'.
There are 0 rows in 0 pages for object "logmarkhistory".
DBCC results for 'sqlagent_info'.
There are 0 rows in 0 pages for object "sqlagent_info".
DBCC results for 'suspect_pages'.
There are 0 rows in 0 pages for object "suspect_pages".
DBCC results for 'sysdownloadlist'.
There are 0 rows in 0 pages for object "sysdownloadlist".
DBCC results for 'sysjobhistory'.
There are 903 rows in 411 pages for object "sysjobhistory".
DBCC results for 'log_shipping_primary_databases'.
There are 0 rows in 0 pages for object "log_shipping_primary_databases".
DBCC results for 'sysdtscategories'.
There are 3 rows in 1 pages for object "sysdtscategories".
DBCC results for 'sysoriginatingservers'.
There are 0 rows in 0 pages for object "sysoriginatingservers".
DBCC results for 'log_shipping_primary_secondaries'.
There are 0 rows in 0 pages for object "log_shipping_primary_secondaries".
DBCC results for 'sysdtspackages'.
There are 0 rows in 0 pages for object "sysdtspackages".
DBCC results for 'log_shipping_monitor_primary'.
There are 0 rows in 0 pages for object "log_shipping_monitor_primary".
DBCC results for 'log_shipping_monitor_history_detail'.
There are 0 rows in 0 pages for object "log_shipping_monitor_history_detail".
DBCC results for 'log_shipping_monitor_error_detail'.
There are 0 rows in 0 pages for object "log_shipping_monitor_error_detail".
DBCC results for 'log_shipping_secondary'.
There are 0 rows in 0 pages for object "log_shipping_secondary".
DBCC results for 'sysjobs'.
There are 10 rows in 3 pages for object "sysjobs".
DBCC results for 'log_shipping_secondary_databases'.
There are 0 rows in 0 pages for object "log_shipping_secondary_databases".
DBCC results for 'sysjobservers'.
There are 10 rows in 1 pages for object "sysjobservers".
DBCC results for 'log_shipping_monitor_secondary'.
There are 0 rows in 0 pages for object "log_shipping_monitor_secondary".
DBCC results for 'syssessions'.
There are 6 rows in 1 pages for object "syssessions".
DBCC results for 'log_shipping_monitor_alert'.
There are 0 rows in 0 pages for object "log_shipping_monitor_alert".
DBCC results for 'sysjobactivity'.
There are 10 rows in 1 pages for object "sysjobactivity".
DBCC results for 'sysjobsteps'.
There are 29 rows in 19 pages for object "sysjobsteps".
DBCC results for 'sysjobstepslogs'.
There are 0 rows in 0 pages for object "sysjobstepslogs".
DBCC results for 'sysdtspackagelog'.
There are 0 rows in 0 pages for object "sysdtspackagelog".
DBCC results for 'sysdtssteplog'.
There are 0 rows in 0 pages for object "sysdtssteplog".
DBCC results for 'sysschedules'.
There are 9 rows in 1 pages for object "sysschedules".
DBCC results for 'sysdtstasklog'.
There are 0 rows in 0 pages for object "sysdtstasklog".
DBCC results for 'sysjobschedules'.
There are 9 rows in 1 pages for object "sysjobschedules".
DBCC results for 'syscategories'.
There are 21 rows in 1 pages for object "syscategories".
DBCC results for 'systargetservers'.
There are 0 rows in 0 pages for object "systargetservers".
DBCC results for 'systargetservergroups'.
There are 0 rows in 0 pages for object "systargetservergroups".
DBCC results for 'systargetservergroupmembers'.
There are 0 rows in 0 pages for object "systargetservergroupmembers".
DBCC results for 'sysalerts'.
There are 0 rows in 0 pages for object "sysalerts".
DBCC results for 'sysmail_profile'.
There are 1 rows in 1 pages for object "sysmail_profile".
DBCC results for 'sysoperators'.
There are 0 rows in 0 pages for object "sysoperators".
DBCC results for 'sysnotifications'.
There are 0 rows in 0 pages for object "sysnotifications".
DBCC results for 'sysmaintplan_subplans'.
There are 0 rows in 0 pages for object "sysmaintplan_subplans".
DBCC results for 'sys.queue_messages_843150049'.
There are 0 rows in 1 pages for object "sys.queue_messages_843150049".
DBCC results for 'sysmail_principalprofile'.
There are 0 rows in 0 pages for object "sysmail_principalprofile".
DBCC results for 'sys.queue_messages_875150163'.
There are 0 rows in 1 pages for object "sys.queue_messages_875150163".
DBCC results for 'sysmaintplan_log'.
There are 0 rows in 0 pages for object "sysmaintplan_log".
DBCC results for 'sysmaintplan_logdetail'.
There are 0 rows in 0 pages for object "sysmaintplan_logdetail".
DBCC results for 'sysmail_account'.
There are 4 rows in 1 pages for object "sysmail_account".
DBCC results for 'systaskids'.
There are 0 rows in 0 pages for object "systaskids".
DBCC results for 'syscachedcredentials'.
There are 1 rows in 1 pages for object "syscachedcredentials".
DBCC results for 'sysdbmaintplans'.
There are 1 rows in 1 pages for object "sysdbmaintplans".
DBCC results for 'sysmail_profileaccount'.
There are 4 rows in 1 pages for object "sysmail_profileaccount".
DBCC results for 'MSdbms'.
There are 7 rows in 1 pages for object "MSdbms".
DBCC results for 'MSdbms_datatype'.
There are 141 rows in 1 pages for object "MSdbms_datatype".
DBCC results for 'sysmail_servertype'.
There are 1 rows in 1 pages for object "sysmail_servertype".
DBCC results for 'sysdbmaintplan_jobs'.
There are 0 rows in 0 pages for object "sysdbmaintplan_jobs".
DBCC results for 'MSdbms_map'.
There are 248 rows in 3 pages for object "MSdbms_map".
DBCC results for 'sysdbmaintplan_databases'.
There are 0 rows in 0 pages for object "sysdbmaintplan_databases".
DBCC results for 'sysmail_server'.
There are 4 rows in 1 pages for object "sysmail_server".
DBCC results for 'sysdbmaintplan_history'.
There are 0 rows in 0 pages for object "sysdbmaintplan_history".
DBCC results for 'MSdbms_datatype_mapping'.
There are 325 rows in 2 pages for object "MSdbms_datatype_mapping".
DBCC results for 'sysmail_configuration'.
There are 7 rows in 1 pages for object "sysmail_configuration".
DBCC results for 'sysmail_mailitems'.
There are 19 rows in 5 pages for object "sysmail_mailitems".
DBCC results for 'log_shipping_primaries'.
There are 0 rows in 0 pages for object "log_shipping_primaries".
DBCC results for 'log_shipping_secondaries'.
There are 0 rows in 0 pages for object "log_shipping_secondaries".
DBCC results for 'sysmail_attachments'.
There are 0 rows in 0 pages for object "sysmail_attachments".
DBCC results for 'sysmail_send_retries'.
There are 0 rows in 0 pages for object "sysmail_send_retries".
DBCC results for 'backupmediaset'.
There are 5949 rows in 74 pages for object "backupmediaset".
DBCC results for 'sysmail_log'.
There are 40 rows in 1 pages for object "sysmail_log".
DBCC results for 'backupmediafamily'.
There are 5951 rows in 316 pages for object "backupmediafamily".
DBCC results for 'sysdtspackages90'.
There are 0 rows in 0 pages for object "sysdtspackages90".
DBCC results for 'backupset'.
There are 5949 rows in 571 pages for object "backupset".
DBCC results for 'sysdtspackagefolders90'.
There are 2 rows in 1 pages for object "sysdtspackagefolders90".
DBCC results for 'sys.queue_messages_1977058079'.
There are 0 rows in 0 pages for object "sys.queue_messages_1977058079".
DBCC results for 'sysmail_query_transfer'.
There are 0 rows in 0 pages for object "sysmail_query_transfer".
DBCC results for 'backupfilegroup'.
There are 5949 rows in 85 pages for object "backupfilegroup".
DBCC results for 'sys.queue_messages_2009058193'.
There are 0 rows in 0 pages for object "sys.queue_messages_2009058193".
DBCC results for 'sysdtslog90'.
There are 0 rows in 0 pages for object "sysdtslog90".
DBCC results for 'sysmail_attachments_transfer'.
There are 0 rows in 0 pages for object "sysmail_attachments_transfer".
DBCC results for 'backupfile'.
There are 11898 rows in 521 pages for object "backupfile".
DBCC results for 'sys.queue_messages_2041058307'.
There are 0 rows in 0 pages for object "sys.queue_messages_2041058307".
DBCC results for 'restorehistory'.
There are 458 rows in 7 pages for object "restorehistory".
DBCC results for 'sysproxies'.
There are 0 rows in 0 pages for object "sysproxies".
DBCC results for 'restorefile'.
There are 916 rows in 17 pages for object "restorefile".
CHECKDB found 0 allocation errors and 0 consistency errors in database 'msdb_test'.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
This confirms that my msdb is clean 🙂
But as a side note: what is the disadvantage of restoring msdb from a previous backup except that we loose the job history for all the jobs and backups that happend after the restored version date ?
and when restoring msdb, does SQL agent needs to be stopped ?
what will happen to any jobs that are created after the restored version of the msdb ?
Thanks,
\\K
______________________________________________________________________________________________________________________________________________________________________________________
HTH !
Kin
MCTS : 2005, 2008
Active SQL Server Community Contributor 🙂
March 30, 2010 at 12:07 pm
Paul Randal (3/30/2010)
Regardless of what the h/w diagnostics show, you do have a transient corruption problem with your I/O subsystem. That's the only these errors can occur, and to prove these problems, that's why we put the errors into 2005.Hardware vendors' diagnostics don't adequately stress I/O subsystems to expose such problems - you need to run SQLIOSIM to expose these. Don't ignore this issue because of what the diagnostics tell you.
Thanks
Thanks Paul !
Will talk with the IT guys to run SQLIOSIM. Also, I restored msdb to a different server as msdb_test and ran a dbcc checkdb 'msdb_test' and below is the result :
DBCC results for 'msdb_test'.
Service Broker Msg 9675, State 1: Message Types analyzed: 16.
Service Broker Msg 9676, State 1: Service Contracts analyzed: 7.
Service Broker Msg 9667, State 1: Services analyzed: 5.
Service Broker Msg 9668, State 1: Service Queues analyzed: 5.
Service Broker Msg 9669, State 1: Conversation Endpoints analyzed: 0.
Service Broker Msg 9674, State 1: Conversation Groups analyzed: 0.
Service Broker Msg 9670, State 1: Remote Service Bindings analyzed: 0.
DBCC results for 'sys.sysrowsetcolumns'.
There are 1444 rows in 15 pages for object "sys.sysrowsetcolumns".
DBCC results for 'sys.sysrowsets'.
There are 209 rows in 2 pages for object "sys.sysrowsets".
DBCC results for 'sysallocunits'.
There are 243 rows in 3 pages for object "sysallocunits".
DBCC results for 'sys.sysfiles1'.
There are 2 rows in 1 pages for object "sys.sysfiles1".
DBCC results for 'sys.syshobtcolumns'.
There are 1444 rows in 16 pages for object "sys.syshobtcolumns".
DBCC results for 'sys.syshobts'.
There are 209 rows in 2 pages for object "sys.syshobts".
DBCC results for 'sys.sysftinds'.
There are 0 rows in 0 pages for object "sys.sysftinds".
DBCC results for 'sys.sysserefs'.
There are 243 rows in 1 pages for object "sys.sysserefs".
DBCC results for 'sys.sysowners'.
There are 23 rows in 1 pages for object "sys.sysowners".
DBCC results for 'sys.sysprivs'.
There are 304 rows in 3 pages for object "sys.sysprivs".
DBCC results for 'sys.sysschobjs'.
There are 676 rows in 14 pages for object "sys.sysschobjs".
DBCC results for 'sys.syscolpars'.
There are 2862 rows in 68 pages for object "sys.syscolpars".
DBCC results for 'sys.sysnsobjs'.
There are 1 rows in 1 pages for object "sys.sysnsobjs".
DBCC results for 'sys.syscerts'.
There are 2 rows in 1 pages for object "sys.syscerts".
DBCC results for 'sys.sysxprops'.
There are 0 rows in 0 pages for object "sys.sysxprops".
DBCC results for 'sys.sysscalartypes'.
There are 27 rows in 1 pages for object "sys.sysscalartypes".
DBCC results for 'sys.systypedsubobjs'.
There are 0 rows in 0 pages for object "sys.systypedsubobjs".
DBCC results for 'sys.sysidxstats'.
There are 368 rows in 6 pages for object "sys.sysidxstats".
DBCC results for 'sys.sysiscols'.
There are 520 rows in 5 pages for object "sys.sysiscols".
DBCC results for 'sys.sysbinobjs'.
There are 28 rows in 1 pages for object "sys.sysbinobjs".
DBCC results for 'sys.sysobjvalues'.
There are 837 rows in 404 pages for object "sys.sysobjvalues".
DBCC results for 'sys.sysclsobjs'.
There are 23 rows in 1 pages for object "sys.sysclsobjs".
DBCC results for 'sys.sysrowsetrefs'.
There are 0 rows in 0 pages for object "sys.sysrowsetrefs".
DBCC results for 'sys.sysremsvcbinds'.
There are 0 rows in 0 pages for object "sys.sysremsvcbinds".
DBCC results for 'sys.sysxmitqueue'.
There are 0 rows in 0 pages for object "sys.sysxmitqueue".
DBCC results for 'sys.sysrts'.
There are 1 rows in 1 pages for object "sys.sysrts".
DBCC results for 'sys.sysconvgroup'.
There are 0 rows in 1 pages for object "sys.sysconvgroup".
DBCC results for 'sys.sysdesend'.
There are 0 rows in 1 pages for object "sys.sysdesend".
DBCC results for 'sys.sysdercv'.
There are 0 rows in 1 pages for object "sys.sysdercv".
DBCC results for 'sys.syssingleobjrefs'.
There are 274 rows in 2 pages for object "sys.syssingleobjrefs".
DBCC results for 'sys.sysmultiobjrefs'.
There are 2483 rows in 19 pages for object "sys.sysmultiobjrefs".
DBCC results for 'sys.sysdbfiles'.
There are 2 rows in 1 pages for object "sys.sysdbfiles".
DBCC results for 'sys.sysguidrefs'.
There are 0 rows in 0 pages for object "sys.sysguidrefs".
DBCC results for 'sys.sysqnames'.
There are 91 rows in 1 pages for object "sys.sysqnames".
DBCC results for 'sys.sysxmlcomponent'.
There are 93 rows in 1 pages for object "sys.sysxmlcomponent".
DBCC results for 'sys.sysxmlfacet'.
There are 97 rows in 1 pages for object "sys.sysxmlfacet".
DBCC results for 'sys.sysxmlplacement'.
There are 17 rows in 1 pages for object "sys.sysxmlplacement".
DBCC results for 'sys.sysobjkeycrypts'.
There are 618 rows in 23 pages for object "sys.sysobjkeycrypts".
DBCC results for 'sys.sysasymkeys'.
There are 0 rows in 0 pages for object "sys.sysasymkeys".
DBCC results for 'sys.syssqlguides'.
There are 0 rows in 0 pages for object "sys.syssqlguides".
DBCC results for 'sys.sysbinsubobjs'.
There are 0 rows in 0 pages for object "sys.sysbinsubobjs".
DBCC results for 'syssubsystems'.
There are 11 rows in 1 pages for object "syssubsystems".
DBCC results for 'sysproxysubsystem'.
There are 0 rows in 0 pages for object "sysproxysubsystem".
DBCC results for 'restorefilegroup'.
There are 458 rows in 4 pages for object "restorefilegroup".
DBCC results for 'sysproxylogin'.
There are 0 rows in 0 pages for object "sysproxylogin".
DBCC results for 'logmarkhistory'.
There are 0 rows in 0 pages for object "logmarkhistory".
DBCC results for 'sqlagent_info'.
There are 0 rows in 0 pages for object "sqlagent_info".
DBCC results for 'suspect_pages'.
There are 0 rows in 0 pages for object "suspect_pages".
DBCC results for 'sysdownloadlist'.
There are 0 rows in 0 pages for object "sysdownloadlist".
DBCC results for 'sysjobhistory'.
There are 903 rows in 411 pages for object "sysjobhistory".
DBCC results for 'log_shipping_primary_databases'.
There are 0 rows in 0 pages for object "log_shipping_primary_databases".
DBCC results for 'sysdtscategories'.
There are 3 rows in 1 pages for object "sysdtscategories".
DBCC results for 'sysoriginatingservers'.
There are 0 rows in 0 pages for object "sysoriginatingservers".
DBCC results for 'log_shipping_primary_secondaries'.
There are 0 rows in 0 pages for object "log_shipping_primary_secondaries".
DBCC results for 'sysdtspackages'.
There are 0 rows in 0 pages for object "sysdtspackages".
DBCC results for 'log_shipping_monitor_primary'.
There are 0 rows in 0 pages for object "log_shipping_monitor_primary".
DBCC results for 'log_shipping_monitor_history_detail'.
There are 0 rows in 0 pages for object "log_shipping_monitor_history_detail".
DBCC results for 'log_shipping_monitor_error_detail'.
There are 0 rows in 0 pages for object "log_shipping_monitor_error_detail".
DBCC results for 'log_shipping_secondary'.
There are 0 rows in 0 pages for object "log_shipping_secondary".
DBCC results for 'sysjobs'.
There are 10 rows in 3 pages for object "sysjobs".
DBCC results for 'log_shipping_secondary_databases'.
There are 0 rows in 0 pages for object "log_shipping_secondary_databases".
DBCC results for 'sysjobservers'.
There are 10 rows in 1 pages for object "sysjobservers".
DBCC results for 'log_shipping_monitor_secondary'.
There are 0 rows in 0 pages for object "log_shipping_monitor_secondary".
DBCC results for 'syssessions'.
There are 6 rows in 1 pages for object "syssessions".
DBCC results for 'log_shipping_monitor_alert'.
There are 0 rows in 0 pages for object "log_shipping_monitor_alert".
DBCC results for 'sysjobactivity'.
There are 10 rows in 1 pages for object "sysjobactivity".
DBCC results for 'sysjobsteps'.
There are 29 rows in 19 pages for object "sysjobsteps".
DBCC results for 'sysjobstepslogs'.
There are 0 rows in 0 pages for object "sysjobstepslogs".
DBCC results for 'sysdtspackagelog'.
There are 0 rows in 0 pages for object "sysdtspackagelog".
DBCC results for 'sysdtssteplog'.
There are 0 rows in 0 pages for object "sysdtssteplog".
DBCC results for 'sysschedules'.
There are 9 rows in 1 pages for object "sysschedules".
DBCC results for 'sysdtstasklog'.
There are 0 rows in 0 pages for object "sysdtstasklog".
DBCC results for 'sysjobschedules'.
There are 9 rows in 1 pages for object "sysjobschedules".
DBCC results for 'syscategories'.
There are 21 rows in 1 pages for object "syscategories".
DBCC results for 'systargetservers'.
There are 0 rows in 0 pages for object "systargetservers".
DBCC results for 'systargetservergroups'.
There are 0 rows in 0 pages for object "systargetservergroups".
DBCC results for 'systargetservergroupmembers'.
There are 0 rows in 0 pages for object "systargetservergroupmembers".
DBCC results for 'sysalerts'.
There are 0 rows in 0 pages for object "sysalerts".
DBCC results for 'sysmail_profile'.
There are 1 rows in 1 pages for object "sysmail_profile".
DBCC results for 'sysoperators'.
There are 0 rows in 0 pages for object "sysoperators".
DBCC results for 'sysnotifications'.
There are 0 rows in 0 pages for object "sysnotifications".
DBCC results for 'sysmaintplan_subplans'.
There are 0 rows in 0 pages for object "sysmaintplan_subplans".
DBCC results for 'sys.queue_messages_843150049'.
There are 0 rows in 1 pages for object "sys.queue_messages_843150049".
DBCC results for 'sysmail_principalprofile'.
There are 0 rows in 0 pages for object "sysmail_principalprofile".
DBCC results for 'sys.queue_messages_875150163'.
There are 0 rows in 1 pages for object "sys.queue_messages_875150163".
DBCC results for 'sysmaintplan_log'.
There are 0 rows in 0 pages for object "sysmaintplan_log".
DBCC results for 'sysmaintplan_logdetail'.
There are 0 rows in 0 pages for object "sysmaintplan_logdetail".
DBCC results for 'sysmail_account'.
There are 4 rows in 1 pages for object "sysmail_account".
DBCC results for 'systaskids'.
There are 0 rows in 0 pages for object "systaskids".
DBCC results for 'syscachedcredentials'.
There are 1 rows in 1 pages for object "syscachedcredentials".
DBCC results for 'sysdbmaintplans'.
There are 1 rows in 1 pages for object "sysdbmaintplans".
DBCC results for 'sysmail_profileaccount'.
There are 4 rows in 1 pages for object "sysmail_profileaccount".
DBCC results for 'MSdbms'.
There are 7 rows in 1 pages for object "MSdbms".
DBCC results for 'MSdbms_datatype'.
There are 141 rows in 1 pages for object "MSdbms_datatype".
DBCC results for 'sysmail_servertype'.
There are 1 rows in 1 pages for object "sysmail_servertype".
DBCC results for 'sysdbmaintplan_jobs'.
There are 0 rows in 0 pages for object "sysdbmaintplan_jobs".
DBCC results for 'MSdbms_map'.
There are 248 rows in 3 pages for object "MSdbms_map".
DBCC results for 'sysdbmaintplan_databases'.
There are 0 rows in 0 pages for object "sysdbmaintplan_databases".
DBCC results for 'sysmail_server'.
There are 4 rows in 1 pages for object "sysmail_server".
DBCC results for 'sysdbmaintplan_history'.
There are 0 rows in 0 pages for object "sysdbmaintplan_history".
DBCC results for 'MSdbms_datatype_mapping'.
There are 325 rows in 2 pages for object "MSdbms_datatype_mapping".
DBCC results for 'sysmail_configuration'.
There are 7 rows in 1 pages for object "sysmail_configuration".
DBCC results for 'sysmail_mailitems'.
There are 19 rows in 5 pages for object "sysmail_mailitems".
DBCC results for 'log_shipping_primaries'.
There are 0 rows in 0 pages for object "log_shipping_primaries".
DBCC results for 'log_shipping_secondaries'.
There are 0 rows in 0 pages for object "log_shipping_secondaries".
DBCC results for 'sysmail_attachments'.
There are 0 rows in 0 pages for object "sysmail_attachments".
DBCC results for 'sysmail_send_retries'.
There are 0 rows in 0 pages for object "sysmail_send_retries".
DBCC results for 'backupmediaset'.
There are 5949 rows in 74 pages for object "backupmediaset".
DBCC results for 'sysmail_log'.
There are 40 rows in 1 pages for object "sysmail_log".
DBCC results for 'backupmediafamily'.
There are 5951 rows in 316 pages for object "backupmediafamily".
DBCC results for 'sysdtspackages90'.
There are 0 rows in 0 pages for object "sysdtspackages90".
DBCC results for 'backupset'.
There are 5949 rows in 571 pages for object "backupset".
DBCC results for 'sysdtspackagefolders90'.
There are 2 rows in 1 pages for object "sysdtspackagefolders90".
DBCC results for 'sys.queue_messages_1977058079'.
There are 0 rows in 0 pages for object "sys.queue_messages_1977058079".
DBCC results for 'sysmail_query_transfer'.
There are 0 rows in 0 pages for object "sysmail_query_transfer".
DBCC results for 'backupfilegroup'.
There are 5949 rows in 85 pages for object "backupfilegroup".
DBCC results for 'sys.queue_messages_2009058193'.
There are 0 rows in 0 pages for object "sys.queue_messages_2009058193".
DBCC results for 'sysdtslog90'.
There are 0 rows in 0 pages for object "sysdtslog90".
DBCC results for 'sysmail_attachments_transfer'.
There are 0 rows in 0 pages for object "sysmail_attachments_transfer".
DBCC results for 'backupfile'.
There are 11898 rows in 521 pages for object "backupfile".
DBCC results for 'sys.queue_messages_2041058307'.
There are 0 rows in 0 pages for object "sys.queue_messages_2041058307".
DBCC results for 'restorehistory'.
There are 458 rows in 7 pages for object "restorehistory".
DBCC results for 'sysproxies'.
There are 0 rows in 0 pages for object "sysproxies".
DBCC results for 'restorefile'.
There are 916 rows in 17 pages for object "restorefile".
CHECKDB found 0 allocation errors and 0 consistency errors in database 'msdb_test'.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
This confirms that my msdb is clean 🙂
But as a side note: what is the disadvantage of restoring msdb from a previous backup except that we loose the job history for all the jobs and backups that happend after the restored version date ?
and when restoring msdb, does SQL agent needs to be stopped ?
what will happen to any jobs that are created after the restored version of the msdb ?
Thanks,
\\K
______________________________________________________________________________________________________________________________________________________________________________________
HTH !
Kin
MCTS : 2005, 2008
Active SQL Server Community Contributor 🙂
March 31, 2010 at 3:26 pm
Hello All,
Just to update the post, I have restored msdb from previous backup and its working fine 🙂
How ?
stop sql agent --> as this will be accessing msdb
restore msdb with replace from previous back using normal restore command
run a dbcc checkdb ('msdb') --> making sure it is not having corruption
start sql agent service.
The down-side is you will loose your history (job and backup) within the difference of the old backup and the time you restored msdb.
Thanks to all for their valuable responses.
Gail, I would appreciate any though from you that you wrote to get back 🙂
HTH,
\\K
______________________________________________________________________________________________________________________________________________________________________________________
HTH !
Kin
MCTS : 2005, 2008
Active SQL Server Community Contributor 🙂
March 31, 2010 at 3:26 pm
Hello All,
Just to update the post, I have restored msdb from previous backup and its working fine 🙂
How ?
stop sql agent --> as this will be accessing msdb
restore msdb with replace from previous back using normal restore command
run a dbcc checkdb ('msdb') --> making sure it is not having corruption
start sql agent service.
The down-side is you will loose your history (job and backup) within the difference of the old backup and the time you restored msdb.
Thanks to all for their valuable responses.
Gail, I would appreciate any though from you that you wrote to get back 🙂
HTH,
\\K
______________________________________________________________________________________________________________________________________________________________________________________
HTH !
Kin
MCTS : 2005, 2008
Active SQL Server Community Contributor 🙂
March 31, 2010 at 3:42 pm
I was the second opinion Gail was talking about. I monitor this forum but only usually jump in on the really nasty/obscure problems.
Thanks
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 31, 2010 at 3:55 pm
Paul Randal (3/31/2010)
I was the second opinion Gail was talking about. I monitor this forum but only usually jump in on the really nasty/obscure problems.Thanks
Thanks Paul ! Also I saw your video showing "Corruption and How to fix" really enjoyed and worth learning a lot.
Much obliged with your reply 🙂
\\K
______________________________________________________________________________________________________________________________________________________________________________________
HTH !
Kin
MCTS : 2005, 2008
Active SQL Server Community Contributor 🙂
March 31, 2010 at 3:55 pm
Paul Randal (3/31/2010)
I was the second opinion Gail was talking about. I monitor this forum but only usually jump in on the really nasty/obscure problems.Thanks
Thanks Paul ! Also I saw your video showing "Corruption and How to fix" really enjoyed and worth learning a lot.
Much obliged with your reply 🙂
\\K
______________________________________________________________________________________________________________________________________________________________________________________
HTH !
Kin
MCTS : 2005, 2008
Active SQL Server Community Contributor 🙂
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply