如果自己搞不定可以找诗檀软件专业SQL SERVER数据库修复团队成员帮您恢复!
诗檀软件专业数据库修复团队
服务热线 ： 13764045638 QQ号:47079569 邮箱：[email protected]
SQL Server使用Windows平台的标准API例如 ReadFile, WriteFile, ReadFileScatter, WriteFileGather 来实施IO操作。 在实施IO的过程中，SQL SERVER服务器检测这些API的报错信息。若这些API因操作系统原因报错，则SQL SERVER会报错Error 823 Msg。
典型的报错信息如下：
Error: 823, Severity: 24, State: 2.
2010-03-06 22:41:19.55 spid58 The operating system returned error 1117 (The request could not be performed because of an I/O device error.) to SQL Server during a read at offset 0x0000002d460000 in file ‘e:\program files\Microsoft SQL Server\mssql\data\mydb.MDF’. Additional messages in the SQL Server error log and system event log may provide more detail. This is a severe, system-level error condition that threatens database integrity and must be corrected immediately. It is recommended to complete a full database consistency check (DBCC CHECKDB). This error can be caused by many factors; for more information, see SQL Server Books Online.
若用户运行DBCC CHECKDB 检测数据库，则可能看到相关的数据文件有报错，但也有可能看不到。若执行该命令无任何报错，则可能遇到了一个系统临时发生的问题或磁盘问题。
原因:
823 报错通常说明存储子系统例如文件系统、驱动器或存储存在问题。当文件系统损坏导致数据文件损害时可能出现该问题。在读取数据时，SQL SERVER会在遇到823错误后再次发起读取请求，若再次读取成功，则查询不会失败；但是825告警会被写入到错误日志和事件日志中。
措施
可以检查MSDB里的suspect_pages表来发现存在问题的数据页面： SQLCMD.EXE -S localhost\sqlexpress 1> use msdb; 2> select * from suspect_pages; 3> go Changed database context to 'msdb'. database_id file_id page_id event_type error_count last_update_date ----------- ----------- -------------------- ----------- ----------- ----------------------- (0 rows affected) 使用DBCC CHECKDB 命令来检测位于同一个存储单元上的所有数据库 1> dbcc checkdb([china]) 2> go DBCC results for 'china'. Service Broker Msg 9675, State 1: Message Types analyzed: 14. Service Broker Msg 9676, State 1: Service Contracts analyzed: 6. Service Broker Msg 9667, State 1: Services analyzed: 3. Service Broker Msg 9668, State 1: Service Queues analyzed: 3. 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. Service Broker Msg 9605, State 1: Conversation Priorities analyzed: 0. DBCC results for 'sys.sysrscols'. There are 1229 rows in 15 pages for object "sys.sysrscols". DBCC results for 'sys.sysrowsets'. There are 164 rows in 3 pages for object "sys.sysrowsets". DBCC results for 'sys.sysclones'. There are 0 rows in 0 pages for object "sys.sysclones". DBCC results for 'sys.sysallocunits'. There are 195 rows in 3 pages for object "sys.sysallocunits". DBCC results for 'sys.sysfiles1'. There are 2 rows in 1 pages for object "sys.sysfiles1". DBCC results for 'sys.sysseobjvalues'. There are 0 rows in 0 pages for object "sys.sysseobjvalues". DBCC results for 'sys.syspriorities'. There are 0 rows in 0 pages for object "sys.syspriorities". DBCC results for 'sys.sysdbfrag'. There are 0 rows in 0 pages for object "sys.sysdbfrag". DBCC results for 'sys.sysfgfrag'. There are 0 rows in 0 pages for object "sys.sysfgfrag". DBCC results for 'sys.sysdbfiles'. There are 2 rows in 1 pages for object "sys.sysdbfiles". DBCC results for 'sys.syspru'. There are 0 rows in 0 pages for object "sys.syspru". DBCC results for 'sys.sysbrickfiles'. There are 0 rows in 0 pages for object "sys.sysbrickfiles". DBCC results for 'sys.sysphfg'. There are 1 rows in 1 pages for object "sys.sysphfg". DBCC results for 'sys.sysprufiles'. There are 2 rows in 1 pages for object "sys.sysprufiles". DBCC results for 'sys.sysftinds'. There are 0 rows in 0 pages for object "sys.sysftinds". DBCC results for 'sys.sysowners'. There are 14 rows in 1 pages for object "sys.sysowners". DBCC results for 'sys.sysdbreg'. There are 0 rows in 0 pages for object "sys.sysdbreg". DBCC results for 'sys.sysprivs'. There are 177 rows in 1 pages for object "sys.sysprivs". DBCC results for 'sys.sysschobjs'. There are 2428 rows in 33 pages for object "sys.sysschobjs". DBCC results for 'sys.syscsrowgroups'. There are 0 rows in 0 pages for object "sys.syscsrowgroups". DBCC results for 'sys.sysextsources'. There are 0 rows in 0 pages for object "sys.sysextsources". DBCC results for 'sys.sysexttables'. There are 0 rows in 0 pages for object "sys.sysexttables". DBCC results for 'sys.sysextfileformats'. There are 0 rows in 0 pages for object "sys.sysextfileformats". DBCC results for 'sys.sysmultiobjvalues'. There are 0 rows in 0 pages for object "sys.sysmultiobjvalues". DBCC results for 'sys.syscolpars'. There are 1015 rows in 18 pages for object "sys.syscolpars". DBCC results for 'sys.sysxlgns'. There are 0 rows in 0 pages for object "sys.sysxlgns". DBCC results for 'sys.sysxsrvs'. There are 0 rows in 0 pages for object "sys.sysxsrvs". DBCC results for 'sys.sysnsobjs'. There are 1 rows in 1 pages for object "sys.sysnsobjs". DBCC results for 'sys.sysusermsgs'. There are 0 rows in 0 pages for object "sys.sysusermsgs". DBCC results for 'sys.syscerts'. There are 0 rows in 0 pages for object "sys.syscerts". DBCC results for 'sys.sysrmtlgns'. There are 0 rows in 0 pages for object "sys.sysrmtlgns". DBCC results for 'sys.syslnklgns'. There are 0 rows in 0 pages for object "sys.syslnklgns". DBCC results for 'sys.sysxprops'. There are 0 rows in 0 pages for object "sys.sysxprops". DBCC results for 'sys.sysscalartypes'. There are 34 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 224 rows in 5 pages for object "sys.sysidxstats". DBCC results for 'sys.sysiscols'. There are 425 rows in 3 pages for object "sys.sysiscols". DBCC results for 'sys.sysendpts'. There are 0 rows in 0 pages for object "sys.sysendpts". DBCC results for 'sys.syswebmethods'. There are 0 rows in 0 pages for object "sys.syswebmethods". DBCC results for 'sys.sysbinobjs'. There are 23 rows in 1 pages for object "sys.sysbinobjs". DBCC results for 'sys.sysaudacts'. There are 0 rows in 0 pages for object "sys.sysaudacts". DBCC results for 'sys.sysobjvalues'. There are 225 rows in 20 pages for object "sys.sysobjvalues". DBCC results for 'sys.syscscolsegments'. There are 0 rows in 0 pages for object "sys.syscscolsegments". DBCC results for 'sys.syscsdictionaries'. There are 0 rows in 0 pages for object "sys.syscsdictionaries". DBCC results for 'sys.sysclsobjs'. There are 16 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 0 pages for object "sys.sysconvgroup". DBCC results for 'sys.sysdesend'. There are 0 rows in 0 pages for object "sys.sysdesend". DBCC results for 'sys.sysdercv'. There are 0 rows in 0 pages for object "sys.sysdercv". DBCC results for 'sys.syssingleobjrefs'. There are 179 rows in 1 pages for object "sys.syssingleobjrefs". DBCC results for 'sys.sysmultiobjrefs'. There are 113 rows in 1 pages for object "sys.sysmultiobjrefs". DBCC results for 'sys.sysguidrefs'. There are 0 rows in 0 pages for object "sys.sysguidrefs". DBCC results for 'sys.sysfoqueues'. There are 0 rows in 0 pages for object "sys.sysfoqueues". DBCC results for 'sys.syschildinsts'. There are 0 rows in 0 pages for object "sys.syschildinsts". DBCC results for 'sys.syscompfragments'. There are 0 rows in 0 pages for object "sys.syscompfragments". DBCC results for 'sys.sysftsemanticsdb'. There are 0 rows in 0 pages for object "sys.sysftsemanticsdb". DBCC results for 'sys.sysftstops'. There are 0 rows in 0 pages for object "sys.sysftstops". DBCC results for 'sys.sysftproperties'. There are 0 rows in 0 pages for object "sys.sysftproperties". DBCC results for 'sys.sysxmitbody'. There are 0 rows in 0 pages for object "sys.sysxmitbody". DBCC results for 'sys.sysfos'. There are 0 rows in 0 pages for object "sys.sysfos". DBCC results for 'sys.sysqnames'. There are 98 rows in 1 pages for object "sys.sysqnames". DBCC results for 'sys.sysxmlcomponent'. There are 100 rows in 1 pages for object "sys.sysxmlcomponent". DBCC results for 'sys.sysxmlfacet'. There are 112 rows in 1 pages for object "sys.sysxmlfacet". DBCC results for 'sys.sysxmlplacement'. There are 19 rows in 1 pages for object "sys.sysxmlplacement". DBCC results for 'sys.sysobjkeycrypts'. There are 0 rows in 0 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 3 rows in 1 pages for object "sys.sysbinsubobjs". DBCC results for 'sys.syssoftobjrefs'. There are 2 rows in 1 pages for object "sys.syssoftobjrefs". DBCC results for 'sys.sqlagent_jobs'. There are 0 rows in 0 pages for object "sys.sqlagent_jobs". DBCC results for 'sys.sqlagent_jobsteps'. There are 0 rows in 0 pages for object "sys.sqlagent_jobsteps". DBCC results for 'sys.sqlagent_job_history'. There are 0 rows in 0 pages for object "sys.sqlagent_job_history". DBCC results for 'sys.sqlagent_jobsteps_logs'. There are 0 rows in 0 pages for object "sys.sqlagent_jobsteps_logs". DBCC results for 'sys.plan_persist_query_text'. There are 0 rows in 0 pages for object "sys.plan_persist_query_text". DBCC results for 'sys.plan_persist_query'. There are 0 rows in 0 pages for object "sys.plan_persist_query". DBCC results for 'sys.plan_persist_plan'. There are 0 rows in 0 pages for object "sys.plan_persist_plan". DBCC results for 'sys.plan_persist_runtime_stats'. There are 0 rows in 0 pages for object "sys.plan_persist_runtime_stats". DBCC results for 'sys.plan_persist_runtime_stats_interval'. There are 0 rows in 0 pages for object "sys.plan_persist_runtime_stats_interval". DBCC results for 'sys.plan_persist_context_settings'. There are 0 rows in 0 pages for object "sys.plan_persist_context_settings". DBCC results for 'sys.plan_persist_query_hints'. There are 0 rows in 0 pages for object "sys.plan_persist_query_hints". DBCC results for 'sys.plan_persist_query_template_parameterization'. There are 0 rows in 0 pages for object "sys.plan_persist_query_template_parameterization". DBCC results for 'sys.plan_persist_wait_stats'. There are 0 rows in 0 pages for object "sys.plan_persist_wait_stats". DBCC results for 'sys.persistent_version_store'. There are 0 rows in 0 pages for object "sys.persistent_version_store". DBCC results for 'sys.persistent_version_store_long_term'. There are 0 rows in 0 pages for object "sys.persistent_version_store_long_term". DBCC results for 'sys.wpr_bucket_table'. There are 0 rows in 0 pages for object "sys.wpr_bucket_table". DBCC results for 'largetable'. There are 6291456 rows in 45924 pages for object "largetable". DBCC results for 'largetabl2'. There are 6291456 rows in 31937 pages for object "largetabl2". DBCC results for '??'. There are 0 rows in 0 pages for object "??". DBCC results for 'sys.queue_messages_1977058079'. There are 0 rows in 0 pages for object "sys.queue_messages_1977058079". DBCC results for 'sys.queue_messages_2009058193'. There are 0 rows in 0 pages for object "sys.queue_messages_2009058193". DBCC results for 'sys.queue_messages_2041058307'. There are 0 rows in 0 pages for object "sys.queue_messages_2041058307". DBCC results for 'sys.filestream_tombstone_2073058421'. There are 0 rows in 0 pages for object "sys.filestream_tombstone_2073058421". DBCC results for 'sys.syscommittab'. There are 0 rows in 0 pages for object "sys.syscommittab". DBCC results for 'sys.filetable_updates_2105058535'. There are 0 rows in 0 pages for object "sys.filetable_updates_2105058535". CHECKDB found 0 allocation errors and 0 consistency errors in database 'china'. DBCC execution completed. If DBCC printed error messages, contact your system administrator. 检查Window的事件日志 看有无存储或IO子系统的报错，例如当你看到823报错时可能时间日志中也有 The driver detected a controller error on \Device\Harddisk4\DR4 这样的信息。基于这些信息确定出现问题的磁盘。
Leave a Reply