در مورد دستور Shrink در SQL Server بدانیم.

در این مقاله به بررسی دستور Shrink در SQL Server و مفهوم آن می‌پردازیم. این دستور به معنای جمع کردن یا فشرده کردن فضای Data File و Log File در یک بانک اطلاعاتی است. در ادامه به بررسی جزئیات این دستور و نحوه استفاده از آن خواهیم پرداخت.

مفهوم Data File و Log File

طی فرآیند دستور Shrink در SQL فضای خالی فایل‌های بانک اطلاعاتی تا حد امکان از بین رفته و داده‌ها در یک قسمت جمع می‌شوند. جهت شرینک کردن بانک اطلاعاتی می‌توان از دستور DBCC ShrinkDatabase استفاده کرد که شکل کلی آن به صورت زیر است:

DBCC ShrinkDatabase (database_name | database_id | 0 [, target_percent] [, {NOTRUNCATE | TRUNCATEONLY}])

پارامترهای دستور Shrink

۱. مفهوم Database Name نام بانک اطلاعاتی که عملیات Shrink بر روی فایل‌های آن انجام می‌شود. می‌توان به‌جای نام بانک اطلاعاتی از ID بانک اطلاعاتی نیز استفاده کرد.

۲. مفهوم Target Percent این پارامتر مشخص می‌کند که چند درصد از فضای خالی فایل مورد نظر پس از Shrink در دسترس باشد.

۳. پارامترهای NOTRUNCATE و TRUNCATEONLY

• TruncateOnly: در این حالت فضای خالی انتهای فایل به سیستم عامل بازگشت داده می‌شود و Target Percent نادیده گرفته می‌شود. این پارامتر تنها بر روی Log File تأثیر دارد و برای Data File باید از دستور DBCC ShrinkFile استفاده کرد.
• NoTruncate: این حالت تنها بر روی Data File تأثیر دارد و فضای پر (Page پر) به فضای خالی (Page خالی) منتقل می‌شود که باعث کاهش Performance بانک اطلاعاتی می‌شود.

دستور

DBCC ShrinkDatabase(N’MyDB’, NOTRUNCATE)

اجرای این دستور بر روی Data Fileهای بانک اطلاعاتی تأثیر می‌گذارد و جابجایی بین Pageهای بانک اطلاعاتی رخ می‌دهد. این موضوع باعث Fragment شدن ایندکس‌ها می‌شود که کارایی بانک اطلاعاتی را پایین می‌آورد.

تاثیر Shrink بر کارایی بانک اطلاعاتی

Shrink کردن Data File باعث بوجود آمدن Fragmentation در ایندکس‌ها و جداول می‌شود که آدرس منطقی و فیزیکی Pageها یکسان نخواهد بود و این موضوع باعث می‌شود که کوئری‌های ما IO بیشتری جهت واکشی Data داشته باشند.

نکات مهم درباره دستور Shrink در SQL Server

1. دستور DBCC ShrinkFile برای شرینک کردن یکی از فایل‌های بانک اطلاعاتی مورد استفاده قرار می‌گیرد. این دستور دارای پارامترهای مشابه با دستور DBCC ShrinkDatabase است.
2. عدم استفاده از Auto Shrink در محیط‌های عملیاتی به هیچ عنوان Auto Shrink بانک اطلاعاتی را True نکنید.

3. شرینک بیشتر برای سناریوهای موقتی استفاده شود:

   • حذف حجم زیادی از داده‌ها

   • انتقال دیتابیس به سرور دیگر

4. پس از Shrink، ایندکس‌ها را بررسی و بهینه‌سازی کنید.

نحوه بررسی وضعیت Fragmentation جدول

در ادامه به بررسی وضعیت Fragmentation یک جدول قبل و بعد از انجام عملیات می‌پردازیم:

1. ایجاد بانک اطلاعاتی تستی

   USE master
   GO
   IF DB_ID('Test_Shrink') > 0
   DROP DATABASE Test_Shrink
   GO
   CREATE DATABASE Test_Shrink
   

2. ایجاد دو جدول تستی

   USE Test_Shrink
   GO
   IF OBJECT_ID('Employees1') > 0
   DROP TABLE Employees1
   GO
   CREATE TABLE Employees1
   (
   EmployeeID INT IDENTITY(1,1),
   SSN INT,
   FirstName NCHAR(2000),
   LastName NCHAR(2000),
   CONSTRAINT PK_Employees1 PRIMARY KEY (EmployeeID),
   CONSTRAINT UK_SSN1 UNIQUE (SSN)
   )
   

3. بررسی ایندکس‌های موجود در جدول

   SP_HELPINDEX Employees1
   

4. درج تعداد ۱۰۰۰۰ رکورد تستی در جداول

   -- Script for inserting test records
   

5. حذف جدول دوم

   DROP TABLE Employees2
   

6. بررسی وضعیت Fragmentation جدول و ایندکس‌های موجود در آن

   SELECT index_type_desc, Avg_Fragmentation_In_Percent
   FROM sys.dm_db_index_physical_stats (DB_ID('Test_Shrink'), OBJECT_ID('Employees1'), NULL, NULL, 'Limited')
   GO
   

       ۷. انجام عملیات

DBCC SHRINKDATABASE (Test_Shrink)

سوالات متداول (FAQ)

۱. آیا Shrink کردن دیتابیس به‌طور مرتب توصیه می‌شود؟
خیر. Shrink فقط در شرایط خاص مثل حذف انبوه داده‌ها توصیه می‌شود. استفاده مداوم = کاهش کارایی.

۲. Auto Shrink چه ضرری دارد؟
فعال بودن Auto Shrink باعث اجرای مکرر فرآیند Shrink و ایجاد Fragmentation بالا می‌شود.

۳. تفاوت ShrinkDatabase و ShrinkFile چیست؟
ShrinkDatabase روی کل دیتابیس اعمال می‌شود، در حالی‌که ShrinkFile فقط یک فایل خاص را فشرده می‌کند.

۴. بعد از Shrink چه کاری باید انجام شود؟
Rebuild یا Reorganize ایندکس‌ها برای جلوگیری از کاهش کارایی.

جمع‌بندی

Shrink ابزاری قدرتمند برای آزادسازی فضای دیتابیس در SQL Server است، اما استفاده نادرست از آن می‌تواند به شدت به عملکرد سیستم آسیب بزند.
مدیران دیتابیس باید تنها در شرایط ضروری از این دستور استفاده کنند و پس از آن ایندکس‌ها را بهینه‌سازی نمایند.