I-DBCC CHECKDB: IsiKhokelo esiGqibeleleyo se-MS SQL Server Impilo yeDatha (2025)

Database urhwaphilizo yonke SQL Server iphupha elibi lomlawuli. Xa idatha yeshishini ebalulekileyo ingafikeleleki okanye ingathembeki, i-cost kunokuba buhlungu. Esi sikhokelo sibanzi sibandakanya yonke into ekufuneka uyazi malunga nokusebenzisa i-DBCC CHECKDB ukugcina impilo yesiseko sedatha kunye nokuthintela urhwaphilizo, kunye nezisombululo eziphuculweyo zokubuyisela xa izixhobo eziqhelekileyo zingonelanga.

1. Ukubaluleka kwe SQL Server Database Health

1.1 Yintoni uRhwaphilizo kuVimba weeNkcukacha Costs Amashishini

Namhlanje, uMost amashishini agcina idatha yawo ebalulekileyo koovimba beenkcukacha. Xa urhwaphilizo lwesiseko sedatha lusenzeka, iziphumo ziba yintlekele:

• Ilahleko yemali i-avareji ye-2.3 yezigidi zeedola ngonyaka ngenxa yelahleko yedatha, kunye nokusilela kwehardware kunye norhwaphilizo ngoonobangela abaphambili (EMC Corporation)
• Amaxabiso okuvalwa kweshishini bonisa ukuba i-50% yamashishini amancinci afumana ilahleko yedatha ngenxa yokungaphumeleli kwe-hardware aphume kwishishini kwiminyaka emibini, ngelixa i-94% yamashishini anelahleko yedatha eyintlekele ayiphili konke konke.
• Ubuqhetseba bokonakala kwedatha ichaphazela i-20% yezicelo ezibalulekileyo zobuthunywa ngonyaka, ezibangela ukuphazamiseka kokuqhubeka kweshishini (uphando lukaGartner)
• urhwaphilizo olunxulumene nehardware I-akhawunti ye-67% yazo zonke iziganeko zelahleko yedatha ngokuphazamiseka kwe-hard drive kunye nokusilela kwenkqubo, kunye ne-40% yelahleko yedatha ebangelwa ngokuthe ngqo kukungasebenzi kakuhle kwehardware.
• Urhwaphilizo lweSoftware costs ukusuka kumawaka ukuya kwizigidi zeerandi ngokuxhomekeke kubungqongqo kunye nobubanzi, kunye ne-82% yamashishini afumana ukungabikho okungacwangciswanga apho urhwaphilizo lwalungunobangela ophambili.

1.2 Kutheni uHlolo lweMpilo lwarhoqo lubalulekile

Abantu bafuna ukuxilongwa rhoqo ukuze babone izifo ezinokubakhona kwangethuba. Ngokufanayo, oovimba beenkcukacha bafuna ukujongwa rhoqo kwezempilo:

1. Khangela urhwaphilizo olunokubakho kwangethuba kwaye ulusingathe ngokukhawuleza, uthintele iingxaki ukuba zingabi mbi kwaye zisasazeke, nto leyo enokukhokelela kwiziphumo ezibi kwishishini.
2. Qinisekisa ukuba uvimba weenkcukacha usebenza ngokugqibeleleyo.
3. Inkqubo cost Ukuhlolwa kwempilo yesiseko sedata esebenzayo isezantsi kakhulu kunokubuyiselwa kwedatha esebenzayo emva kwentlekele yedatabase.

1.3 Intshayelelo kwiMiyalelo yeMfezeko yeDatha

SQL Server inikeza imiyalelo eyakhelwe-ngaphakathi emininzi yokugcina impilo yedatabase, nge DBCC CHECKDB esebenza njenge most isixhobo sokujonga ingqibelelo esibanzi sikhoyo. Le miyalelo isebenza kunye ukuqinisekisa iinkalo ezahlukeneyo zesakhiwo sakho sesiseko sedatha, ukusuka kwiitheyibhile ezizimeleyo ukuya kwisiseko sedatha epheleleyo, ukwenza isicwangciso esipheleleyo sokugcinwa esigcina idatha yakho ikhuselekile kwaye ifikeleleke.

2. Yintoni iDBCC CHECKDB

DBCC CHECKDB is SQL ServerIsixhobo esingundoqo sokuqinisekisa imfezeko yedatabase kunye nokuchonga imiba yorhwaphilizo.

• Yingxelo ye-T-SQL, kungekhona isixhobo se-GUI.
• Ungayiphumeza ngeendlela eziqhelekileyo, ezinje SQL Server Isitudiyo soLawulo(SSMS), SQL Server Iarhente, SQLCMD, njl.

2.1 Eyona nto ijongwa yi-CHECKDB kuVimba wakho weenkcukacha

Xa usebenzisa i-DBCC CHECKDB, umyalelo wenza ungqinisiso oluninzi kuyo yonke isiseko sesiseko sakho:

• Ukuqinisekiswa kwephepha lokutshekisha ukubona urhwaphilizo olubonakalayo kunye nemiba enxulumene nehardware
• Ukuqinisekiswa kokuhambelana kwesalathisi ukuqinisekisa ukufunyanwa kwedatha eyiyo kunye nokusebenza kwemibuzo
• Iitshekhi zesakhiwo solwabiwo ukuqinisekisa ukusetyenziswa kwendawo echanekileyo kunye nokwabiwa kwephepha
• Uvavanyo lwentembeko yereferensi phakathi kweetheyibhile ezinxulumeneyo kunye nobudlelwane obuphambili bangaphandle
• Inkqubo yokuqinisekisa ukuhambelana kwetafile ukuqinisekisa SQL ServerImetadata yangaphakathi ihlala ithembekile
• Ukuqinisekiswa koqhagamshelwano lwekhasi ledatha ukuqinisekisa ingqibelelo yekhonkco lephepha elifanelekileyo
• Ukungqinelana kweschema sesiseko sedatha ukuqinisekisa iinkcazo zezinto kunye nokuxhomekeka

Olu vavanyo lubanzi lubandakanya idatha yomsebenzisi kunye nolwakhiwo lwenkqubo, lubonelela ngokubonakala okupheleleyo kwimeko yempilo yesiseko sedatha yakho.

3. Ukuqhuba iDBCC CHECKDB: Inyathelo ngenyathelo

3.1 Izinto eziyimfuneko

Apha ngezantsi luluhlu lokutshekisha phambi kokwenza nawuphi na umsebenzi weDBCC CHECKDB:

• Gqibezela ugcino lwedatabase -Yenza ugcino olupheleleyo ngaphambi kokuba ujonge ingqibelelo njengenetha yakho yokhuseleko ukuba urhwaphilizo lufunyenwe okanye umsebenzi wokulungisa uyimfuneko.
• Iimvume ezifanelekileyo -Udinga iimvume ze-sysadmin okanye ze-db_owner ukwenza imiyalelo ye-DBCC CHECKDB
• Imithombo yenkqubo eyaneleyo:
  • Imemori: 25% yesayizi yedatha
  • Indawo yeTempdb: 10-15% yobungakanani bedatabase
  • CPU: 50-70% ukufumaneka ngexesha lokugcinwa
  • I/O: Lindela imisebenzi yokufunda enzima
• Ukufikeleleka kweDatabase -Qinisekisa ukuba uvimba wakho wedatha uyafikeleleka kwaye akukho kwimeko yothintelo, njengoko i-CHECKDB ifuna ufikelelo lokufunda kuwo onke amaphepha edatha

3.2 Umyalelo osisiseko

Most Umyalelo osisiseko weDBCC CHECKDB uquka izinto ezintathu ezahlukeneyo eziqhelekileyo:

(1) Jonga uvimba weenkcukacha wangoku (akukho parameters):

DBCC CHECKDB

(2) Jonga uvimba weenkcukacha ngegama:

DBCC CHECKDB ('YourDatabaseName')

(3) Jonga uvimba weenkcukacha nge-ID:

DBCC CHECKDB(5)  -- Replace 5 with your database ID

Lo myalelo usisiseko wenza uqwalaselo olupheleleyo lwemfezeko yedatabase ekhankanyiweyo, iphonononga zonke iitafile, izalathisi, kunye nezakhiwo zesixokelelwano. Kumaziko edatha anamagama asemgangathweni angenazithuba, ungashiya ucaphulo. Umyalelo uya kusebenza de ugqitywe, ubonisa imiyalezo yenkqubela phambili kunye neziphumo zokugqibela. Esi sintaksi sisisiseko sisebenza ngokugqibeleleyo kugcino-lwazi oluncinane okanye xa unexesha elaneleyo lokulondoloza elikhoyo.

Apha ngezantsi kukho umfanekiso wekhusi wokuqhuba iDBCC CHECKDB ngaphakathi SQL Server Isitudiyo soLawulo (SSMS):

3.3 Gqibezela uKhetho

Ngezantsi lukhetho olupheleleyo lweDBCC CHECKDB:

udidi	option	inkcazelo	Umzekelo weDBCC CHECKDB
Iinketho yokulungisa	REPAIR_REBUILD	Ukulungisa ngaphandle kwelahleko yedata (umzekelo, isalathisi ngokutsha)	DBCC CHECKDB ('MyDB', REPAIR_REBUILD)
	REPAIR_FAST	Akukho ukulungiswa. Ukuhambelana komva kuphela	DBCC CHECKDB ('MyDB', REPAIR_FAST)
	REPAIR_ALLOW_DATA_LOSS	Ukulungisa zonke iimpazamo (kunokubangela ilahleko yedatha)	DBCC CHECKDB ('CorruptDB', REPAIR_ALLOW_DATA_LOSS)
Ulawulo loMda	NOINDEX	Utsiba ujongo lwesalathiso olungadityaniswanga	DBCC CHECKDB ('LargeDB', NOINDEX)
	PHYSICAL_ONLY	Ijonga kuphela ingqibelelo yogcino lomzimba (amaphepha/iirekhodi)	DBCC CHECKDB ('ProdDB', PHYSICAL_ONLY)
	DATA_PURITY	Kujongwa iimpazamo zexabiso lomhlathi wengqiqo (umzekelo, imihla engasebenziyo)	DBCC CHECKDB ('OldDB', DATA_PURITY)
	EXTENDED_LOGICAL_CHECKS	Ukuhlolwa okunzulu okunengqondo (iimbono ezinesalathiso, XML/izalathisi zendawo)	DBCC CHECKDB ('ComplexDB', EXTENDED_LOGICAL_CHECKS)
Ulawulo lweMveliso	ALL_ERRORMSGS	Ibonisa zonke iimpazamo (okungagqibekanga: 200 into nganye)	DBCC CHECKDB ('MyDB', ALL_ERRORMSGS)
	NO_INFOMSGS	Ifihla imiyalezo yolwazi	DBCC CHECKDB ('MyDB', NO_INFOMSGS)
ukwenza	TABLOCK	Isebenzisa izitshixo zetafile (inciphisa ukusetyenziswa kweTempDB kodwa iibhloko ziyabhala)	DBCC CHECKDB ('BigDB', TABLOCK)
	MAXDOP = number	Uthathela indawo useto lwe-parallelism	DBCC CHECKDB ('MyDB', MAXDOP = 2)
Usebenziso	ESTIMATEONLY	Uqikelelo lwesithuba seTempDB esifunekayo. (akukho tsheki yokwenyani)	DBCC CHECKDB ('MyDB', ESTIMATEONLY)

4. Ukuqonda Iziphumo Zakho

I-DBCC CHECKDB iya kuvelisa iziphumo ezahlukeneyo ngokusekelwe ekubeni ukuphunyezwa kwayo kugqiba ngempumelelo okanye hayi. Makhe sizichaze ngokweenkcukacha.

4.1 Uphumezo lwe-CHECKDB Lugqitywe ngempumelelo

Ukuba ukuphunyezwa kweDBCC CHECKDB kugqiba ngempumelelo, iya kuxela iintlobo ezahlukeneyo zeziphumo ngokuxhomekeke kwimeko yempilo yedatabase yakho.

4.1.1 Akukho Miba ifunyenweyo

Ukuba i-DBCC CHECKDB ayifumani nayiphi na imiba, uya kubona imveliso efana nale:

CHECKDB found 0 allocation errors and 0 consistency errors in database 'YourDatabase'.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

Esi siphumo sibonisa ukuba idatabase yakho igcina ingqibelelo egqibeleleyo kuzo zonke izakhiwo ezikhangelweyo.

4.1.2 Kufunyenwe iimpazamo zoRhwaphilizo

Nanini na i-DBCC CHECKDB ibona impazamo yorhwaphilizo, iya kuxela umyalezo wemposiso ngolu lwakhiwo lulandelayo:
IsiKhokelo seNqanaba loBungqongqo:

• Inqanaba 16-19: Iimpazamo ezilungiswayo ngabasebenzisi, amaxesha amaninzi urhwaphilizo oluncinci
• Inqanaba 20-24: Iimpazamo zenkqubo, urhwaphilizo olubi olufuna ingqalelo ekhawulezileyo
• Inqanaba loku-25: Iimpazamo ezibulalayo, i-database inokungafumaneki

Iimpazamo eziqhelekileyo ziquka:

• Ukusilela kwephepha lokutshekisha (umyalezo 824)
• Iimpazamo zolwabiwo (umyalezo 8928)
• Izalathisi iingxaki zokungaguquguquki (umyalezo 8964)

Ukuqonda isakhiwo somyalezo kunceda ukubeka phambili izenzo zokuphendula kunye nokumisela izicwangciso ezifanelekileyo zokubuyisela.

4.1.3 Ulwazi oluqhelekileyo kunye neMiyalezo yezilumkiso

Ayizizo zonke iziphumo ze-DBCC CHECKDB ezibonisa iingxaki ezinzulu. Isenokukhupha imiyalezo yolwazi kunye nesilumkiso, kuquka:

• Iingxelo zokulungisa -Imiyalezo ecebisa imiyalelo yokulungisa ukulungisa imiba emincinci
• Izilumkiso zokwabiwa – Izilumkiso malunga nokwabiwa kwendawo ezingachaphazeli ukufikelela kwidatha
• Iingcebiso zokusebenza -Iingcebiso malunga nokugcinwa kwesalathisi kunye nokwenza ngcono
• Izaziso zolwazi – Imiyalezo yesimo ngokubanzi engadingi ntshukumo yangoko nangoko

Le miyalezo ibonelela ngesikhokelo sogcino oluxabisekileyo ngelixa isahlula phakathi korhwaphilizo olubalulekileyo olufuna inyathelo elikhawulezileyo kunye nemiba emincinci enokuthi iqwalaselwe ngexesha lokulondolozwa kwefestile rhoqo.

Umzekelo womyalezo wesilumkiso:

DBCC results for 'InventoryDatabase'.
Msg 2570, Level 16, State 3, Line 1
Page (2:8452), slot 17 in object ID 485577333, index ID 0, partition ID 72057594038845456, 
alloc unit ID 72057594042515968 (type "In-row data").
Column "ProductPrice" value is out of range for data type "decimal". Update column to a legal value.
There are 45892 rows in 1247 pages for object "Products".
CHECKDB found 0 allocation errors and 1 consistency errors in table 'Products' (object ID 485577333).
CHECKDB found 0 allocation errors and 1 consistency errors in database 'InventoryDatabase'.

4.2 Ukulahlwa kwe-CHECKDB

Ukuba i-CHECKDB iyalahlwa ngexesha lokuphunyezwa kwayo ngenxa yezizathu ezahlukeneyo, iya kuxela umyalezo wemposiso kwaye yongeze impazamo yelogi ngekhowudi yelizwe engezantsi:

imeko	inkcazelo
0	Inombolo yempazamo engu-8930 yaphakanyiswa. Oku kubonisa urhwaphilizo kwimetadata ephelise umyalelo weDBCC.
1	Inombolo yempazamo engu-8967 yaphakanyiswa. Bekukho impazamo yangaphakathi yeDBCC.
2	Ukusilela kwenzeke ngexesha lolungiso lwesiseko sedatha.
3	Oku kubonisa urhwaphilizo kwimetadata ephelise umyalelo weDBCC.
4	Ukubanga okanye ukwaphulwa kofikelelo kuchongiwe.
5	Kwenzeke imposiso engaziwayo evale umyalelo weDBCC.

Umzekelo womyalezo wemposiso:

Failed:(-1073548784) Executing the query "DBCC CHECKDB('InventoryDB') WITH NO_INFOMSGS" failed with the following error: "There is insufficient system memory to run this query.Check terminated. A failure was detected while collecting facts. Possibly tempdb out of space or a system table is inconsistent. Check previous errors.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

or

2024-11-18 09:52:41.38 spid35 I/O error (bad page ID) detected during read at offset 0x00000024886000 in file 'C:\Data\MSSQL\DATA\SalesDatabase.mdf'.

Umzekelo welogi yempazamo:

11/15/2024 09:23:17,spid52,Unknown,DBCC CHECKDB (SalesDatabase) WITH all_errormsgs no_infomsgs executed by CORP\dbadmin terminated abnormally due to error state 3. Elapsed time: 1 hours 32 minutes 18 seconds.

Kwimeko enjalo, ungazama ezinye iindlela eziphambili ezifana DataNumen SQL Recovery ukulungisa urhwaphilizo kwidatabase yakho.

5. Ukulungisa iimpazamo zoRhwaphilizo

5.1 I-Backup kunye noBuyiselo: Ulungiso oluKhuselekileyo

Xa i-DBCC CHECKDB ichonga iimpazamo zorhwaphilizo, ukubuyisela kwi-backup ecocekileyo kubonisa eyona ikhuselekileyo kwaye m.ost isisombululo esithembekileyo. Le ndlela iqinisekisa imfezeko yedatha ngelixa isusa oonobangela borhwaphilizo. Ngaphambi kokubuyisela, qinisekisa ukugcinwa kwemfezeko usebenzisa BUYISELWA NGOKUQINISEKILEYO imiyalelo, kwaye uqwalasele iinketho zokubuyisela inqaku-kwixesha ukunciphisa ilahleko yedatha. Bhala iinkcukacha zorhwaphilizo zohlalutyo lwengcambu, njengoko imiba yehardware okanye ibugs zesoftware inokufuna uqwalaselo olongezelelweyo ukunqanda ukuphinda.

5.2 Izisombululo zoRhwaphilizo zeNqanaba lePhepha

Ngorhwaphilizo lwephepha elisecaleni elichaphazela izahlulo ezincinci zedatha, SQL Server Ushicilelo loShishino lunikeza amandla okubuyisela iphepha alungisa amaphepha awonakeleyo ngaphandle kokubuyiselwa kwedatha epheleleyo. Obu buchule buhamba phambili bufuna imodeli epheleleyo yokubuyisela kunye nee-backups zelogi zangoku.

Inyathelo ngenyathelo inkqubo yokubuyisela iphepha:

1. Chonga iphepha elonakeleyo ukusuka kumyalezo wemposiso ye-CHECKDB (umzekelo, iphepha 1:256)
2. Thatha ugcino lwelogi lwangoku ukubamba iintengiselwano zakutsha nje:

BACKUP LOG YourDatabase TO DISK = 'C:\Backups\YourDB_Log.trn'

3. Buyisela iphepha elonakeleyo ukusuka kwi most ugcino olupheleleyo lwamva nje:

RESTORE DATABASE YourDatabase PAGE = '1:256' 
FROM DISK = 'C:\Backups\YourDB_Full.bak'

4. Faka i-backup eyahlukileyo (ukuba ikhona):

RESTORE DATABASE YourDatabase PAGE = '1:256' 
FROM DISK = 'C:\Backups\YourDB_Diff.bak'

5. Faka zonke ii-backups zelogi ngolandelelwano, ukuquka nale isanda kwenziwa:

RESTORE LOG YourDatabase FROM DISK = 'C:\Backups\YourDB_Log1.trn'
RESTORE LOG YourDatabase FROM DISK = 'C:\Backups\YourDB_Log2.trn'
-- Continue for all log backups in order
RESTORE LOG YourDatabase FROM DISK = 'C:\Backups\YourDB_Log.trn'

6. Thatha i-backup yokugqibela kwaye ubuyisele ukuzisa iphepha langoku:

BACKUP LOG YourDatabase TO DISK = 'C:\Backups\YourDB_Final.trn'
RESTORE LOG YourDatabase FROM DISK = 'C:\Backups\YourDB_Final.trn'

Enye idata engabalulekanga: Ukuba urhwaphilizo luchaphazela idatha engabalulekanga, ungathumela ngaphandle imiqolo engachaphazelekiyo kwiitafile ezintsha ngaphambi kokwakha kwakhona izakhiwo ezonakeleyo:

-- Export good data to a new table
SELECT * INTO YourTable_Backup 
FROM YourTable 
WHERE NOT EXISTS (SELECT 1 FROM corrupt_page_list WHERE page_id = target_page)

-- Drop and recreate the corrupted table
DROP TABLE YourTable
-- Recreate table structure and reload clean data

5.3 Isalathiso soRhwaphilizo loLungiso oluKhawulezayo

Urhwaphilizo lwesalathiso luhlala luphendula kakuhle kulwakhiwo ngokutsha lwemisebenzi oluphinda lwenze izalathisi izakhiwo ngaphandle kokuchaphazela idatha yetheyibhile esisiseko:

ALTER INDEX ALL ON YourTable REBUILD

Le ndlela yokusebenza isebenza kakuhle ngakumbi kurhwaphilizo lwesalathiso olungadityaniswanga, njengoko ulwakhiwo ngokutsha luvelisa amaphepha esalathiso asuka kwidatha yetheyibhile yomthombo, ngokufanelekileyo kuphelisa urhwaphilizo ngelixa lugcina lonke ulwazi lwangaphambili.

6. Sebenzisa iREPAIR_REBUILD kunye neREPAIR_ALLOW_DATA_LOSS

Ukuba iindlela zangaphambili zonke ziyasilela okanye azinakwenzeka, ungasebenzisa i REPAIR_REBUILD kunye ne REPAIR_ALLOW_DATA_LOSS iinketho ukulungisa uvimba wedata.

6.1 REPAIR_REBUILD (Ukhetho oluKhuselekileyo):

• Sebenzisa ngenxa: Urhwaphilizo lwesalathisi kunye neempazamo ezincinci zolwabiwo
• Ukhuseleko lwedatha: Izama ukulungisa urhwaphilizo ngaphandle kokucima idatha
• Inqanaba lomngcipheko: Phantsi - akukho lahleko yedatha elindelekileyo
• Iimeko eziqhelekileyo: Urhwaphilizo lwesalathiso olungadityaniswanga, imiba emincinci yemetadata
• Umyalelo umzekelo: DBCC CHECKDB('YourDB', REPAIR_REBUILD)

6.2 REPAIR_ALLOW_DATA_LOSS (iNdawo yokuGqibela yokuGqibela):

• Sebenzisa ngenxa: Urhwaphilizo olukhulu xa ii-backups zingekho
• Ukhuseleko lwedatha: Ngamana ukucima idatha eyonakeleyo ukubuyisela ukusebenza kwesiseko sedatha
• Inqanaba lomngcipheko: Ephakamileyo - ilahleko yedatha esisigxina enokwenzeka
• Iimeko eziqhelekileyo: Urhwaphilizo lwephepha, umonakalo wetafile yenkqubo, iimpazamo zekhonkco lolwabiwo
• Umyalelo umzekelo: DBCC CHECKDB('YourDB', REPAIR_ALLOW_DATA_LOSS)

6.3 IiNdlela eziNgcono kakhulu kwezi ziKhetho:

• Hlala uvavanya ukulungisa imisebenzi kwiikopi zedathabheyisi xa kunokwenzeka
• Hlala uxhasa phambi kokuba usebenzise olu khetho
• Bhala zonke iinguqu ukuthobela kunye neenjongo zokusombulula ingxaki
• Cwangcisa ugcino lwedatha kwimowudi yomsebenzisi omnye phambi kokuqhuba imisebenzi yokulungisa

Ngokuqhelekileyo, kufuneka sizame REPAIR_REBUILD ukhetho kuqala. Ukuba ayiphumelelanga, zama REPAIR_ALLOW_DATA_LOSS ukhetho.

6.4 REPAIR_ALLOW_DATA_LOSS Iziphumo

6.4.1 Ukulungisa kuPhumelele ngokuLahleka kweeDatha

Ngamanye amaxesha REPAIR_ALLOW_DATA_LOSS ukhetho luyakuphumelela, kodwa enye idata yi lost emva kokulungiswa.

Ngezantsi yimizekelo yemizekelo ethile:

CHECKDB found 0 allocation errors and 103 consistency errors in database ‘SalesDatabase’.
CHECKDB fixed 0 allocation errors and 103 consistency errors in database ‘SalesDatabase’.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

Msg 8909, Level 16, State 1, Line 8
Table error: Object ID 0, index ID -1, partition ID 0, alloc unit ID 45035996309880832 (type Unknown), page ID (1:553) contains an incorrect page ID in its page header. The PageId in the page header = (0:0).
The error has been repaired.
Msg 8939, Level 16, State 98, Line 8
Table error: Object ID 0, index ID -1, partition ID 0, alloc unit ID 111464090777419776 (type Unknown), page (0:0). Test (IS_OFF (BUF_IOERR, pBUF->bstat)) failed. Values are 2057 and -1.
Could not repair this error.

Oku kungenxa yokuba i-DBCC CHECKDB ilungisa uvimba weenkcukacha ngokulahla iirekhodi ezonakeleyo, kodwa eneneni, m.ost yazo isenokubuyiselwa nge DataNumen SQL Recovery.

Iifayile zesampuli:

SQL Server Inguqulelo	Ifayile ye-MDF eyonakele	Ifayile ye-MDF ilungiswe ngu DataNumen SQL Recovery
SQL Server 2014	Imposiso10_1.mdf (Msg 8909 elandelwa nguMsg 8939) (iirekhodi ezingama-600 lost nge REPAIR_ALLOW_DATA_LOSS)	Imposiso10_1_fixed.mdf (Akukho rekhodi lost)
SQL Server 2014	Imposiso10_2.mdf (Msg 8909 elandelwa nguMsg 8939) (6000 iirekhodi(50%) lost nge REPAIR_ALLOW_DATA_LOSS)	Imposiso10_2_fixed.mdf (Iirekhodi ezili-100 kuphela lost)
SQL Server 2014	Impazamo7.mdf (100 iirekhodi lost nge REPAIR_ALLOW_DATA_LOSS)	Imposiso7_ilungisiwe.mdf (Irekhodi enye kuphela lost)

6.4.2 Ukulungisa kusilele – Qwalasela isiSombululo sobuNgcali

If REPAIR_ALLOW_DATA_LOSS ayiphumelelanga, izakukhupha imposiso enye okanye imiyalezo emininzi.

Ngezantsi kukho imizekelo:

DBCC results for ‘MyDatabase’.
CHECKDB found 0 allocation errors and 0 consistency errors in database ‘MyDatabase’.
Msg 824, Level 24, State 2, Line 8
SQL Server detected a logical consistency-based I/O error: incorrect checksum (expected: 0xea8a9a2f; actual: 0x37adbff8). It occurred during a read of page (1:28) in database ID 39 at offset 0x00000000038000 in file ‘MyDatabase.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.

Msg 7909, Level 20, State 1, Line 8
The emergency-mode repair failed.You must restore from backup.

Msg 8992, Level 16, State 1, Line 8
Check Catalog Msg 3852, State 1: Row (object_id=69) in sys.objects (type=S ) does not have a matching row (object_id=69,column_id=1) in sys.columns.

Msg 8945, Level 16, State 1, Line 8
Table error: Object ID 41, index ID 1 will be rebuilt.
Could not repair this error.

Msg 2510, Level 16, State 17, Line 8
DBCC checkdb error: This system table index cannot be recreated.
Repair: The Nonclustered index successfully rebuilt for the object “sysidxstats” in database “MyDatabase”.

Msg 8921, Level 16, State 1, Line 8
Check terminated. A failure was detected while collecting facts. Possibly tempdb out of space or a system table is inconsistent. Check previous errors.
Msg 8998, Level 16, State 2, Line 8
Page errors on the GAM, SGAM, or PFS pages prevent allocation integrity checks in database ID 39 pages from (1:0) to (1:8087). See other errors for cause.
CHECKDB found 1 allocation errors and 0 consistency errors not associated with any single object.
Msg 2575, Level 16, State 1, Line 8
The Index Allocation Map (IAM) page (1:157) is pointed to by the next pointer of IAM page (0:0) in object ID 3, index ID 1, partition ID 196608, alloc unit ID 196608 (type In-row data), but it was not detected in the scan.
Could not repair this error.
CHECKDB found 1 allocation errors and 0 consistency errors in table ‘sys.sysrscols’ (object ID 3).

Msg 8948, Level 16, State 3, Line 8
Database error: Page (1:295) is marked with the wrong type in PFS page (1:1). PFS status 0x70 expected 0x60.
The error has been repaired.
Msg 8905, Level 16, State 1, Line 8
Extent (1:296) in database ID 39 is marked allocated in the GAM, but no SGAM or IAM has allocated it.
The error has been repaired.

Msg 5028, Level 16, State 4, Line 4
The system could not activate enough of the database to rebuild the log.

Msg 5125, Level 24, State 2, Line 2
File ‘C:Program FilesMicrosoft SQL ServerMSSQL12.SQL2014MSSQLDATASalesDatabase.mdf’ appears to have been truncated by the operating system. Expected size is 5120 KB but actual size is 5112 KB.
Msg 3414, Level 21, State 1, Line 2
An error occurred during recovery, preventing the database ‘SalesDatabase’ (39:0) from restarting. Diagnose the recovery errors and fix them, or restore from a known good backup. If errors are not corrected or expected, contact Technical Support.

Msg 3313, Level 21, State 1, Line 2
During redoing of a logged operation in database ‘SalesDatabase’, an error occurred at log record ID (135:752:2). Typically, the specific failure is previously logged as an error in the Windows Event Log service. Restore the database from a full backup, or repair the database.

Kwezi meko, kufuneka usebenzise isisombululo sobuchwephesha njenge DataNumen SQL Recovery ukulungisa idatabase yakho.

Iifayile zesampuli

SQL Server Inguqulelo	Ifayile ye-MDF eyonakele	Ifayile ye-MDF ilungiswe ngu DataNumen SQL Recovery
SQL Server 2014	Imposiso1_3.mdf (Umyalezo omnye 824)	Imposiso1_3_fixed.mdf
SQL Server 2014	Imposiso1_1.mdf (iimpazamo zikaMsg 824 eziqhubekayo)	Imposiso1_1_ilungisiwe.mdf
SQL Server 2014	Imposiso1_2.mdf ((Msg 824 elandelwa nguMsg 7909)	Imposiso1_2_fixed.mdf
SQL Server 2014	Imposiso4_1.mdf (uMsg 8992 ulandelwa nguMsg 3852)	Imposiso4_1_fixed.mdf
SQL Server 2014	Imposiso4_2.mdf (uMsg 8992 ulandelwa nguMsg 3852)	Imposiso4_2_fixed.mdf
SQL Server 2014	Imposiso5.mdf (Msg 8945)	Imposiso5_ilungisiwe.mdf
SQL Server 2014	Imposiso6.mdf (Msg 2510)	Imposiso6_ilungisiwe.mdf
SQL Server 2014	Imposiso2.mdf (Msg 2575)	Imposiso2_ilungisiwe.mdf
SQL Server 2014	Imposiso11.mdf (Msg 8905)	Imposiso11_ilungisiwe.mdf
SQL Server 2014	Imposiso3.mdf (Msg 5028)	Imposiso3_ilungisiwe.mdf
SQL Server 2014	Impazamo8.mdf (Msg 5125)	Impazamo8_ilungisiwe.mdf
SQL Server 2014	Imposiso9.mdf (Msg 3313)	Imposiso9_ilungisiwe.mdf

7. IiNdlela zokusebenza eziPhumelekileyo

7.1 Ukucwangcisa imiSebenzi yarhoqo ye-CHECKDB

Qalisa umiliselo lwe-DBCC CHECKDB lweveki nganye kwiziko ledatha lemveliso ebalulekileyo, kunye nokuhlolwa kwemihla ngemihla kwiinkqubo eziphezulu zentengiselwano. Cwangcisa imisebenzi ngexesha lokusetyenziswa okuphantsi ukunciphisa impembelelo yokusebenza, kwaye ucinge ngokujikeleza phakathi kokuhlolwa okupheleleyo kunye neenketho PHYSICAL_ONLY ngokusekelwe kubungakanani bedathabheyisi kunye neefestile zokulondolozwa. Ukucwangcisa okuzenzekelayo nge SQL Server I-Arhente iqinisekisa ukuphunyezwa okungaguqukiyo ngelixa ibonelela ngezakhono ezibekwe kwindawo eyodwa kunye nokulumkisa.

7.2 ULawulo lweMpembelelo yokuSebenza

Imisebenzi ye-DBCC CHECKDB isebenzisa izixhobo ezibalulekileyo zenkqubo, ezinokuthi zichaphazele umsebenzi wabasebenzisi ngaxeshanye. Jonga ukusetyenziswa kwe-CPU, ukusetyenziswa kwememori, kunye ne-disk I / O ngexesha lokujonga ukuqonda iipateni zempembelelo yokusebenza. Cinga ukusebenzisa iinketho ze-NOINDEX zokujonga rhoqo, ukugcina ukuqinisekiswa okugcweleyo kwiifestile zokugcinwa kwenyanga. Sebenzisa izandiso zexesha lokuphuma kwemibuzo kunye nezicwangciso zonxibelelwano lwabasebenzisi ukulawula okulindelweyo ngexesha lokujonga ingqibelelo.

7.3 UCwangciso lwefestile yoLondolozo

Lungelelanisa ukucwangciswa kwe-DBCC ye-CHECKDB kunye neminye imisebenzi yogcino efana nokusebenza kwe-backup, ukwakhiwa kwakhona kwesalathisi, kunye nohlaziyo lweenkcukacha-manani. Kuphephe ukutyhutyha imisebenzi yobutyebi enokuthi ibangele ukuthotywa komgangatho wokusebenza okanye imiba yokuphela kwexesha. Iifestile zogcino lwezicwangciso ezisekelwe kuqikelelo lobungakanani bedathabheyisi, ukuqinisekisa ixesha elaneleyo lokuqinisekisa imfezeko epheleleyo njengoko imithamo yedatha ikhula.

7.4 UkuBeka esweni okuSebenzayo kunye noKwazisa

Qwalasela SQL Server Izaziso zearhente ukwazisa abalawuli ngoko nangoko xa iDBCC CHECKDB ichonga urhwaphilizo. Sebenzisa izisombululo zokwahlulahlula kwelog ezikhupha kwaye zihlele iziphumo zokutshekisha imfezeko, ukwenza uhlalutyo lwentsingiselo kunye nokuchongwa kwengxaki okusebenzayo. Ukudala iinkqubo zokunyuka ezichaza amaxesha okuphendula kunye nabasebenzi abanoxanduva kumanqanaba ahlukeneyo obunzima borhwaphilizo.

8. I-DBCC CHECKTABLE: I-Lightweight Alternative

8.1 Isetyenziswa nini i-CHECKTABLE endaweni ye-CHECKDB

I-DBCC CHECKTABLE ibonelela ngokujongwa kwemfezeko yokujonga iitafile zomntu ngamnye, iyenza ilungele tarifumene ulungiso lweengxaki kunye nokugcinwa kwezinto ezithile zesiseko sedatha. Sebenzisa i-CHECKTABLE xa uphanda imiba yokusebenza kunye neetheyibhile ezithile, uqinisekisa iitheyibhile ezibalulekileyo zoshishino phakathi kokuhlolwa kwesiseko sedatha epheleleyo, okanye xa imiqobo yexesha ithintela ukuqinisekiswa okupheleleyo kwedatha. Le ndlela ingqina ukuxabiseka ngokukodwa koovimba beenkcukacha abakhulu apho imisebenzi epheleleyo ye-CHECKDB igqitha iifestile zolondolozo ezikhoyo.

8.2 I-syntax ye-DBCC CHECKTABLE kunye neMizekeliso

Umyalelo osisiseko WE-CHECKTABLE tarifumana iitafile ezithile:

DBCC CHECKTABLE('YourTable')

Njenge-CHECKDB, i-CHECKTABLE ixhasa iinketho ezahlukeneyo ezibandakanya i-NOINDEX yokuphucula ukusebenza kunye nokulungisa iiparamitha zokusombulula urhwaphilizo. Ungakhankanya amagama e-schema okuchongwa kwetafile ngokuthe ngqo:

DBCC CHECKTABLE('SchemaName.TableName', NOINDEX)

le tarindlela efunyenweyo ivumela ukuqinisekiswa kwengqibelelo yegranular ngelixa kugcinwa ukusebenza kwenkqubo ngexesha leeyure zokusebenza.

8.3 IiNzuzo zokuSebenza koovimba beenkcukacha abakhulu

Imisebenzi ye-CHECKTABLE igqiba ngokukhawuleza kakhulu kunokuhlolwa kwedatha epheleleyo, okwenza ukuba ukuqinisekiswa okuthe gqolo kwengqibelelo yeetheyibhile ezibalulekileyo. Le ndlela ivumela ukuqinisekiswa kwemihla ngemihla kweetheyibhile zoshishino ezibalulekileyo ngelixa ugcina imisebenzi ebanzi ye-CHECKDB kwiishedyuli zeveki okanye zenyanga. Ukusetyenziswa kwezixhobo ezincitshisiweyo kwenza i-CHECKTABLE ilungele ukwenziwa kwendawo yemveliso kunye nempembelelo encinci yabasebenzisi.

9. Xa I-CHECKDB Isilele

I-DBCC CHECKDB iya kusilela kwiimeko ezahlukeneyo, kubandakanya:

• Ukuphunyezwa kweDBCC CHECKDB iphelisa ngokungaqhelekanga
• Ukuphunyezwa kweDBCC CHECKDB kugqiba ngempumelelo, kodwa i iindlela zokulungisa usilele ukulungisa idatabase.

Kwezi meko, sifuna isixhobo esichubekileyo sokusinceda silungise ubuqhophololo kuvimba weenkcukacha.

9.1 Intshayelelo ku DataNumen SQL Recovery

DataNumen SQL Recovery ibonelela ngezakhono eziphezulu ngakumbi:

• Ireyithi yokubuyisela kwishishini.
• Buyisa iifayile zedatha ezonakele kakhulu.
• Fumana kwakhona zonke izinto zesiseko sedatha, kuquka iitafile, izalathisi, iimbono, izichukumisi, imithetho, kunye nokungagqibekanga.
• Fumana kwakhona iinkqubo ezigciniweyo, imisebenzi ye-scalar, imisebenzi enexabiso letheyibhile engaphakathi, kunye nemisebenzi enexabiso letheyibhile eninzi.
• Fumana iirekhodi ezicinywe ngokusisigxina.
• Khupha izinto ezintsonkothileyo ngaphakathi SQL Server iziseko sedatha.
• Lungisa iifayile zeMDF kwibhetshi.
• Iinketho zokulungisa ezibanzi.
• Ukugawulwa kwemithi kunye nokunika ingxelo.
• Inkxaso kubo bonke SQL Server iinguqulelo.
• Ukufumaneka kwenkxaso yobugcisa
• Uhlaziyo rhoqo kunye nophuculo

9.2 Uthelekiso lweRayithi yempumelelo

Amazinga okuphumelela kwakhona ahluka kakhulu:

• I-DBCC CHECKDB & ITSHEKILE: 1.27% izinga lokubuyisela eliphakathi
• DataNumen: 92.6% izinga lokubuyisela

Apha ngezantsi luthelekiso olupheleleyo lokhuphiswano:

9.3 Ukuchacha kuRhwaphilizo oluBuqatha

Izakhono eziphezulu kwiimeko ezinzima:

• Ukubuyiselwa kwindawo yokugcina eyonakalisiweyo
• Ukufumana kwakhona kwiidrive ezifomathiweyo okanye iinkqubo ezintlithekileyo
• Buyisa kwimifanekiso yedisk, iifayile zogcino, iifayile zedisk zomatshini, itemporary iifayile, njl.

9.4 Ziya kuqwalaselwa nini izisombululo zobuNgcali

• Akukho kufumaneka kogcino lwamva nje
• I-DBCC CHECKDB iyasilela
• Iimeko zorhwaphilizo ezimandundu
• Ukujongana nedatha yeshishini ebalulekileyo
• Xa ixesha libalulekile
• Xa ukuchacha okuphezulu kubalulekile

10. Ii-FAQ

11. Isiphelo

11.1 Isishwankathelo samanqaku aphambili

11.1.1 Essential DBCC CHECKDB Commands Recap

Yazi isivakalisi esisisiseko se-DBCC CHECKDB yokujonga ugcino lwedatha olubanzi, sebenzisa i-NOINDEX kunye ne-PHYSICAL_ONLY iinketho zokuphucula ukusebenza, kwaye uqonde i-CHECKTABLE ye tarifumene ukuqinisekiswa kwetafile. Le miyalelo isisiseko yenza isiseko sogcino lwedatha olusebenzayo, oluvumela ukufunyanwa kwangethuba urhwaphilizo kunye nohlolo olucwangcisiweyo lwemfezeko.

11.1.2 Isikhumbuzi seZenzo eziNgcono ezibalulekileyo

Ngalo lonke ixesha ugcina ii-backups zangoku phambi kokuba ujonge imfezeko, cwangcisa imisebenzi ye-CHECKDB rhoqo ngokusekelwe kukubaluleka kwedathabheyisi, kwaye usebenzise uhlolo oluzenzekelayo kwizilumkiso zorhwaphilizo olukhawulezileyo. Khumbula ukuba uthintelo ngokubeka iliso rhoqo lugqithise iindlela ezisebenzayo, kwaye izisombululo zokubuyisela kubuchwephesha zibonelela ngeendlela ezixabisekileyo zokugcina xa izixhobo eziqhelekileyo zingqineka zingonelanga.

11.2 Isetyenziswa nini iDBCC CHECKDB vs

Sebenzisa i-DBCC CHECKDB yokuhlola imfezeko yesiqhelo kunye nokusombulula urhwaphilizo olungephi, ngelixa ugcina izixhobo zokubuyisela ubungcali kwiimeko zorhwaphilizo ezimandundu ezingaphaya kwamandla akhelwe-ngaphakathi okulungisa. Isakhelo sesigqibo kufuneka siqwalasele ukufumaneka kogcino, ukubaluleka kwedatha, imiqobo yexesha, kunye nobungqongqo borhwaphilizo. Abalawuli besiseko sedatha abaphumeleleyo badibanisa uhlolo oluqhelekileyo lwe-CHECKDB kunye nezicwangciso ezibanzi zokulondoloza kunye nokwazisa ngeendlela zokufumana kwakhona xa iindlela ezisemgangathweni zingabonakali ngokwaneleyo.

12. Iingcaciso

1. Microsoft Funda. "DBCC CHECKDB (Transact-SQL)." SQL Server amaxwebhu. Microsoft Corporation.
   https://learn.microsoft.com/en-us/sql/t-sql/database-console-commands/dbcc-checkdb-transact-sql?view=sql-server-ver17
2. Microsoft Funda. "Sombulula ingxaki kwiimpazamo zokungaguquguquki kwedatha echazwe yi-DBCC CHECKDB." SQL Server amaxwebhu. Microsoft Corporation.
   https://learn.microsoft.com/en-us/troubleshoot/sql/database-engine/database-file-operations/troubleshoot-dbcc-checkdb-errors