Suatu ketika saya diminta membantu "memperbaiki" satu basis data. CHECKDB selama pemeriksaan memberikan daftar kesalahan, beberapa di antaranya ditandai sebagai "tidak dapat diperbaiki". Aplikasi itu berfungsi, tetapi entah bagaimana masih gelisah.
Ya, solusi yang tepat dalam situasi seperti itu adalah mengambil cadangan dari saat kesalahan masih belum muncul, untuk melokalkan data yang rusak dan menimpanya dari salinan yang bersih. Tapi ... seperti yang sering terjadi, kesalahannya terlambat, jadi tidak ada yang bisa dipulihkan. Di sisi lain - akan ada cadangan, tidak akan ada cerita ini.
Anamnesis
Langkah pertama adalah meluncurkan DBCC CHECKDB untuk memahami skala tragedi. Tim jujur menjalankan semua tabel, sebagian besar tidak menemukan masalah. Dalam kesimpulan yang sama, ada seratus laporan kesalahan yang "bisa diperbaiki". Sesuatu seperti ini:
Index row (1:386974:44) with values (C_FK_6bb5032ec2f94557a7d4a9d39a356168 = '04DA7FC4-B8F2-4D97-B8D2-B207A918D3DF' and C_PK_dd87d9cad5504a1199d4ddaf511ea6a9 = 'F44E8C34-862D-4952-8821-D0E87143A74E') pointing to the data row identified by (C_PK_dd87d9cad5504a1199d4ddaf511ea6a9 = 'F44E8C34-862D-4952-8821-D0E87143A74E').
Dan beberapa kesalahan lebih serius:
Table error: Object ID 1333579789, index ID 1, partition ID 72057609974841344, alloc unit ID 72057610075701248 (type In-row data). Page (1:20426) is missing a reference from previous page (1:267203). Possible chain linkage problem.
Baiklah kalau begitu. Skala pekerjaan diuraikan, mari kita mulai!
Kesalahan yang bisa diperbaiki
Untuk memahami mengapa beberapa kesalahan dapat dengan mudah diperbaiki secara otomatis, mari kita ingat bagaimana indeks diatur dalam MS SQL. Anda dapat membaginya menjadi 2 jenis: kluster dan (mengejutkan) non-kluster. (Kami tidak akan menyelidiki hal-hal khusus seperti indeks kolomstore - ini bukan masalahnya). Keduanya adalah pohon seimbang, yang sangat nyaman untuk menemukan data.
Adalah penting bahwa indeks cluster pada tingkat "daun" mereka menyimpan secara langsung isi baris tabel. Tetapi indeks non-cluster hanya menyimpan data kunci (dan, jika tersedia, bidang "termasuk"), serta tautan ke baris indeks cluster. Artinya, jika kita memiliki masalah dalam indeks non-cluster, kita bisa mengambil dan menimpa data yang rusak dari indeks cluster. Baik, atau hanya membangun kembali indeks yang dikalahkan - itu bagus, komposisi bidang diketahui, dan data sumber di dekatnya, utuh.
Tugas ini sama sekali tidak kreatif, sehingga Anda dapat dengan aman mempercayakannya ke mobil tanpa jiwa. Jalankan perintah
DBCC CHECKDB (< >, REPAIR_REBUILD)
dan pelajari laporan perkembangannya.
Baik log sumber dan log setelah fitur "memperbaiki" alamat halaman yang rusak. Kami membandingkan alamat-alamat ini dan memastikan bahwa semua kesalahan yang ditandai sebagai "dapat diperbaiki" memang berhasil diselesaikan.
Gangguan Konektivitas Data
Sekarang sesuatu yang lebih serius. Setelah memperbaiki indeks yang tidak berkerumun dan menghapus log dari pesan informasi, laporan tersebut berisi tiga catatan kesalahan "fatal"
Anda dapat, tentu saja, menyerah dan memotong dengan perintah DBCC CHECKDB (<DB name>, REPAIR_ALLOW_DATA_LOSS). Tapi ... Saya hanya tidak ingin kehilangan data. Saya ingin mengembalikan semua yang dimungkinkan secara maksimal. Karenanya, mari kita lihat secara lebih terperinci apa yang disampaikan laporan kesalahan secara umum.
Table error: Object ID 1333579789, index ID 1, partition ID 72057609974841344, alloc unit ID 72057610075701248 (type In-row data). Page (1:20426) is missing a reference from previous page (1:267203). Possible chain linkage problem. Table error: Object ID 1333579789, index ID 1, partition ID 72057609974841344, alloc unit ID 72057610075701248 (type In-row data). The previous link (1:267203) on page (1:267204) does not match the previous page (1:20426) that the parent (1:218898), slot 213 expects for this page. Table error: Object ID 1333579789, index ID 1, partition ID 72057609974841344, alloc unit ID 72057610075701248 (type In-row data). B-tree chain linkage mismatch. (1:20426)->next = (1:267204), but (1:267204)->Prev = (1:267203).
Laporan itu berisi cerita suram yang satu halaman harapkan untuk melihat tetangga, tetapi tetangga itu tidak tahu apa-apa tentang itu. Beberapa pelaut pelevinsky Zheleznyak: pergi ke dek, tetapi tidak ada dek.
Untuk pemahaman yang lengkap, diperlukan lebih banyak detail dan mereka harus langsung menuju ke isi halaman. Tetapi sebelum itu, kita akan mencari tahu bagaimana halaman indeks SQL Server terkait secara umum.
Jelas, halaman-halaman tersebut memiliki tautan “vertikal” hierarkis, yang membentuk B-tree. Halaman atas berisi tautan ke halaman-halaman dari tingkat yang lebih rendah dan seterusnya ke bagian paling kiri. Seperti yang sudah saya katakan, ini sangat nyaman untuk menemukan nilai: Anda ingin menemukan "Vasya Pupkin" dan setelah beberapa halaman ("dari B ke G" → "dari Ba ke Bb" → "Vasya Pupkin") Anda menemukan yang diinginkan.
Tetapi ada situasi ketika permintaan harus segera memilih seluruh rentang baris ("Dari Vasya ke Grisha"). Dalam hal ini, setiap kali Anda turun pohon dari atas ke bawah - Anda menabraknya. Untuk memenuhi permintaan tersebut, halaman menyimpan tautan "horisontal": setiap halaman mengetahui nomor tetangga "sebelum" dan "setelah". Dengan hubungan ini, pemindaian indeks lebih mudah dilakukan.
Dilihat oleh log kesalahan, kami memiliki ketidakcocokan koneksi horisontal dan vertikal. Tetapi untuk akhirnya yakin akan hal ini, mari kita lihat halaman-halamannya sendiri.
Kita harus masuk lebih dalam!
Untuk melihat halaman, kami akan menggunakan tim DBCC HALAMAN lama, layak dan tidak berdokumen. Dibutuhkan 4 parameter:
- Basis Idul Fitri
- ID file basis
- Halaman id
- Level of Detail (0 hingga 3)
Bergantung pada parameter terakhir, Anda bisa melihat hanya tajuk layanan (0), atau seluruh konten halaman (3), atau tajuk dan beberapa bagian konten (1 dan 2)
By the way, di SQL Server 2019, tampilan
sys.db_db_page_info akhirnya didokumentasikan, yang melakukan tugas yang sama. Sayangnya, ini hanya menampilkan data header (analog dengan menelusuri 0), sehingga masih belum menjawab tugas kami sepenuhnya.
Jadi, sebagai permulaan, jalankan perintah
DBCC TRACEON (3604, 1)
sehingga output dari perintah DBCC yang tersisa sampai ke konsol kami, dan bukan ke ErrorLog
Setelah itu, lihat judul halaman 20426:
DBCC PAGE (11, 1, 20426, 0)
Saya melihat. Halaman ingin berada di tengah antara halaman 267203 dan 267204. Tetapi bagaimana dengan halaman yang sama?
Tanpa tidur, tanpa semangat! Mereka baik-baik saja tanpa tamu tak diundang.
Baiklah, mari kita lihat daftar isi yang akan datang:
Teka-teki secara bertahap berkembang:
- Dalam hal tautan "vertikal" (struktur pohon indeks), halaman 20426 harus antara 267203 dan 267204
- Sambungan horizontal bertentangan dengan ini dan mengatakan bahwa tidak ada satu antara 267203 dan 267204.
Sekarang mari kita coba memahami data seperti apa yang diderita karena kegagalan tersebut.
Baris di dalam halaman diurutkan berdasarkan kunci indeks. Dengan demikian, mengetahui nilai kunci pertama dan terakhir pada halaman, Anda dapat menemukan rentang catatan "terpengaruh".
Kuncinya dapat dilihat hanya dalam deskripsi indeks. Kami akan menggunakan perintah
sp_helpindex untuk ini. Dalam hal ini, kuncinya hanya satu kolom, termasuk. membayar semua perhatian padanya.
Untuk menemukan kunci yang ada di halaman, kami menggunakan PAGE DBCC dengan maksimum, 3 level detail. Jika Anda menelusuri log keluaran, Anda bisa melihat konten "mentah" dari setiap baris dan rincian untuk setiap bidang:
Omong-omong, nilai batas utama (nilai pertama pada halaman) juga dapat dilihat pada output PAGE DBCC untuk halaman indeks superior (218898, lihat tangkapan layar di atas). Mereka ditampilkan di kolom segera setelah nomor halaman.
Pemeriksaan halaman menunjukkan bahwa halaman 20426 berisi persis setengah dari halaman 267203. Sifat kegagalan menjadi jelas. Ketika halaman database penuh dan tidak memiliki ruang lagi untuk memasukkan data baru, itu dibagi menjadi dua halaman baru. Rupanya, ketika halaman 267203 menjadi penuh, masalah halaman 20426 dibuat. DBMS mulai membangun kembali tautannya: ia berhasil menulis halaman baru ke struktur indeks. Tetapi untuk beberapa alasan, catatan horisontal tidak dapat diperbarui dan halaman baru "digantung" dalam keadaan tidak terdefinisi.
Apa semua ini bagi kita?
Tentu tidak banyak yang baik. Tautan halaman jelas diperlukan untuk mengakses data. Saat menjalankan kueri, DBMS secara independen menentukan cara untuk mendapatkan data ini. Tetapi aturan umum (dari mana pengecualian dimungkinkan) adalah ini:
- Pilihan "lebar" (misalnya, untuk laporan) dilakukan menggunakan catatan horisontal. Lebih mudah untuk menelusuri beberapa halaman secara berurutan, memilih rentang yang besar
- "Point" query (perbarui catatan tertentu) dilakukan oleh pencarian "daftar isi".
Ternyata, ketika catatan tertentu berubah, DBMS sampai ke "masalah" halaman 20426. Dan ketika menjalankan laporan, itu membaca data "secara horizontal" dan tidak melihat perubahan yang dibuat. Sekali lagi: dalam praktiknya, algoritme mungkin lebih rumit, tetapi kelas masalah yang mungkin masih dapat dimengerti.
Pertanyaan abadi intelektual Rusia
Sejujurnya, masih belum jelas bagi saya bagaimana ini bisa terjadi. DBMS modern sebenarnya adalah hal yang cukup andal. Semua perubahan dalam file database (termasuk penyesuaian dalam hubungan horizontal dan vertikal) dilakukan dalam transaksi. Operasi-operasi ini dicatat dalam log transaksi dan, jika tidak ada konfirmasi operasi yang berhasil dalam log ini, semua operasi dibatalkan. Di sini Anda dapat melihat bahwa transaksi selesai dengan sukses, tetapi beberapa perubahan di sepanjang jalur ke file data "hilang".
Satu-satunya penjelasan yang masuk akal yang terjadi pada saya adalah kegagalan cache subsistem disk. Semua data masuk ke cache, kemudian bagian dari catatan dari file data dan dari log ditulis ke disk - dan kemudian seseorang menarik sakelar. Akibatnya, rekaman "horizontal" yang dimodifikasi tidak berhasil direkam, tetapi database sudah tidak tahu apa-apa tentang ini. (Dan di sini, banyak pembaca harus buru-buru memeriksa baterai pada pengontrol disk industri mereka)
Apa yang harus dilakukan jauh lebih jelas. Untuk mengembalikan konektivitas tabel, cukup menyalin data ke tabel lain sehingga pembacaan horizontal digunakan saat menyalin. Untuk kesetiaan, Anda dapat secara eksplisit menentukan metode akses DBMS yang tepat menggunakan petunjuk FORCESCAN
select * into T_bca79e9e77c24cdc8bbb7cfd0ddc16fd_BKP from T_bca79e9e77c24cdc8bbb7cfd0ddc16fd with (FORCESCAN)
Setelah itu, Anda dapat menghapus tabel sumber dan mengembalikan data yang disalin di sana.
Tetapi kemudian perubahan yang dilakukan pada halaman 20426 akan hilang.
Oleh karena itu, sebelum membuat salinan tabel, Anda perlu menyalin baris dengan pengidentifikasi dari halaman 20426. Dan setelah mengembalikan tabel, perbaiki entri yang diperlukan. Akses ke catatan halaman 20426 diperlukan secara eksplisit oleh pengidentifikasi:
select * from T_bca79e9e77c24cdc8bbb7cfd0ddc16fd where C_PK_bca79e9e77c24cdc8bbb7cfd0ddc16fd = ''
Pengidentifikasi dapat diperoleh dengan membaca konten halaman dengan HALAMAN DBCC yang sama. Seperti yang sudah saya tulis, tepat ada setengah dari halaman ini 267.203, yaitu, 15 tautan.
Cadangkan halaman yang rusak, muat ulang tabel dan pencocokan catatan yang cocok - setelah beberapa puluh menit tabel dikembalikan.
Hore, kami menang! Tetapi apakah itu benar?
Yang benar adalah. Data dipulihkan, CHECKDB berhenti menuangkan kesalahan, bahkan matahari melihat keluar jendela. Anda dapat memuji diri sendiri dengan aman, mengangkat gelas minuman berkuda yang layak diterima dan ... ingat bahwa data dalam basis data tidak hanya ditautkan ke halaman. Jadi, saatnya untuk mengambil
DBCC CHECKCONSTRAINTS dan terjun langsung ke daftar kunci asing yang rusak. Tapi ini adalah kisah yang sangat berbeda ...
Apa lagi yang harus dibaca pada topik
- Perintah sintaks CHECKDB (Perhatikan peringatan tentang kemungkinan risiko menggunakan perintah!)
- Deskripsi DBCC HALAMAN tidak resmi
- Artikel bagus tentang indeks MS SQL yang menjelaskan banyak hal menarik. Termasuk bagaimana indeks fisik disimpan dalam database