کاهش حجم لاگ هنگام حذف رکورد ها بصورت انبوه در SQL Server

حذف میلیون‌ها رکورد از یک جدول SQL Server می‌تواند عملیات پرهزینه‌ای از نظر مصرف فضای Transaction Log باشد. در بسیاری از موارد، فقط تعداد محدودی رکورد (مثلاً ۱۰۰۰) باید باقی بمانند، اما حذف مستقیم بقیه رکوردها منجر به تولید حجم بالایی از لاگ، پر شدن دیسک و کندی قابل‌توجه سرور می‌شود.

در این مقاله، با یک سناریو عملی و کدهای واقعی، تکنیک‌هایی مانند استفاده از جداول موقت (Helper Tables) و دستور SWITCH TABLE را بررسی می‌کنیم تا بتوانید با کمترین هزینه، دیتای غیرضروری را حذف و دیتابیس را پاکسازی کنید — بدون اینکه لاگ سرور شما منفجر شود!

سناریوی عملی حذف بهینه رکوردها با استفاده از SWITCH

۱. ایجاد جدول اصلی

CREATE TABLE [dbo].[TestLog] (
    id INT IDENTITY(1,1),
    Date1 DATETIME DEFAULT GETDATE(),
    Char1 NCHAR(20) DEFAULT 'My test desc!',
    Rep NVARCHAR(MAX) DEFAULT REPLICATE('3213213213', 100)
);

۲. درج داده تستی و ساخت ایندکس‌ها

-- درج یک میلیون رکورد
SET NOCOUNT ON;
INSERT INTO [dbo].[TestLog] (date1, char1, rep)
VALUES (DEFAULT, 'This is A Test Log!', DEFAULT);
GO 1000000

-- ایجاد ایندکس‌ها
CREATE CLUSTERED INDEX ClusteredIndex_Testlog_date1 ON [dbo].[Testlog](date1);
CREATE UNIQUE NONCLUSTERED INDEX NonClusteredIndex_Testlog_Col1_ID ON [dbo].[Testlog](id);

۳. ساخت جدول موقت برای SWITCH

CREATE TABLE [dbo].[TestLog_Switch] (
    id INT IDENTITY(1,1),
    Date2 DATETIME DEFAULT GETDATE(),
    Char2 NCHAR(20) DEFAULT 'My test desc!',
    Rep2 NVARCHAR(MAX) DEFAULT REPLICATE('3213213213', 100)
);

-- ایندکس‌ها برای تطابق ساختار
CREATE CLUSTERED INDEX ClusteredIndex_Test1_SWITCH_date2 ON [dbo].[Testlog_Switch](date2);
CREATE UNIQUE NONCLUSTERED INDEX NonClusteredIndex_Test1_SWITCH_ID2 ON [dbo].[Testlog_Switch](id);

۴. سوییچ کردن کل جدول

ALTER TABLE [dbo].[Testlog] SWITCH TO [dbo].[Testlog_Switch];

🔄 این عملیات هیچ لاگی ایجاد نمی‌کند چون فقط ساختار داده را بین دو جدول با ساختار مشابه جابه‌جا می‌کند.

۵. بازیابی رکوردهای منتخب (مثلاً ID ≤ ۱۰۰۰)

SET IDENTITY_INSERT [dbo].[Testlog] ON;
INSERT INTO dbo.TestLog (ID, Date1, Char1, Rep)
SELECT ID, Date2, Char2, Rep2
FROM [dbo].[Testlog_Switch]
WHERE ID <= 1000;
SET IDENTITY_INSERT [dbo].[Testlog] OFF;

۶. حذف جدول موقت

DROP TABLE [dbo].[Testlog_Switch];

مزایای این روش

چالش‌ها و نکات فنی

• ساختار جدول مقصد (Switch) باید دقیقاً با جدول اصلی همسان باشد (نام ستون‌ها، نوع داده، ایندکس‌ها و ترتیب آنها).
• SWITCH فقط در جداول Partitioned کاربرد دارد مگر اینکه عملیات را در محیط تست یا dev انجام دهید.
• قبل از اعمال در محیط production، حتماً تست و مستندسازی انجام شود.

کاربردهای عملی

• پاکسازی لاگ‌های قدیمی
• حذف داده‌های منقضی در جدول‌های Event Log
• آماده‌سازی داده‌ها برای تحلیل در Data Warehouse
• کاهش اندازه دیتابیس برای تهیه بکاپ‌های سریع‌تر

سوالات پرتکرار (FAQ)

آیا این روش روی همه نسخه‌های SQL Server قابل اجراست؟

عملیات SWITCH نیاز به نسخه‌های Enterprise یا Developer دارد. در برخی نسخه‌های Standard با محدودیت‌هایی همراه است.

چرا به جای DELETE ساده از SWITCH استفاده کنیم؟

DELETE باعث تولید حجم زیاد لاگ و کندی شدید می‌شود. SWITCH سریع، بهینه و بدون لاگ حجیم است.

آیا جدول Switch باید خالی باشد؟

بله، جدول مقصد برای استفاده از SWITCH باید بدون رکورد باشد و ساختار کاملاً مشابهی داشته باشد.

اگر بخواهیم فقط رکوردهای خاص را نگه داریم، چطور عمل کنیم؟

می‌توانید رکوردهای مورد نیاز را از جدول SWITCH دوباره به جدول اصلی وارد کرده و بقیه را حذف کنید، همان‌طور که در سناریو این مقاله نشان داده شد.

آیا می‌توان این فرآیند را اتوماتیک‌سازی کرد؟

بله. با استفاده از Jobهای SQL Server Agent یا اسکریپت‌های PowerShell می‌توان این عملیات را زمان‌بندی و خودکار کرد.

ارتباط و مشاوره

اگر با جداول بزرگ سر و کار دارید و می‌خواهید عملیات پاکسازی یا بهینه‌سازی را بدون افت عملکرد انجام دهید:

همین حالا با شرکت لاندا تماس ✆ بگیرید

در دوره‌های تخصصی مدیریت لاگ و بهینه‌سازی دیتابیس شرکت کنید
اجرای پروژه‌های بهینه‌سازی دیتابیس‌تان را به تیم فنی لاندا بسپارید