SQL Server日志损坏造成整个数据库损坏的修复

SQLServer日志损坏造成整个数据库损坏的修复版本：V1.0作者：知行合一邮箱：409629442@时间：2014/9/29一、问题说明由于用户的数据库某张表比较大，大约有1000万条记录，数据库管理员在非业务时间对这个表进行删除清理，删除操作持续了1个小时左右，到工作时间，仍然没有正常结束。前端用户反应应用系统比较慢后，数据库管理员对删除操作进行终止，终止时仍然无法正常终止。最后，数据库管理员不得已对数据库进行重启，重启后发现数据库已经无法正常打开。由于用户比较急，就用一个比较老的备份进行了恢复。我们赶赴现场后，原先的数据库已经被删除，但用户已经针对原先的数据库文件和日志文件进行了拷贝。我们把备份的数据库文件拷贝到异机进行附加，总是报错，提示无法读取日志文件。具体报错如下：Thelogcannotberebuiltbecausetherewereopentransactions/userswhenthedatabasewasshutdown,nocheckpointoccurredtothedatabase,orthedatabasewasread-only.Thiserrorcouldoccurifthetransactionlogfilewasmanuallydeletedorlostduetoahardwareorenvironmentfailure.Msg1813,Level16,State2,Line2通过上面的报错可知，数据库的日志文件发生了损坏，这时已经不能通过简单的附加方式

进行恢复，也无法通过无日志附加的方式进行附加，因为这时数据库处在一个不一致的状态。、环境介绍操作系统：Windows2008R2SQLserver:SQLServer2008SP1数据文件路径：数据文件路径Fileid逻辑文件名称文件组大小D:\PPmonitor\PPmonitorData.MDF1PPmonitorDataPRIMARY41787200KBD:\PPmonitor\PPmonitorLog.LDF2PPmonitorLogD:\PPmonitor\PPmonitorData1.NDF3PPmonitor1DataPRIMARYD:\PPmonitor\PPmonitorLog1.ldf4PPmonitor1LogD:\PPmonitor\PPmonitorLog2.ldf5PPmonitor2LogG:\PPmonitor\PPmonitorDtat3.Mdf6PPmonitor3DataPRIMARYG:\PPmonitor\PPmonitordata2.Ndf7PPmonitor2DataPRIMARYG:\PPmonitor\PPmonitorLog3.Ldf8PPmonitor3Log注：数据文件后面为数据文件的file_id,file_id可以通过sys.master_files进行查看三、处理过程由于这时数据库已经无法正常打开（已经没有对应的数据库，或数据库状态错误，查看不了任何属性信息），所以我们必须重新创建同名的数据库，然后用备份的数据文件覆盖新创建的数据文件。处理的主要过程如下：3.1、新建同名的数据库。1、 新建数据库时，要求数据文件目录、路径、数据文件大小要和原来保持一致。如果是恢复到其他机器，要保证SQLServer实例的版本和原来保持一致，小版本也要保持一致，如果小版本不一致，需要设置实例和数据库兼容级别。2、 数据文件大小不能小于原来的数据文件，可以比原来的数据文件大。数据文件大小不一致时，替换数据文件时会发生错误。3、 日志文件大小可以不一致。4、 需要保持文件的file_id要和原来对应，文件的Id可以通过mster数据库系统表sys.master_files进行查看。如果包含文件组，且文件组中含有多个数据文件，直接一起创建的时候，可能会产生文件id和原来不一致的情况，这时可以采用一个一个数据文件单独创建的方式进行创建。如果文件id不一致，用备份的数据文件替换新建的数据文

件时会报错。具体错误信息如下：_?消息耳旦谢]Im技态I■蛍1厂遇到了意外的文件m所需的丈件IB为4,但从缓右小心问^让匚血煜勺旺”读取的罡7：请诵恨在gm*_g打诵映射迩竺嚳扛可以便用si5161,级别1乩狀态1・克[行遇到了感外的文件rx所需的文件：：IVJ-.仁从“F：b肛血应FFU叫M_[]g3II卄”诙印刖壬5o请繭保在=y=n址3r_fil淞中正确映月打送些文件。可以使用AlI时I■:轉巫引Im技姦H1；7遇到了砂卜的文件m所需械件ID再2-但aF：\3.iaat^on:tnr_Lo5l.liE"^J!的定X请蒯S在zy3.r.15t=r_filS3中正诵璃射了谨熨件匚可次枝用SU交件迹醉赎。厠浬交件害稱K：^c_litAttn 可龍f上谄・沽旦-I-I-药引I-狀态I-I<遇到了意外的女比IDo所需的文件ID対&，但从*F'i5qZdata\rTflonitor_Los21朮"读HS的是5。请福IS在niElet-_files中正福雀肘了逸些文件■:■可此偿用\LTEBT,交件敕舌失败•韧湮交件君珀F/.^LlxLArrP.n.-..：L^.Ll：可鼾|洎、5161-级别血舷1■免[行遇到了意外的文件皿所需的文件IDH「但从“F：h心弧曲FFfJEm_laj3.ME”读取的是Eo请确棵左=y=n皆中正輛取肘了谨咬件。可处用VTFKD；文件逾舌失血©物哩丈件呂秫F^.JidH\rrr.„-,_i,t:i.i--可議不正环。汨見7131：加引C,牯卞I,註1■■-遇到了意外的艾件口。所需的反件IDS8-但从.卩上小皿画皿伙丄平功尸虞取的是2“篩煤在=y=.r.lst6t-_£iK=中正瀬谢了这些文件a可以■使用XUEE皿：文件癒降豌”物哩文件宕秫F：治qliEAFRJ0niM『_LagLDT-[ruT正确。文件Id的查看方法：UsemasterSelect*fromsys.master_file;ridster!Il.ii加>励ii连接*鱼舍.t'国菖:jJma^012012(sql日口SrtB.SJ覇繃据库

_1数费库曲鷲

③FFhgi:«r(1K订〕

；=

土一I丈

-二i戌也

-Jh-：.n

-jh^t'1

■+-J乞：『;・：

■+_i-Ttr

■+_1乞全主S^LQriGT^L.帆L_[jtlect3^rJ5_F&li.：7l^£^J5_FolicyT=qC'/lOk/LiiiiidstTXTMTJITBTAVTHOFiimES.I7Y3T.MESSEHVICES9315ZELVICIWSZELYIClWii_J加番提氓程序审核月賠器帚樓覘范|fLl*id£LlftgULdtypftdstc日i.tUEp-aC*3dphyE.2^1」itfi0■122HITLL1LM0tAtplog1:tProgrifiFilas^LcroioftSQ匚53LHELL□ED¥3]Tiodtlile?2：'Jr■:-cronFnltsMicrosoft5^LB3 2HITLL1LM0riodallogS:tFrograiiFilas oroft刃匚TqL-JB&3-4EZ£^amB-aTTIiEC]UKDY5]临DEUb/LeI：'Jr■:-firanFaLesMicrosoft田LE：4 2&3LK6Eff3-5E3e4BLB^ESM76FCBB97FLF]LM0VEllUL^2：lLpfdgrimP^lAE'dVLCEOibftSQL.95LSILBIiE5F-aATi.-4n)3-AEDI>3115A383gDLO0EDK1PTbmiltorJJatiF:k=41db.tdPTHwdtwr_Dit4.FJDF105 2SPBBnEe-TirO-^E'ffT^F^O^JUDIiAlFeiiD%jLCK3UPP・nmior_L_LniF:VsqldaiJPF^oiuIw_Lo日.Idf1]5 3TAJC<19ADjl-9E-lB-4Zra-aZ8B-a83L3Bjl£3a060EDK1PTidultor_L_B4taJ:ksgldb-tdPTfiwdtwr_Dml.HDH125 4師4P药EtiFXFf盼9TFTFT：笑血笳5甩旳URDVSjF'P»gLicr_2_BataF：\sqldntn^F'Pf'PCiLt Hd£135 5ra5C：:SFC-61EI-4iUJ-B363-aLF：:fl5jDBUFS0ED1B1FTidult-：T_3_B4t4JAsqldft.tk\FTlfl«iitMr_Dt&.t3.Hdf1<5 &B2IftT]C4-7CgE-4B®5-BECri-P75l3Eir65ftI；3]LM0FF・mitcr_E_Lflc7:"Qmt曲FTHmut蚯亠cS.ldf1557EFEB6FFT-aFLC-im-BIXB^IEia3623495]LM0PTioq.ltor_3_LfigF i\FTlfldaiior_lo出Ldf165 SDf+ESEEO-KBl^CSl-AEiBMM-tlAlESIL4]UKnPTiKDLLtorLacI:ksqldtt曲FTHmlltwrLar.LDF3.2、用备份的数据文件替换新的数据文件1、首先停止SQLServer服务2、替换数据文件和日志文件3、启动数据库服务如果替换时，报拒绝访问错误，需要把数据文件的权限更改为everyone完全访问3.3、数据库日志修复在用备份的数据和日志文件替换新建的数据文件和日志文件后，启动数据库时，数据库自动置为inrecovery状态，这时修复的时间可能会比较长。由于日志文件3已经发生损坏，修复到损坏日志部分的时候，数据库会无法进行修复。这时可以采用如下方法处理：sp_configure'allowupdates',1reconfigurewithoverrideGo3.4、损坏的数据库日志重建1、停止数据库服务2、删除日志文件3的数据文件3、启动数据库4、查询数据库信息selectname,filenamefromsys.sysaltfileswheredbid=5;Resuits Messagesnamefilename1jPPmonitar_DataD:\PPmonitQi\PPMonito「_D京a.MDF_2_PPmonitor_LogD:IFPmonitoi\PPMonitor_Log.LDF3PPmonitor_1_DataD:IPPmcinitoi\PPnrionitcir_Datal.NDFJ-_PPmonitor_1_LogDAPPmonitoi\PPmonitor_Log1.Idf5PPmonitor_2_LogDAPPmonitoi\PPnnonitcir_Log2.ldf6PPmonitor_3_DataG:IFPmonitoi\PPmonitor_Dta13.Mdf7PPmonitor_2_DataG:IPPmonitoi\PPnnonitor_data2.Ndf8PPmonitor_3_LogGAPPmonitoi\PPmonitor_Log3.Ldl:select*fromsys.master_files;—EsiultEI刍Hessares|chi曲占EE.id「lierl「lie5jd»P=I4卩=日巴匚rialaipeceid1nameph^icaln-amem51NULL0RE51PFYnanlcr_DdteiDIPPmcnfciNPFMDniai—D日怙MDFin52EE3EFB22H219-4211-SEED•匚匸i5AF田1AILOG□R?nwnlor_LjagD:\PRnoifcirtPPMoHfcii_LiigLDFilhs3NULLQROWS1FPnwnlw_l_„D:\PFWioriteihPFTroika_D^l.NDF124E6AD2AF旳田4』153^C3SC9EE16CH381LOGnFPnwnlor_l_^DAPI^OHtohPPrraita_LoglHI1355021E3471-FETEOEflBBAEEBFDDBBD.1LOGnFPnwniior_2_..DAPI^0Hto6PFTwi:a_Log2HI145677105F4BDGOB-iEffi-AFEE^D懈DBA3.0ROWS1PPnHrilW_3_„G:\PFwiotikaNPPrrx沁o_Di咸Lfrldf15573C5Ei^89-82C1-4A115SD756EE乩.0RDW51PP™rilor_2„...GAPI^onta^PFTriotitix.dalaZ.Ncf15586EE315401E：924mDEE6D-F4E&+B3e1&-1LOG0PPnunilor』…GAPI^otita^PPrriotii:w_Lon3lLet5、重建损坏的日志文件如果数据库没有处在质疑模式，可能需要手工把数据库设置为质疑模式：sp_configure'allowupdates',0reconfigurewithoverrideGoALTERDATABASEPPmonitorSETEMERGENCYGOALTERDATABASEPPmonitorSETSINGLE_USER;alterdatabasePPmonitorrebuildlogon(Name=PPmonitor_3_Log,filename='G:\PPmonitor\PPmonitor_Log3.Ldf');»QljQ|!a«rjr^■'E■■■mAo-r^2JJ#''SQLQuBry]e.s.Lnr(S3)]'I era»!■Jb]x..itnr厉駅sp_conri^uxe1allDUupdares1,i1Lt■已亡口口壬ig1」匚已with口verrideGOselecttirure.tiLeticuretronj375,sysa1of11cjntieredbid=5.：alterdataba3i=PPmanit口匸setemergency-規TERDATABASEPPnonitorSETSINGLE_USER;iterdatabasePPmanitarfeb*uiIdlagon^Name-PPm口mt口 fiLmatYfiG:\PPtroni匕口匸1PPtfotiit.C!ii_La弓2 1);jK・WI0£ixnl_Kit|: THaIngfdi 匚血二说口'PPkDnitDr' hasbaatirrijuiIt.. 1ariCiLcon£l£C.>atiK：YhA.£ Locc.moPZSIOfJaJiaixivas broJceh.^.anil匸he： too1aiiqEt:上1曲击 aoauoar匚 anr^i«pr^nouf丄口g上l丄«£rrCymmiltiaailtoTm.o*中血匚ctiny .TTmfh.a>XLclrunPECCCH£CE-Z"Brdx^alLd^tc：pby污Jcel丄cc&fLft-^ncT.Iled^babu±hasbeexiput211dlaa-onlyiQode.Uh^tiyouetereadytoDLaksthe ■日bx亡airai1eib丄上torust,■7™inlltieed七口fe-setdatabaseoptiomsa»ddeleteanye-itrolog1files.6、alterdatabasePPmonitorsetonline;7、alterdatabasePPmonitorsetmulti_usergo这时数据库中的表已经能够正常访问，由于某些表仍然存在一致性问题，在访问时会报错可能的报错信息如下：5...vdoT(SZlJ*■ s...rt+otir(E-3))SULUueftS-s„..c^nnKcted.*_[dataT]X[厲曰匚旦日][data9]j[daraD][data_sjx[djev1ceno][InsctrtedDete]u[plisse][BUM]-7P.OK[PFTnatllCOr].[dbCi]” 口9]aelectCOUNT|T)fromReVievi2B.e3lut_10;electCOUNT|*)工工codReViewPjsReslUL?selecc匚OUNTI*)XxcouJLcilustPucicrrsuriTDK654Z,-斗I I3恥沁x白血心歼|TC石g£D£rL^valZ1f£匚oxo3HLIjja1JU匚工onp匚ea亡总匚uhLa^ical壬斛。<2:55)Lttda.Tahfl.-=4£fa.llc>d.1c.L>aloti^c:匚□Allacajc.ionLinir72057ES405ZEDSQQ口畀口匚c.a3^222lDLO^^B口勻首.Jj_J消息'表勰音i岂油焉:按i.i/■■■!■'J'J-索弓iti-i'L"I-. i.ih.'..分禹单元idTmggmini^-.：...h.1,-,i[t=,ii026)o谡页可老融,或宵页头中可龍包含轧J:r.^■'-处引"-1-.L1•云：L栅艮呑碑鳧込按11(■■■■-'I'!■^=11：1. ■:-khi"■n：'1-n：:. ■ ■■qj单二i f.-:. mi•• hi I土埜77 -..h.i--i(r= i i：：""齒忍5gp中匸为电di说■■::■织别⑹状选n圭i行去错艮呑侍0濟込擄II/•■•::.'I'I-^=1IE1. ■:'kIII■'■：.^：-J"+-H：:. . '■■'^'单二I 川：，■.已■111 吐±77 -..H.1--l(r= I l：：-'8)vp；f7=^-v1-详=7中u为电占iiR.t2::■级别16-状态八京I行狒艮百jUjSH述忖II{'■•::.'I'I-^=|IE1.■:-kHI-.!./!.,^'4.11：!..■：■'^单••I!.■.!!!■/II-'111\±^-~-. .1--1^.=Il：：.'9)v V1'氓="中U为电占iiTOC\o"1-5"\h\z•咗■'::■咲引I—1-1-1■.^'(T _ _十油1石伸如曲月鶴I'IV：-■:■-.1I-車引IEI./■EIII"■■..f...iy4-II；:..m：单77r?:■:.■■-.I-：-7IIJHJuI±3^y.■-■.h.1--ifr~' 节；775■士兀•:r.或者二y冲U总也出L■■■；■膜引IGI■.^LIf __ _+iH：r'百T?(诒貯卞廉i■'vh-.i-.-.i■!■^p|ili.性匚in"hvh-1：-1ikh-.性耳单=r?：-h--.r：--7n■■-m d--.-ifr~'i曲宀谕jfh牝元哼■或击二;丿“叵能也含ti'R.e、»■；.词ih,肚色I,土l计熬错逞:对矗1JYbTJz^'J.'J：室弓ID1.卅区LDT205T阴42342O3H5E,曲03单元IU720^7554237091340.型生丫In-rm亦5“索引节占石(7:43ITSCEl,(fi«.:5梧向子页(L1325)和上7V.AiiQ3-.Lot1YU:Jt-:lJUL _llIO'-r-Tjl jl3.5、数据库的一致性修复1、把数据库置为紧急和单用户模式reconfigurewithoverrideGoALTERDATABASEPPmonitorSETEMERGENCYGOALTERDATABASEPPmonitorSETSINGLE_USER;2、修复数据库中存在的错误DBCCCheckDB(PPmonitor,REPAIR_ALLOW_DATA_LOSS)修复完成后，有部分报错，具体如下：sg259,Level16,State1,Line1Adhocupdatestosystemcatalogsarenotallowed.Msg2510,Level16,State17,Line1DBCCcheckdberror:Thissystemtableindexcannotberecreated.DBCCresultsfor'PPmonitor'.Repair:Thepage(1:3960704)hasbeendeallocatedfromobjectID0,indexID-1,partitionID0,allocunitID72057594284408832(typeUnknown).Repair:Thepage(1:4088798)hasbeendeallocatedfromobjectID0,indexID-1,partitionID0,allocunitID72057594284408832(typeUnknown).Repair:Thepage(1:4089144)hasbeendeallocatedfromobjectID0,indexID-1,partitionID0,allocunitID72057594284408832(typeUnknown).CHECKDBfound12638allocationerrorsand0consistencyerrorsintable'BarcodeReslut'(objectID1966682104).CHECKDBfixed12638allocationerrorsand0consistencyerrorsintable'BarcodeReslut'(objectID1966682104).Repair:IAMchainforobjectID2000166321,indexID1,partitionID72057594193575936,allocunitID72057594196459520(typeIn-rowdata),hasbeentruncatedbeforepage(1:210)andwillberebuilt.Repair:Theextent(6:448)hasbeenallocatedtoobjectID2000166321,indexID1,partitionID72057594193575936,allocunitID72057594196459520(typeIn-rowdata).Repair:Page(7:4613336)nextand(0:0)previouspointershavebeensettomatcheachotherinobjectID2000166321,indexID1,partitionID72057594193575936,allocunitID72057594196459520(typeIn-rowdata).Msg2575,Level16,State1,Line1TheIndexAllocationMap(IAM)page(1:210)ispointedtobythenextpointerofIAMpage(7:4613336)inobjectID2000166321,indexID1,partitionID72057594193575936,allocunitID72057594196459520(typeIn-rowdata),butitwasnotdetectedinthescan.Theerrorhasbeenrepaired.CHECKDBfound1allocationerrorsand0consistencyerrorsintable'TestVoltage'(objectID2000166321).CHECKDBfixed1allocationerrorsand0consistencyerrorsintable'TestVoltage'(objectID2000166321).Msg8913,Level16,State3,Line1Extent(7:616)isallocatedto'dbo.ZModul'andatleastoneotherobject.Theerrorhasbeenrepaired.CHECKDBfound1allocationerrorsand0consistencyerrorsintable'ZModul'(objectID2121110647).CHECKDBfixed1allocationerrorsand0consistencyerrorsintable'ZModul'(objectID2121110647).CHECKDBfound470285allocationerrorsand9consistencyerrorsindatabase'PPmonitor'.CHECKDBfixed470259allocationerrorsand8consistencyerrorsindatabase'PPmonitor'.repair_allow_data_lossistheminimumrepairlevelfortheerrorsfoundbyDBCCCHECKDB(PPmonitor,repair_allow_data_loss).DBCCexecutioncompleted.IfDBCCprintederrormessages,contactyoursystemadministrator.3、表分配检查USE[PPmonitor];DBCCCheckAlloc();DBCCresultsfor'PPmonitor'.Msg8904,Level16,State1,Line2Extent(6:525720)indatabaseID5isallocatedbymorethanoneallocationobject.Msg8913,Level16,State1,Line2Extent(6:525720)isallocatedto'GAM'andatleastoneotherobject.Msg8904,Level16,State1,Line2Extent(6:582336)indatabaseID5isallocatedbymorethanoneallocationobject.Msg8913,Level16,State1,Line2Extent(6:582336)isallocatedto'GAM'andatleastoneotherobject.Msg8904,Level16,State1,Line2Extent(6:606600)indatabaseID5isallocatedbymorethanoneallocationobject.Msg8913,Level16,State1,Line2Extent(6:606600)isallocatedto'GAM'andatleastoneotherobject.data),indexextents2089,pages16491,mixedextentpages6.ObjectID2121110647,indexID10,partitionID72057594202947584,allocunitID

72057594205765632(typeIn-rowdata),indexextents945,pages7403,mixedextentpages15.ObjectID2138594807,indexID1,partitionID72057594173194240,allocunitID72057594175946752(typeIn-rowdata),dataextents0,pages3,mixedextentpages3.Thetotalnumberofextents=1938384,usedpages=15430706,andreservedpages=15507106inthisdatabase.(numberofmixedextents=702,mixedpages=5650)inthisdatabase.CHECKALLOCfound58allocationerrorsand0consistencyerrorsindatabase'PPmonitor'.DBCCexecutioncompleted.IfDBCCprintederrormessages,contactyoursystemadministrator.4、查询测试查询指定条件时报错，只能全部按顺序扫描，即只能使用select*fromtable使用selectcount(*)fromtable报错，具体报错如下：Attempttofetchlogicalpage(1:229734)indatabase5failed.Itbelongstoallocationunit72057594251182080notto72057594275561472.消息605,级别21,状态3,第2行尝试在数据库5中提取逻辑页(1:1824)失败。该逻辑页属于分配单元72057594194165760，而非72057594237091840。5、表无损修复把数据启动到单用户模式下ALTERDATABASEPPmonitorSETsingle_user;GO对出错的表进行修复DBCCCheckTable(AdjustData_7878_N,REPAIR_REBUILD);AdjustData_7878_N的DBCC结果。修复已为数据库'PPmonitor'中的对象'dbo.AdjustData_7878_N'成功地重新生成了Clustered索引。消息8945,级别16,状态1,第1行表错误•将重新生成对象ID368876531，索引ID1。该错误已修复。消息8976,级别16,状态1,第1行表错误•对象ID368876531,索引ID1,分区ID72057594241220608,分配单元ID72057594244825088(类型为In-rowdata)。在扫描过程中未发现^(3:656)，但该页的父级(7:4810173)和上一页(7:4850791)都引用了它。请检查以前的错误消息。该错误已修复。消息8980,级别16,状态1,第1行表错误•对象ID368876531,索引ID1,分区ID72057594241220608,分配单元ID72057594244825088(类型为In-rowdata)。索引节点页(7:4810173),槽617指向子页(3:657)和上一子页(3:656)，但未遇到这些页。该错误已修复。消息8980,级别16,状态1,第1行表错误•对象ID368876531,索引ID1,分区ID72057594241220608,分配单元ID72057594244825088(类型为In-rowdata)。索引节点页(7:4810173),槽618指向子页(3:658)和上一子页(3:657),但未遇到这些页。该错误已修复。消息8980，级别16，状态1，第1行表错误:对象ID368876531，索引ID1，分区ID72057594241220608，分配单元ID72057594244825088(类型为In-rowdata)。索引节点页(7:4810173),槽619指向子页(3:659)和上一子页(3:658)，但未遇到这些页。该错误已修复。消息8980，级别16，状态1，第1行表错误•对象ID368876531,索引ID1,分区ID