MSSQL教程之修复SQLSERVER2000数据库之实战履历

仓酷云

支持AIX、FreeBSD、HP-UX、Linux、MacOS、NovellNetware、OpenBSD、OS/2Wrap、Solaris、Windows等多种操作系统server|sqlserver|数据|数据库
修复SQLSERVER2000数据库之实战履历

********************************************************************************

Author:黄山光亮顶

mail:leimin@jxfw.com

version:1.0.0

date:2004-1-30

(如需转载,请说明出处!,假如有成绩请发MAIL给我:-))

*******************************************************************************

我所讲的一个故事的背景是如许的，在某一个POS的项目中利用SQLSERVER2000做前台数据库，IBM的DB2做背景数据库。前台数据库的情况是如许的操纵体系是WINDOWS2000SERVER(10USERS),数据库是SQLSERVER2000(E)+SP3,Application是POS的收银体系（是一种及时的买卖体系）。硬件的设置是：P4XRON2.4G*2,36GHDD*5做的RAID5,1GMEMORY，HPDDS4磁带机,数据库的容量一样平常坚持在5G摆布。
由于数据对照的主要，而且数据容量也不年夜，我们请求的备份战略是天天在磁带机做POS_DB的全备份（一个礼拜7天一个轮回），在早晨还在硬盘上做全体备份（MASTER,MSDB,POS_DB).如许坚持两重的保险。

1.妨碍发作：
2003－12－2613:00
客户呈报一切的POS逝世机和SERVER运转速率十分的慢。经由从头启动服务器（启动到反省RAID卡时入手下手报警）我们发明在WINDEOWS2000SERVER的“体系日记”中有如许的信息：
Error:823,Severity:24,State:2
I/Oerror(tornpage)detectedduringreadatoffset0x0000001bf96000infileD:DATAPOS_DB.mdf.
SQLSERVER的“毛病日记”中有如许的信息：
2003-12-1003:34:22.23spid56Error:823,Severity:24,State:2
2003-12-1003:34:22.23spid56I/Oerror(tornpage)detectedduringreadatoffset0x00000074964000infileD:DATAPOS_DB.mdf..
来自msdn的注释：
I/Ologicalcheckfailure:IfareadWindowsAPIcallorawriteWindowsAPIcallforadatabasefileissuccessful,butspecificlogicalchecksonthedataarenotsuccessful(atornpage,forexample),an823errorisraised.Thefollowingerrormessageisanexampleofan823errorforanI/Ologicalcheckfailure:
2003-09-0516:51:18.90spid17Error:823,Severity:24,State:2
2003-09-0516:51:18.90spid17I/Oerror(tornpage)detectedduringreadatoffset0x00000094004000infileF:QLDatamydb.MDF..
Toresolvethisproblem,firstruntheDBCCCHECKDBstatementonthedatabasethatisassociatedwiththefileintheerrormessage.IftheDBCCCHECKDBstatementreportserrors,correctthoseerrorsbeforeyoutroubleshootthisproblem.IftheproblempersistsevenaftertheDBCCCHECKDBerrorshavebeencorrected,oriftheDBCCCHECKDBstatementdoesnotreportanyerrors,reviewtheMicrosoftWindowsNTsystemeventlogforanysystemerrorsordisk-relatederrors.Youcanalsocontactyourhardwarevendortorunanyappropriatediagnostics.
I/O逻辑反省失利：假如有一个WINDOWS程序在读取和写数据库文件时是乐成的，可是在具体的数据逻辑反省时没有乐成（好比：不完全的页），SQLSERVER会前往MSG823的毛病。上面就是一个I/O逻辑反省失利MSG823的实例：
2003-09-0516:51:18.90spid17Error:823,Severity:24,State:2
2003-09-0516:51:18.90spid17I/Oerror(tornpage)detectedduringreadatoffset0x00000094004000infileF:QLDatamydb.MDF..
要办理如许的成绩，起首要在该数据库中实行DBCCCHECKDB（毛病信息提醒的数据库文件）。假如DBCCCHECKDB报错，在你修复毛病之前改正这些毛病。假如这些毛病信息一向保存到实行DBCCCHECKDB运转以后，大概DBCCCHECKDB没有呈报任何毛病，反省WINDOWSNT体系的的事务检察器的和体系毛病或磁盘毛病相干的信息。你也能够接洽硬件厂商运转准确的诊断工具。


坏了:-(，数据库文件有成绩，在反省OS的事务检察器，我们发明在一个礼拜之前就有毛病信息（只是OFFSET的偏移地点分歧）。

赶忙反省HDD,公然发明在RAID5的第一快HDD亮了红灯（尘土太多，很难于看清）

实行DBCCCHECKDB(POS_DB)反省发明：
Server:Msg8909,Level16,State1,Line1
Tableerror:ObjectID26342838,indexID35207,pageID(1:50978).ThePageIdinthepageheader=(32230:-2048732002).


Server:Msg8939,Level16,State1,Line1
Tableerror:ObjectID859150106,indexID255,page(1:238770).Test(IS_ON(BUF_IOERR,bp->bstat)&&bp->berrcode)failed.Valuesare2057and-1.


Server:Msg8928,Level16,State1,Line1
ObjectID861246123,indexID0:Page(1:57291)couldnotbeprocessed.Seeothererrorsfordetails.


Server:Msg2511,Level16,State1,Line1
Tableerror:ObjectID862626116,IndexID0.Keysoutoforderonpage(1:269310),slots0and1.
啊哈，公然有良多的表都有毛病联系关系（请纪录每个毛病表的OBJECTID)
从MSDN查到：
毛病号Msg823:暗示SQLSERVER在读取数据和写数据时检测到硬件设备有成绩大概体系有成绩。
TORNPAGE:的意义是不完全的页
0x0000001bf96000：这是从数据文件入手下手处到TORNPAGE的字节数。
毛病号Msg8939：人人能够看看：http://support.microsoft.com/default.aspx?kbid=320434
FIX：在运转CHECKDB时，具有TABLOCK提醒的年夜容量拔出（bulkinsert,bcp等）大概招致毛病8929和8965
毛病号MSG8928:是和8939相干联的信息，
毛病号MSG8965:是和8939相干联的信息，

人人能够到上面的地点找到相干的信息：
http://support.microsoft.com/default.aspx?scid=kb;en-us;826433
PRB:AdditionalSQLServerDiagnosticsAddedtoDetectUnreportedI/OProblems
http://support.microsoft.com/default.aspx?scid=kb;en-us;828339
PRB:Errormessage823mayindicatehardwareproblemsorsystemproblems
http://support.microsoft.com/default.aspx?scid=kb;en-us;308795
FIX:CheckDBMayNotFixError8909orError8905

妨碍确诊：RAID有一块HDD坏，形成数据库文件损坏

2.改换HDD
2003－12－2823:00
如今就表现了RAID5的优点，坏了一块HDD，体系能够照旧运转，不外体系的日记和SQLSERVER的日记仍是有MSG823的报错信息。
依照RAID卡的REBUILD的步骤将新的HDD绑定到原始的RAID5中，顺遂完成:-)
用DBCC反省数据库的完全性
DBCCCHECKDB(POS_DB)WITHALL_ERRORMSGS
发明仍是有和改换HDD之前一样的ERROR信息，看来数据库文件仍是有成绩。

--有一个奇异成绩1，既然是5块HDD的RAID5，为什么有一块HDD坏会影响数据库文件的破坏，不解？？？:-(

3.恢单数据库
2003－12－2900:30
没有举措，用备份的数据集恢单数据库（看来备份是何等的主要）
USEMASTER
GO
RESTOREDATABASEPOS_DBFROMDISK=D:DATABASEBACKUPPOS_DB_BACKUP.DAT
从头启动MSSQLSERCVER服务，
NETSTOPMSSQLSERVER/NETSTARTMSSQLSERVER
用DBCC反省数据库的完全性
DBCCCHECKDB(POS_DB)WITHALL_ERRORMSGS

和恢复之前的毛病信息分歧，没有改动。
--奇异成绩之2，SQLSERVERBACKUP之前其实不考证数据库的完全性，数据库的全备份居然是有成绩的。愤恚！！

看来只能经由过程工具修单数据库了（--在修正之前纪录毛病表的纪录数，以便修单数据库落后行对照）。
在查询剖析器中运转：
ALTERDATABASEPOS_DBSETSINGL_USER
GO
DBCCCHECKDB(POS_DB,repair_allow_data_loss)WITHTABLOCK
GO
ALTERDATABASEPOS_DBSETMULTI_USER
GO

CHECKDB有3个参数:
REPAIR_ALLOW_DATA_LOSS
实行由REPAIR_REBUILD完成的一切修复，包含对行和页举行分派和作废分派以更正分派毛病、布局行或页的毛病，和删除已破坏的文本工具。这些修复大概会招致一些数据丧失。修复操纵能够在用户事件下完成以同意用户回滚所做的变动。假如回滚修复，则数据库仍会含有毛病，应当从备份举行恢复。假如因为所供应修复品级的原因漏掉某个毛病的修复，则将漏掉任何取决于该修复的修复。修复完成后，备份数据库。
REPAIR_FAST举行小的、不耗时的修复操纵，如修复非会萃索引中的附加键。这些修复能够很快完成，而且不会有丧失数据的伤害。
REPAIR_REBUILD实行由REPAIR_FAST完成的一切修复，包含必要较长工夫的修复（如重修索引）。实行这些修复时不会有丧失数据的伤害。



第一次运转，我们会发明：
DBCCresultsforTABLE_NAME.
Thereare1rowsin1pagesforobjectTABLE_NAME.
Theerrorhasbeenrepaired.
CHECKDBfound0allocationerrorsand1consistencyerrorsintable(ObjectID26342838)(objectID26342838).
CHECKDBfixed0allocationerrorsand1consistencyerrorsintable(ObjectID26342838)(objectID26342838).
如许的信息有良多，而且有“Theerrorhasbeenrepaired”的提醒。不外到最初仍是有如许的信息：
CHECKDBfound0allocationerrorsand19consistencyerrorsindatabasePOS_DB.
CHECKDBfixed0allocationerrorsand19consistencyerrorsindatabasePOS_DB.
再次运转，仍是有一样的毛病。糟:=)看来这类体例是没法修复如许测毛病。

失利！！！

再细心看看SQLSERVERBOL发明CHECKDB另有一个十分有效的参数PHYSICAL_ONLY

PHYSICAL_ONLY
仅限于反省页和纪录题目物理布局的完全性，和页工具ID和索引ID与分派布局之间的分歧性。该反省旨在以较低的开支反省数据库的物理分歧性，同时还检测会危及用户数据平安的完整页和罕见的硬件妨碍。PHYSICAL_ONLY一直意味着NO_INFOMSGS，而且不克不及与任何修复选项一同利用。


再次运转：
DBCCCHECKDB(POS_DB)withNO_INFOMSGS,PHYSICAL_ONLY
然后再运转：
DBCCCHECKDB(POS_DB,repair_allow_data_loss)WITHTABLOCK
此次会前往一些8952.8956的毛病信息:
Server:Msg8952,Level16,State1,Line1
Tableerror:DatabasePOS_DB,indexPOS_REFER.Idx2_POS_REFER(ID861246123)(indexID2).Extraorinvalidkeyforthekeys:


Server:Msg8956,Level16,State1,Line1
Indexrow(1:26315:23)withvalues(PLU_ID=6922825200240andPRD_AGGR_ID=10006andEVNT_ID=NULLandRGST_MDE=0andSUBPRD_NBR=0andSTR_ID=12andPRD_AGGR_ID=10006andSUBPRD_NBR=0andSTR_ID=12andPLU_ID=6922825200240andEVNT_ID=NULLandRGST_MDE=0)pointstothedatarowidentifiedby().

依据MSDN上的申明：
Thisproblemdoesnotcauseanydataorindexcorruption.Theproblemisinthemetadatawhichiscorrectedonlybydroppingandre-creatingtheindexes.
这些成绩不会引发数据或索引的破坏，这些成绩的元数据是准确的，只是删除再从头创建索引。
看来成绩是修正了。


再次运转DBCCCHECKDB(POS_DB),再次运转:DBCCCHECKDB(POS_DB),message没有毛病信息。

ok乐成修复:-)


4.反省修复后的数据库而且备份数据库
反省DBCCCHECKDB报错的相干表，和没有实行DBCC之前的纪录数举行对照，发明有一个表少了40笔记录。忧郁:-<

5.总结

1.RAID5其实不能包管SQLSERVER2000数据库的数据文件的完全性；
2.SQLERVER2000的备份程序不考证数据库文件的数据完全性；假如你的数据文件有成绩，备份时也不图示；
3.DBCCCHECKDB的repair_allow_data_loss并非十分平安的，不克不及修复一切的毛病，即便是对不完全页（TORNPAGE）的修复也会着成数据丧失；
4.DBCCCHECKDB的REPAIR_ALLOW_DATA_LOSS参数没法修复一切的毛病;

参考文章:
http://support.microsoft.com/default.aspx?scid=kb;en-us;298806
http://support.microsoft.com/default.aspx?scid=kb;en-us;284440
http://support.microsoft.com/default.aspx?kbid=320434
http://support.microsoft.com/default.aspx?scid=kb;en-us;828339
http://support.microsoft.com/default.aspx?scid=kb;en-us;308795
http://support.microsoft.com/default.aspx?scid=kb;en-us;826433
上面我们说了DML的闪回方案。但对于DDL却无能为力，对于大多数的DDL，即使是rowbase格式，二进制日志binlog中仍只记录语句本身。对于删表操作，只记录一个语句droptablet。仅凭这句话，无法还原表的数据。

原理很简单，对要求长时间计算某一时间点的报表生成和防用户操作错误很有帮助。但是比起Oracle10g的闪回技术还是细粒度不够。可惜！

这就引发了对varchar和char效率讨论的老问题。到底如何分配varchar的数据，是否会出现大规模的碎片？

还不是性能有问题！否则面向对象的数据库早就实现了！建议使用CLR的地方一般是和应用的复杂程度或操作系统环境有很高的耦合度的场景。如你想构建复杂的算法，并且用到了大量的指针和高级数据模型。

可能有的朋友会抱怨集成的orderby，其实如果使用ranking函数,Orderby是少不了的。如果担心Orderby会影响效率，可以为orderby的字段建立聚集索引，查询计划会忽略orderby操作（因为本来就是排序的嘛）。

作了些试验，发现使用CLR的存储过程或函数在达到一定的阀值的时候，系统性能会呈指数级下滑！这是非常危险的！只使用几个可能没有问题，当一旦大规模使用会造成严重的系统性能问题！

比如，MicrosoftSQLServer2008的某一个版本可以满足现在的这个业务的需要，而且价格还比Oracle11g要便宜，那么这一产品就是适合的。

但是随着数据量的增大，这种成本差距会逐渐减小，趋于相等。（500万数量级只相差10%左右）

始终遗憾SQLServer的登陆无法分配CPU/内存占用等指标数。如果你的SQLServer给别人分配了一个只可以读几个表的权限，而这个家伙疯狂的死循环进行连接查询，会给你的系统带来很大的负担。