رفع اشکال در فشرده سازی حجم فایل LOG پایگاه داده SQL Server

کاهش حجم Log File دیتابیس SQL Server

توسط admin | گروه sql | 1400/10/17

نظرات 0

 زمانی در واحد برنامه نویسی و توسعه نرم افزار یکی از شرکتهای معروف و صنعتی بسیار بزرگ با حدود 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 گیگابایت کاهش پیدا کرد و توانستم این مشکل را حل کنم.

 

 

0 نظر

نظر محترم شما در مورد مقاله های وب سایت برنامه نویسی و پایگاه داده

نظرات محترم شما در خدمات رسانی بهتر ما را یاری می نمایند. لطفا اگر مایل بودید یک نظر ما را مهمان فرمائید. آدرس ایمیل و وب سایت شما نمایش داده نخواهد شد.

حرف 500 حداکثر