کاهش حجم Log File دیتابیس SQL Server
زمانی در واحد برنامه نویسی و توسعه نرم افزار یکی از شرکتهای معروف و صنعتی بسیار بزرگ با حدود 1500 نفر نیرو در حال کار بودم که به مشکل افزایش حجم Log File دیتابیس SQL Server برخورد نمودم. حجم دیتابیس در حدود 50 گیکابایت بود و حجم Log در ابتدا بیش از 500 مگابایت نبود. ولی ناگهان حجم فایل Log افزایش پیدا کرد و به بیش از 150 گیگابایت افزایش پیدا کرد.
|
dbname
|
logsize
|
logspace
|
stat
|
|
master
|
1.742188
|
31.61435
|
0
|
|
tempdb
|
0.7421875
|
46.51316
|
0
|
|
model
|
0.7421875
|
52.63158
|
0
|
|
msdb
|
3.054688
|
20.84399
|
0
|
|
HesSiteDB
|
186254.9
|
324.399
|
0
|
|
ReportServer
|
6.242188
|
14.2913
|
0
|
|
ReportServer
|
0.8046875
|
59.5267
|
0
|
چند روش را برای کاهش حجم Log File تست کردم ولی کارگشا نبود. مشکلی که وجود داشت این بود که پس از گرفتن وضعیت Log File و مشاهده Page ها مشخص شد که بیش از 90 درصد Page ها درگیر هستند. به همین خاطر اجازه کاهش حجم Log File داده نمی شد و در حقیقت پس از هر بار فشرده سازی فایل Log فقط چند مگابایت از حجم فایل کم می شد که آن هم پس از چند دقیقه مجددا به حالت قبلی برمی گشت و حتی حجم لاگ فایل افزایش بیشتری پیدا می کرد و موردی که مرتبا موجب سختی کار شده بود این بود که تعداد Page های در حال استفاده هم بسیار زیاد بودند و این مساله هم خود باعث ایجاد شبهه در من شده بود. به هر حال سیستم های نرم افزاری که در شرکت در حال استفاده بودند بیش از 100 کابر فعال در آن واحد داشتند و شاید یکی از مسائلی که Page های در حال استفاده را بسیار بالا می برد همین بود (در حدود 11302776 ). ولی افزایش بی روییه لاگ مورد عجیبی بود.
پس از بررسی ساختار دیتابیس و ساختار کدهایی که توسط برنامه نویسان قبلی نوشته شده بود به دو دلیل عمده اصلی رسیدم که موجب این مشکل شده بود:
1- استفاده بی رویه از ایندکسها روی فیلدهای جدولهای پایگاه داده که گاهی اوقات بیش از 5 فیلد در جدول با هم به عنوان فیلد ایندکس تعریف شده بودند ، بطوریکه میشد به راحتی فقط حداکثر با دوفیلد ایندکس را ایجاد کرد و به مشکلی هم بر نخورد. (که این مساله موجب می شد که در هنگام ویرایش یا درج چندید رکورد اضافه در Log File به ازای هر ترنزکشن درج شود)
2- استفاده بی رویه از حالت Transaction Base که حتی برای کوچکترین کارها از Begin Transaction و End Transaction استفاده شده بود و این مساله باعث افزایش انفجاری حجم لاگ فایل شده بود. (در مقاله ای جداگانه به این مساله خواهم پرداخت)
راه حل چه بود؟
در نهایت برای رفع مشکل از روش Log Backup استفاده کردم. در ابتدا Page های Log File را با دستور DBCC SHRINKFILE مجددا عمل reorganize را انجام دادم. بعد از آن یک Job تعریف کردم که در ابتدای کار هر 2 ساعت یک بار یک Log Backup می گرفت. پس از هر بار Log Backup یک بار دستور فشرده سازی Log File هم درون Job اجرا می شد. پس از حدود 2 روز زمان Job را به حدود 45 دقیقه کاهش دادم.
USE [HesSiteDB]
Go
ALTER DATABASE HesSiteDB SET RECOVERY SIMPLE
GO
DBCC SHRINKFILE (N'HesSiteDB_log' , 1)
GO
DBCC SHRINKFILE (N'HesSiteDB_log' , EMPTYFILE)
GO
DBCC SHRINKFILE (N'HesSiteDB_log' , EMPTYFILE)
GO
DBCC SHRINKFILE (N'HesSiteDB_log' , EMPTYFILE)
GO
DBCC SHRINKFILE (N'HesSiteDB_log' , EMPTYFILE)
GO
DBCC SHRINKFILE (N'HesSiteDB_log' , EMPTYFILE)
GO
DBCC SHRINKFILE (N'HesSiteDB_log' , EMPTYFILE)
GO
ALTER DATABASE HesSiteDB SET RECOVERY FULL
GO
در نهایت بعد از حدود یک هفته حجم لاگ فایل به حدود 3 گیگابایت کاهش پیدا کرد و توانستم این مشکل را حل کنم.