Bagikan melalui

Mengelola ukuran file log transaksi

  • Artikel

Berlaku untuk: SQL Server

Artikel ini membahas cara memantau ukuran log transaksi SQL Server, menyusutkan log transaksi, menambahkan atau memperbesar file log transaksi, mengoptimalkan tempdb tingkat pertumbuhan log transaksi, dan mengontrol pertumbuhan file log transaksi.

Artikel ini berlaku untuk SQL Server. Meskipun serupa, untuk informasi tentang mengelola ukuran file log transaksi di Azure SQL Managed Instance, lihat Mengelola ruang file untuk database di Azure SQL Managed Instance. Untuk informasi tentang Azure SQL Database, lihat Mengelola ruang file untuk database di Azure SQL Database.

Memahami jenis ruang penyimpanan untuk database

Memahami jumlah ruang penyimpanan berikut penting untuk mengelola ruang file database.

Kuantitas database Definisi Komentar
Ruang data yang digunakan Ruang yang digunakan untuk menyimpan data database. Umumnya, ruang yang digunakan meningkat (berkurang) pada sisipan (penghapusan). Dalam beberapa kasus, ruang yang digunakan tidak berubah pada sisipan atau penghapusan tergantung pada jumlah dan pola data yang terlibat dalam operasi dan fragmentasi apa pun. Misalnya, menghapus satu baris dari setiap halaman data tidak selalu mengurangi ruang yang digunakan.
Ruang data dialokasikan Ruang file yang diformat tersedia untuk menyimpan data database. Jumlah ruang yang dialokasikan bertambah secara otomatis, tetapi tidak pernah berkurang setelah penghapusan. Perilaku ini memastikan bahwa penyisipan di masa mendatang lebih cepat karena ruang tidak perlu diformat ulang.
Ruang data dialokasikan tetapi tidak digunakan Perbedaan antara jumlah yang dialokasikan dan ruang data yang digunakan. Kuantitas ini mewakili ruang kosong maksimum yang dapat diklaim kembali oleh file data database yang menyusut.
Ukuran maksimal data Jumlah maksimum ruang untuk menyimpan data database. Jumlah ruang data yang dialokasikan tidak dapat tumbuh melebihi ukuran maksimal data.

Diagram berikut ini mengilustrasikan hubungan antara berbagai jenis ruang penyimpanan untuk database.

Diagram yang menunjukkan ukuran perbedaan konsep ruang database dalam tabel kuantitas database.

Mengkueri database tunggal untuk informasi ruang file

Gunakan kueri berikut untuk mengembalikan jumlah ruang file database yang dialokasikan dan jumlah ruang yang tidak digunakan yang dialokasikan. Unit hasil kueri dalam MB.

-- Connect to a user database
SELECT file_id, type_desc,
       CAST(FILEPROPERTY(name, 'SpaceUsed') AS decimal(19,4)) * 8 / 1024. AS space_used_mb,
       CAST(size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128.0 AS decimal(19,4)) AS space_unused_mb,
       CAST(size AS decimal(19,4)) * 8 / 1024. AS space_allocated_mb,
       CAST(max_size AS decimal(19,4)) * 8 / 1024. AS max_size_mb
FROM sys.database_files;

Memantau penggunaan ruang log

Pantau penggunaan ruang log dengan menggunakan sys.dm_db_log_space_usage. DMV ini mengembalikan informasi tentang jumlah ruang log yang saat ini digunakan, dan menunjukkan kapan log transaksi membutuhkan pemotongan.

Untuk informasi tentang ukuran file log saat ini, ukuran maksimumnya, dan opsi pertumbuhan otomatis untuk file, Anda juga dapat menggunakan kolom , , dan growth untuk file log tersebut sizedi sys.database_files. max_size

Penting

Hindari membebani disk log secara berlebih. Pastikan penyimpanan log dapat menahan IOPS dan persyaratan latensi rendah untuk beban transaksi anda.

Menyusutkan file log

Susutkan file log untuk mengurangi ukuran fisiknya dengan mengembalikan ruang kosong ke sistem operasi. Penyusutan hanya membuat perbedaan ketika file log transaksi berisi ruang yang tidak digunakan.

Jika file log penuh, kemungkinan karena transaksi terbuka, selidiki apa yang mencegah pemotongan log transaksi.

Perhatian

Operasi penyusutan tidak boleh dianggap sebagai operasi pemeliharaan rutin. File data dan log yang bertambah karena operasi bisnis yang teratur dan berulang tidak memerlukan operasi penyusutan. Perintah penyusutan memengaruhi performa database saat berjalan; mereka harus dijalankan selama periode penggunaan rendah. Tidak disarankan untuk menyusutkan file data jika beban kerja aplikasi reguler akan menyebabkan file membesar lagi ke ukuran yang sama yang dialokasikan.

Waspadai potensi dampak performa negatif dari penyusutan file database; lihat Pemeliharaan indeks setelah menyusut.

Sebelum menyusutkan log transaksi, ingatlah Faktor-faktor yang dapat menunda pemotongan log. Jika ruang penyimpanan diperlukan lagi setelah log menyusut, log transaksi akan tumbuh lagi, memperkenalkan overhead performa selama operasi pertumbuhan log. Untuk informasi selengkapnya, lihat Rekomendasi.

Anda hanya dapat menyusutkan file log saat database online, dan setidaknya satu file log virtual (VLF) gratis. Dalam beberapa kasus, menyusutkan log mungkin hanya dimungkinkan setelah pemotongan log berikutnya.

Faktor-faktor, seperti transaksi yang berjalan lama, dapat menjaga VLF tetap aktif untuk jangka waktu yang lama, dapat membatasi penyusutan log, atau bahkan mencegah log menyusut sama sekali. Untuk informasi, lihat Faktor-faktor yang dapat menunda pemotongan log.

Menyusutkan file log akan menghapus satu atau beberapa VLF yang tidak memiliki bagian dari log logis (yaitu, VLF yang tidak aktif). Saat Anda menyusutkan file log transaksi, VLF yang tidak aktif dihapus dari akhir file log untuk mengurangi log menjadi sekitar ukuran target.

Untuk informasi selengkapnya tentang operasi penyusutan, tinjau tautan berikut:

Menyusutkan file log (tanpa menyusutkan file database)

Memantau peristiwa penyusutan file log

  • Kelas Peristiwa Penyusutan Otomatis File Log.

Memantau ruang log

Pemeliharaan indeks setelah penyusutan

Indeks mungkin menjadi terfragmentasi setelah operasi penyusutan selesai terhadap file data. Ini mengurangi efektivitasnya untuk pengoptimalan performa untuk beban kerja tertentu, seperti kueri menggunakan pemindaian besar. Jika penurunan performa terjadi setelah operasi penyusutan selesai, pertimbangkan pemeliharaan indeks untuk membangun kembali indeks. Perlu diingat bahwa pembangunan ulang indeks memerlukan ruang kosong dalam database dan karenanya dapat meningkatkan ruang yang dialokasikan, menangkal efek penyusutan.

Untuk informasi lebih lanjut terkait pemeliharaan indeks, lihat Mengoptimalkan pemeliharaan indeks untuk meningkatkan performa kueri dan mengurangi konsumsi sumber daya.

Menambahkan atau memperbesar file log

Anda dapat memperoleh ruang dengan memperbesar file log yang ada (jika ruang disk mengizinkan) atau menambahkan file log ke database, biasanya pada disk yang berbeda. Satu file log transaksi cukup kecuali ruang log habis dan ruang disk juga kehabisan volume yang menyimpan file log.

Untuk menambahkan file log ke database, gunakan ADD LOG FILE klausa ALTER DATABASE pernyataan. Ini memungkinkan log tumbuh.

  • Untuk memperbesar file log, gunakan MODIFY FILE klausa ALTER DATABASE pernyataan, menentukan SIZE sintaks dan MAXSIZE . Untuk informasi selengkapnya, lihat opsi ALTER DATABASE (Transact-SQL) File dan Grup File.

Untuk informasi selengkapnya, lihat Rekomendasi.

Mengoptimalkan ukuran log transaksi tempdb

Menghidupkan ulang instans server mengubah ukuran log tempdb transaksi database menjadi ukuran asli dan pra-pertumbuhannya. Ini dapat mengurangi performa tempdb log transaksi.

Anda dapat menghindari overhead ini dengan meningkatkan tempdb ukuran log transaksi setelah memulai atau memulai ulang instans server. Untuk informasi selengkapnya, lihat Database tempdb.

Mengontrol pertumbuhan file log transaksi

Gunakan pernyataan opsi AlTER DATABASE (Transact-SQL) dan Filegroup untuk mengelola pertumbuhan file log transaksi. Berikut hal-hal yang perlu diketahui:

Gunakan opsi 'UKURAN' untuk mengubah ukuran file saat ini di unit KB, MB, GB, dan TB.

  • Untuk mengubah kenaikan pertumbuhan, gunakan FILEGROWTH opsi . Nilai 0 menunjukkan bahwa pertumbuhan otomatis diatur ke nonaktif dan tidak ada ruang tambahan yang diizinkan. Gunakan opsi MAXSIZE' untuk mengontrol ukuran maksimum file log di unit KB, MB, GB, dan TB atau untuk mengatur pertumbuhan ke UNLIMITED.

Untuk informasi selengkapnya, lihat Rekomendasi.

Rekomendasi

Berikut ini adalah beberapa rekomendasi umum saat Anda bekerja dengan file log transaksi:

  • Peningkatan pertumbuhan otomatis (pertumbuhan otomatis) dari log transaksi, seperti yang ditetapkan oleh FILEGROWTH opsi, harus cukup besar untuk tetap di depan kebutuhan transaksi beban kerja. Kenaikan pertumbuhan file pada file log harus cukup besar untuk menghindari ekspansi yang sering. Pointer yang baik untuk mengukur log transaksi dengan benar memantau jumlah log yang ditempati selama:

    • Waktu yang diperlukan untuk menjalankan pencadangan penuh karena pencadangan log tidak dapat terjadi sampai selesai.
    • Waktu yang diperlukan untuk operasi pemeliharaan indeks terbesar.
    • Waktu yang diperlukan untuk menjalankan batch terbesar dalam database.

  • Saat mengatur pertumbuhan otomatis untuk file data dan log menggunakan FILEGROWTH opsi , mungkin lebih disukai untuk mengaturnya dalam ukuran alih-alih persentase untuk memungkinkan kontrol rasio pertumbuhan yang lebih baik, karena persentase adalah jumlah yang terus bertambah.

    • Dalam versi sebelum SQL Server 2022 (16.x), log transaksi tidak dapat menggunakan Inisialisasi File Instan, sehingga waktu pertumbuhan log yang diperpanjang sangat penting.

    • Dimulai dengan SQL Server 2022 (16.x) (semua edisi) dan di Azure SQL Database, inisialisasi file instan dapat menguntungkan peristiwa pertumbuhan log transaksi hingga 64 MB. Kenaikan ukuran pertumbuhan otomatis default untuk database baru adalah 64 MB. Peristiwa pertumbuhan otomatis file log transaksi yang lebih besar dari 64 MB tidak dapat memperoleh manfaat dari inisialisasi file instan.

    • Sebagai praktik terbaik, jangan tetapkan FILEGROWTH nilai opsi di atas 1.024 MB untuk log transaksi. Nilai default untuk opsi ini FILEGROWTH adalah:

      Versi Nilai default
      Dimulai dengan SQL Server 2016 (13.x) Data 64 MB. File log 64 MB.
      Dimulai dengan SQL Server 2005 (9.x) Data 1 MB. File log 10%.
      Sebelum SQL Server 2005 (9.x) Data 10%. File log 10%.

  • Kenaikan pertumbuhan kecil dapat menghasilkan terlalu banyak VLF kecil dan dapat mengurangi performa. Untuk menentukan distribusi VLF optimal untuk ukuran log transaksi saat ini dari semua database dalam instans tertentu dan kenaikan pertumbuhan yang diperlukan untuk mencapai ukuran yang diperlukan, lihat skrip ini untuk menganalisis dan memperbaiki VLF, yang disediakan oleh Tim SQL Tiger.

  • Kenaikan pertumbuhan otomatis yang besar dapat menyebabkan dua masalah:

    • Kenaikan pertumbuhan otomatis yang besar dapat menyebabkan database dijeda saat ruang baru dialokasikan, berpotensi menyebabkan batas waktu kueri.
      • Kenaikan pertumbuhan otomatis yang besar dapat menghasilkan terlalu sedikit VLF dan besar dan juga dapat memengaruhi performa. Untuk menentukan distribusi VLF optimal untuk ukuran log transaksi saat ini dari semua database dalam instans tertentu dan kenaikan pertumbuhan yang diperlukan untuk mencapai ukuran yang diperlukan, lihat skrip ini untuk menganalisis dan memperbaiki VLF, yang disediakan oleh Tim SQL Tiger.

  • Bahkan dengan autogrow diaktifkan, Anda dapat menerima pesan bahwa log transaksi penuh jika tidak dapat tumbuh cukup cepat untuk memenuhi kebutuhan kueri Anda. Untuk informasi selengkapnya tentang mengubah kenaikan pertumbuhan, lihat opsi ALTER DATABASE (Transact-SQL) File dan Filegroup.

  • Memiliki beberapa file log dalam database tidak meningkatkan performa dengan cara apa pun, karena file log transaksi tidak menggunakan isi proporsional seperti file data dalam grup file yang sama.

File log dapat diatur untuk menyusut secara otomatis. Namun, ini tidak disarankan, dan properti database auto_shrink diatur ke FALSE secara default. Jika auto_shrink diatur ke TRUE, penyusutan otomatis mengurangi ukuran file hanya ketika lebih dari 25 persen ruangnya tidak digunakan. - File disusutkan baik ke ukuran di mana hanya 25 persen dari file yang tidak digunakan ruang atau ke ukuran asli file, mana yang lebih besar. - Untuk informasi tentang mengubah pengaturan properti auto_shrink , lihat Menampilkan atau Mengubah Properti Database dan Mengubah Opsi SET DATABASE (Transact-SQL).

Konten terkait