علت و راهکارهای رفع خطای 1105 در SQL Server

علت و راهکارهای رفع خطای 1105 در SQL Server: عدم تخصیص فضای کافی در tempdb

خطای 1105 در SQL Server یکی از مشکلات رایج و حیاتی است که نشان‌دهنده عدم توانایی SQL Server در تخصیص فضای دیسک لازم برای پایگاه داده موقت (tempdb) است. این خطا معمولاً به صورت “Could not allocate space for object ‘XYZ’ in database ‘tempdb’ because the ‘PRIMARY’ filegroup is full. Create disk space by deleting old files, dropping objects, or adding additional files.” ظاهر می‌شود، اگرچه ممکن است ‘XYZ’ نام یک شیء موقت خاص مانند جدول یا ایندکس نباشد و صرفاً به فضای کلی tempdb اشاره کند. tempdb یک پایگاه داده سیستمی حیاتی در SQL Server است که برای ذخیره‌سازی اشیاء موقت مانند جداول موقت (temporary tables)، متغیرهای جدول (table variables)، Cursorها، اطلاعات نسخه‌سازی برای تراکنش‌های ایزوله‌شده (snapshot isolation) و عملیات‌های داخلی موتور SQL Server مانند مرتب‌سازی (sorts)، توابع هش (hash joins) و بازسازی ایندکس‌ها (index rebuilds) استفاده می‌شود. زمانی که tempdb قادر به گسترش (grow) یا تخصیص فضای جدید نباشد، عملیات‌های وابسته به آن با شکست مواجه شده و خطای 1105 رخ می‌دهد. این موضوع می‌تواند منجر به اختلال در عملکرد برنامه، عدم پاسخگویی سیستم و حتی از کار افتادن سرویس‌های مرتبط با SQL Server شود. بنابراین، درک علت و ارائه راهکارهای مؤثر برای رفع این خطا برای هر مدیر پایگاه داده SQL Server ضروری است.

دلایل اصلی بروز خطای 1105 در tempdb

شناسایی علت ریشه‌ای خطای 1105 اولین قدم برای رفع آن است. این خطا می‌تواند به دلایل مختلفی رخ دهد که برخی از رایج‌ترین آن‌ها عبارتند از:

• کمبود فضای دیسک فیزیکی: اصلی‌ترین و ساده‌ترین دلیل، پر شدن درایو دیسکی است که فایل‌های داده (MDF/NDF) یا فایل لاگ (LDF) پایگاه داده tempdb بر روی آن قرار دارند. اگر فضای کافی برای گسترش فایل‌های tempdb وجود نداشته باشد، SQL Server نمی‌تواند فضای جدید تخصیص دهد.

• تنظیمات نادرست فایل‌های tempdb:

  • عدم فعال‌سازی یا تنظیم نادرست Auto-Growth: اگر قابلیت Auto-Growth برای فایل‌های tempdb غیرفعال باشد یا مقدار آن خیلی کم تنظیم شده باشد، فایل‌ها پس از رسیدن به حداکثر اندازه مشخص شده خود، دیگر نمی‌توانند بزرگ شوند. حتی اگر فضای دیسک کافی باشد، عدم توانایی فایل در گسترش باعث بروز خطا می‌شود.
  • Initial Size ناکافی: اگر اندازه اولیه فایل‌های tempdb بسیار کوچک تنظیم شده باشد و حجم کاری سیستم بالا باشد، فایل‌ها به سرعت رشد می‌کنند و ممکن است به محدودیت‌های Auto-Growth یا فضای دیسک برسند.
  • محدودیت Maximum Size: اگر Maximum Size برای فایل‌های tempdb به یک مقدار خاص (و نه Unlimited) تنظیم شده باشد، پس از رسیدن به آن اندازه، حتی با وجود فضای دیسک کافی، فایل نمی‌تواند بزرگ‌تر شود.

• عملیات‌های پرمصرف tempdb: برخی از عملیات‌ها و کوئری‌ها به طور طبیعی مقدار زیادی از فضای tempdb را مصرف می‌کنند. شناسایی و بهینه‌سازی این عملیات‌ها می‌تواند به جلوگیری از پر شدن tempdb کمک کند:

  • کوئری‌های پیچیده با Sort/Hash Join بزرگ: کوئری‌هایی که نیاز به مرتب‌سازی یا عملیات Join سنگین دارند، مقادیر زیادی از حافظه و در صورت لزوم، فضای tempdb را برای انجام این عملیات‌ها مصرف می‌کنند.
  • استفاده گسترده از جداول موقت و متغیرهای جدول: ایجاد و پر کردن تعداد زیادی جدول موقت یا متغیرهای جدول با حجم بالا، به سرعت فضای tempdb را اشغال می‌کند.
  • بازسازی یا سازماندهی ایندکس‌ها: عملیات بازسازی و سازماندهی ایندکس‌ها (خصوصاً آفلاین) می‌تواند فضای قابل توجهی در tempdb برای ذخیره نسخه‌های میانی مصرف کند.
  • تراکنش‌های طولانی‌مدت یا ایزولاسیون Snapshot: تراکنش‌هایی که برای مدت طولانی باز می‌مانند یا دیتابیس‌هایی که از سطح ایزولاسیون Snapshot استفاده می‌کنند، ممکن است نسخه‌های زیادی از داده‌ها را در tempdb ذخیره کنند.
  • عملیات DBCC: برخی از دستورات DBCC مانند DBCC CHECKDB می‌توانند از tempdb برای ایجاد اشیاء داخلی استفاده کنند.

راهکارهای عملی و گام‌به‌گام برای رفع خطای 1105

برای رفع خطای 1105، باید به صورت سیستماتیک علت اصلی را شناسایی و برطرف کرد. در ادامه، راهکارهای عملی و گام‌به‌گام ارائه شده است:

گام 1: بررسی و افزایش فضای دیسک فیزیکی

اولین اقدام، بررسی فضای درایو دیسکی است که فایل‌های tempdb (هم فایل‌های داده و هم فایل لاگ) در آن قرار دارند. اطمینان حاصل کنید که فضای خالی کافی برای گسترش فایل‌ها وجود دارد. در صورت کمبود فضا، باید اقدام به آزاد کردن فضا در آن درایو (حذف فایل‌های غیرضروری) یا افزایش حجم درایو (تغییر اندازه پارتیشن، اضافه کردن دیسک جدید) کنید. این یک راه حل موقتی نیست و باید به عنوان یک راهکار دائمی در نظر گرفته شود.

گام 2: تنظیمات بهینه فایل‌های tempdb

تنظیمات صحیح فایل‌های tempdb برای جلوگیری از این خطا و بهبود عملکرد کلی سیستم بسیار مهم است:

افزایش حجم فایل‌های موجود tempdb

شما می‌توانید با استفاده از دستور ALTER DATABASE، اندازه فعلی فایل‌های داده و لاگ tempdb را افزایش دهید. این کار به SQL Server فضای بیشتری برای عملیات‌های جاری می‌دهد. به عنوان مثال، برای افزایش حجم فایل داده اصلی tempdev به 2 گیگابایت (2048 مگابایت) و فایل لاگ templog به 512 مگابایت، از دستورات زیر استفاده کنید:

ALTER DATABASE [tempdb] MODIFY FILE ( NAME = N'tempdev', SIZE = 2048MB );

ALTER DATABASE [tempdb] MODIFY FILE ( NAME = N'templog', SIZE = 512MB );

توجه داشته باشید که این مقادیر باید بر اساس نیاز سیستم شما تنظیم شوند و ممکن است لازم باشد آن‌ها را بیشتر افزایش دهید.

فعال‌سازی یا تنظیم Auto-Growth

اطمینان حاصل کنید که قابلیت Auto-Growth برای فایل‌های tempdb فعال است و مقدار آن به اندازه کافی بزرگ (اما نه خیلی بزرگ) تنظیم شده است تا از رشد فایل‌ها در هنگام نیاز جلوگیری نکند. یک مقدار متوازن، رشدی است که از گسترش‌های کوچک و مکرر جلوگیری کند اما باعث وقفه طولانی مدت در عملکرد نشود. به عنوان مثال، برای تنظیم Auto-Growth فایل داده به 256 مگابایت و فایل لاگ به 128 مگابایت، می‌توانید از دستورات زیر استفاده کنید:

ALTER DATABASE [tempdb] MODIFY FILE ( NAME = N'tempdev', FILEGROWTH = 256MB );

ALTER DATABASE [tempdb] MODIFY FILE ( NAME = N'templog', FILEGROWTH = 128MB );

همچنین، مطمئن شوید که MAXSIZE فایل‌ها روی UNLIMITED تنظیم شده یا مقدار کافی بزرگی دارد که بتواند نیازهای سیستم را برطرف کند.

اضافه کردن فایل‌های داده جدید به tempdb

بر اساس بهترین روش‌ها، توصیه می‌شود برای tempdb به تعداد هسته‌های منطقی CPU (تا 8 عدد) فایل داده ایجاد کنید. این کار به SQL Server اجازه می‌دهد تا عملیات‌های موازی را به طور مؤثرتری انجام دهد و رقابت (contention) در فایل‌های داده را کاهش دهد. هر فایل داده جدید باید در یک فایل‌گروپ (filegroup) جداگانه قرار گیرد، اگرچه برای tempdb فقط یک فایل‌گروپ Primary وجود دارد. مثال زیر نحوه اضافه کردن یک فایل داده جدید به tempdb را نشان می‌دهد:

ALTER DATABASE [tempdb] ADD FILE (NAME = N'tempdev2', FILENAME = N'D:\SQLData\tempdb\tempdev2.ndf', SIZE = 2048MB, FILEGROWTH = 256MB);

ALTER DATABASE [tempdb] ADD FILE (NAME = N'tempdev3', FILENAME = N'D:\SQLData\tempdb\tempdev3.ndf', SIZE = 2048MB, FILEGROWTH = 256MB);

توجه داشته باشید که مسیر FILENAME باید به یک درایو و پوشه معتبر اشاره کند و نام فایل‌ها (مانند tempdev2) باید منحصر به فرد باشند. تعداد فایل‌ها و اندازه اولیه و رشد آن‌ها باید بر اساس نیاز سیستم شما تنظیم شوند. این فایل‌ها باید بر روی درایوهایی قرار گیرند که دارای عملکرد بالا و فضای کافی هستند.

گام 3: شناسایی و بهینه‌سازی کوئری‌های پرمصرف tempdb

یکی از مؤثرترین راه‌ها برای جلوگیری از خطای 1105، شناسایی و بهینه‌سازی کوئری‌ها یا عملیات‌هایی است که بیشترین فضای tempdb را مصرف می‌کنند. شما می‌توانید از Dynamic Management Views (DMVs) برای مانیتورینگ مصرف tempdb استفاده کنید. کوئری زیر می‌تواند به شما کمک کند تا sessionهای فعال که در حال مصرف tempdb هستند را شناسایی کنید:

SELECT
    session_id,
    total_elapsed_time,
    cpu_time,
    reads,
    writes,
    (user_objects_alloc_page_count + internal_objects_alloc_page_count) * 8 / 1024 AS AllocatedSpaceMB,
    (user_objects_dealloc_page_count + internal_objects_dealloc_page_count) * 8 / 1024 AS DeallocatedSpaceMB,
    SUBSTRING(text, (statement_start_offset / 2) + 1,
              ((CASE statement_end_offset
                  WHEN -1 THEN DATALENGTH(text)
                  ELSE statement_end_offset
              END - statement_start_offset) / 2) + 1) AS query_text
FROM
    sys.dm_exec_sessions AS s
JOIN
    sys.dm_exec_requests AS r ON s.session_id = r.session_id
CROSS APPLY
    sys.dm_exec_sql_text(r.sql_handle)
WHERE
    (user_objects_alloc_page_count + internal_objects_alloc_page_count) > 0
ORDER BY
    AllocatedSpaceMB DESC;

این کوئری اطلاعاتی از قبیل ID نشست (session_id)، زمان سپری شده، زمان CPU، تعداد خواندن و نوشتن، و مهم‌تر از همه، فضای تخصیص یافته (AllocatedSpaceMB) و آزاد شده (DeallocatedSpaceMB) در tempdb را به مگابایت نشان می‌دهد، همراه با متن کوئری در حال اجرا. با بررسی خروجی این کوئری، می‌توانید کوئری‌های پرمصرف را شناسایی کرده و آن‌ها را بهینه‌سازی کنید. بهینه‌سازی می‌تواند شامل موارد زیر باشد:

• ایجاد ایندکس‌های مناسب: ایندکس‌های صحیح می‌توانند نیاز به مرتب‌سازی و اسکن کامل جداول را کاهش دهند.
• بازنویسی کوئری‌ها: ساده‌سازی منطق کوئری‌ها، استفاده از Joins مؤثرتر، و کاهش تعداد جداول موقت غیرضروری.
• استفاده از متغیرهای جدول به جای جداول موقت: برای مجموعه‌های داده کوچک‌تر، متغیرهای جدول (Table Variables) معمولاً سربار کمتری نسبت به جداول موقت (Temporary Tables) دارند زیرا در tempdb لاگ نمی‌شوند.
• کاهش تراکنش‌های طولانی‌مدت: اطمینان حاصل کنید که تراکنش‌ها به سرعت Commit یا Rollback می‌شوند تا از اشغال طولانی‌مدت فضای tempdb جلوگیری شود.

گام 4: انتقال tempdb به درایو مجزا و سریع‌تر

اگر مشکلات مربوط به عملکرد و فضای tempdb به طور مداوم رخ می‌دهد، انتقال فایل‌های tempdb به یک درایو مجزا و سریع‌تر (مانند SSD) می‌تواند یک راهکار بلندمدت و مؤثر باشد. این کار نه تنها فضای دیسک مربوط به درایو سیستم‌عامل را آزاد می‌کند، بلکه عملکرد tempdb را نیز بهبود می‌بخشد. برای انتقال tempdb، مراحل زیر را دنبال کنید:

ابتدا، مسیرهای جدید را برای فایل‌های داده و لاگ tempdb مشخص کنید. سپس، از دستور ALTER DATABASE برای تغییر مسیر فایل‌ها استفاده کنید:

ALTER DATABASE tempdb MODIFY FILE (NAME = tempdev, FILENAME = 'E:\NewTempDB\tempdata.mdf');

ALTER DATABASE tempdb MODIFY FILE (NAME = templog, FILENAME = 'F:\NewTempDB\templog.ldf');

توجه داشته باشید که 'E:\NewTempDB\tempdata.mdf' و 'F:\NewTempDB\templog.ldf' مسیرهای جدید هستند که باید قبل از اجرای دستور وجود داشته باشند. پس از اجرای این دستورات، برای اعمال تغییرات، باید سرویس SQL Server را راه‌اندازی مجدد کنید. پس از راه‌اندازی مجدد، tempdb در مسیرهای جدید ایجاد خواهد شد.

گام 5: راه‌اندازی مجدد سرویس SQL Server (راهکار موقت)

در موارد اضطراری و به عنوان یک راهکار سریع اما موقت، راه‌اندازی مجدد سرویس SQL Server می‌تواند فضای اشغال شده در tempdb را آزاد کند. این به این دلیل است که tempdb هر بار که سرویس SQL Server راه‌اندازی می‌شود، دوباره ساخته می‌شود و تمام اشیاء موقت و فضای تخصیص یافته به آن پاک می‌شوند. با این حال، این یک راه‌حل دائمی نیست و باید همزمان با راه‌اندازی مجدد، دلایل اصلی پر شدن tempdb را نیز بررسی و برطرف کنید تا از تکرار مجدد خطا جلوگیری شود. این راهکار می‌تواند به شما فرصت دهد تا راهکارهای دائمی‌تر مانند افزایش فضای دیسک یا بهینه‌سازی کوئری‌ها را پیاده‌سازی کنید.

در نهایت، مدیریت مؤثر tempdb نیاز به نظارت مداوم و تنظیمات دقیق دارد. با پیاده‌سازی راهکارهای فوق، می‌توانید به طور قابل توجهی احتمال بروز خطای 1105 را کاهش داده و عملکرد و پایداری سرور SQL Server خود را بهبود بخشید. بهینه‌سازی منابع، نظارت بر الگوهای مصرف و تنظیمات دقیق فایل‌ها، کلید جلوگیری از این خطای آزاردهنده است.