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 sangat mirip, 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 ini penting untuk mengelola ruang file database.

Kuantitas database Definisi Komentar
Ruang data yang digunakan Jumlah 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 Jumlah ruang file yang diformat disediakan untuk menyimpan data database. Jumlah ruang yang dialokasikan tumbuh secara otomatis, tetapi tidak pernah berkurang setelah penghapusan. Perilaku ini memastikan bahwa sisipan di masa depan lebih cepat karena ruang tidak perlu diformat ulang.
Ruang data dialokasikan tetapi tidak digunakan Perbedaan antara jumlah ruang data yang dialokasikan dan ruang data yang digunakan. Kuantitas ini menunjukkan jumlah maksimum ruang kosong yang dapat direklamasi dengan menyusutkan file data database.
Ukuran maksimal data Jumlah ruang maksimum yang dapat digunakan 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 that demonstrates the size of difference database space concepts in the database quantity table.

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 autogrow untuk file, Anda juga dapat menggunakan kolom , , max_sizedan untuk file log tersebut sizedi sys.database_filesgrowth.

Penting

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

Menyusutkan file log

Untuk mengurangi ukuran fisik file log fisik dengan mengembalikan ruang kosong dalam file ke sistem operasi, susutkan file log. 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 berdampak pada performa database saat berjalan, dan jika memungkinkan 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 menyusutnya 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 dan dengan melakukan itu, 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 tidak dimungkinkan sampai 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

Setelah operasi penyusutan terhadap file data selesai, indeks mungkin menjadi terpecah. Hal ini mengurangi efektivitas optimisasi performa untuk beban kerja tertentu, seperti kueri yang menggunakan pemindaian berukuran besar. Jika penurunan performa terjadi setelah operasi penyusutan selesai, pertimbangkan pemeliharaan indeks untuk membangun kembali indeks. Ingatlah bahwa menyusun ulang indeks memerlukan ruang kosong di database, karenanya mungkin menyebabkan ruang yang dialokasikan bertambah, berlawanan dengan 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 dengan menambahkan file log ke database, biasanya pada disk yang berbeda. Satu file log transaksi cukup kecuali ruang log kehabisan, dan ruang disk juga kehabisan volume yang menyimpan file log.

  • Untuk menambahkan file log ke database, gunakan ADD LOG FILE klausa ALTER DATABASE pernyataan. Menambahkan file log 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 ukuran tempdb 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:

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

Untuk informasi selengkapnya, lihat Rekomendasi.

Rekomendasi

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

  • Peningkatan pertumbuhan otomatis (autogrow) 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 data dan file log menggunakan FILEGROWTH opsi , mungkin lebih disukai untuk mengaturnya dalam ukuran alih-alih persentase, untuk memungkinkan kontrol yang lebih baik pada rasio pertumbuhan, 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 atur FILEGROWTH nilai opsi di atas 1.024 MB untuk log transaksi. Nilai default untuk FILEGROWTH opsi 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 otomatis 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 isian 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.

Langkah berikutnya