مشاركة عبر

إدارة مساحة الملف لقواعد البيانات في قاعدة بيانات azure SQL

  • مقالة

ينطبق على: قاعدة بيانات Azure SQL

توضح هذه المقالة أنواع مختلفة من مساحة التخزين لقواعد البيانات في قاعدة بيانات SQL Azure والخطوات التي يمكن اتخاذها عندما تحتاج مساحة الملف المخصصة إلى إدارة بشكل صريح.

ملاحظة

لا تنطبق هذه المقالة على مثيل Azure SQL المدار.

نظرة عامة

مع قاعدة بيانات azure SQL، توجد أنماط حمل العمل حيث يمكن أن يصبح توزيع ملفات البيانات الأساسية لقواعد البيانات أكبر من كمية صفحات البيانات المستخدمة. يمكن أن يحدث هذا الشرط عند زيادة المساحة المستخدمة وحذف البيانات لاحقا. والسبب هو أن مساحة الملف المخصصة لا يتم استردادها تلقائيا عند حذف البيانات.

مراقبة استخدام مساحة الملف وتقليص ملفات البيانات قد يكون ضروريا في السيناريوهات التالية:

  • السماح بنمو البيانات في تجمع مرن عندما تصل مساحة الملف المخصصة لقواعد البيانات الخاصة به إلى الحجم الأقصى للتجمع.
  • السماح بتقليل الحد الأقصى لحجم قاعدة بيانات أحادية أو تجمع مرن.
  • السماح بتغيير قاعدة بيانات أحادية أو تجمع مرن إلى مستوى خدمة أو مستوى أداء مختلف بحجم أقصى أقل.

ملاحظة

ينبغي ألا تعتبر عمليات تقليص العمليات عملية صيانة منتظمة. لا تتطلب ملفات البيانات والسجلات التي تنمو بسبب عمليات العمل العادية والمتكررة تقليص العمليات.

مراقبة استخدام مساحة الملف

تقيس معظم مقاييس مساحة التخزين المعروضة في واجهات برمجة التطبيقات التالية حجم صفحات البيانات المستخدمة فقط:

  • مقاييس مدير موارد Azure المستندة إلى واجهات برمجة التطبيقات بما في ذلك مقاييس الحصول على PowerShell

ومع ذلك، فإن واجهات برمجة التطبيقات التالية تقيس أيضا حجم المساحة المخصصة لقواعد البيانات والتجمعات المرنة:

فهم أنواع مساحة التخزين لقاعدة بيانات

فهم كميات مساحة التخزين التالية مهمة لإدارة مساحة الملف من قاعدة بيانات.

كمية قاعدة البيانات التعريف التعليقات
مساحة البيانات المستخدمة مقدار المساحة المستخدمة لتخزين بيانات قاعدة البيانات. بشكل عام، المساحة المستخدمة زيادات (إنقاص) على إدراج (حذف). في بعض الحالات، لا تتغير المساحة المستخدمة على إدراج أو حذف اعتمادا على مقدار ونمط البيانات المتضمنة في العملية وأي تجزئة. على سبيل المثال، حذف صف واحد من كل صفحة بيانات لا يقلل بالضرورة المساحة المستخدمة.
مساحة البيانات المخصصة مقدار مساحة الملف المنسقة المتوفرة لتخزين بيانات قاعدة البيانات. مساحة المخصصة ينمو تلقائيا، ولكن لا ينتقص بعد حذف. يضمن هذا السلوك أن الإدخالات المستقبلية أسرع بما أن الفضاء لا يحتاج إلى إعادة تنسيق.
مساحة البيانات المخصصة لكن غير مستخدمة الفرق بين مقدار مساحة البيانات المخصصة ومساحة البيانات المستخدمة. تمثل هذه الكمية الحد الأقصى من المساحة الخالية التي يمكن استعادتها عن طريق تقليص ملفات بيانات قاعدة البيانات.
حجم البيانات الأقصى الحد الأقصى للمساحة التي يمكن استخدامها لتخزين بيانات قاعدة البيانات. لا يمكن أن يتجاوز حجم مساحة البيانات المخصصة الحد الأقصى لحجم البيانات.

يوضح الرسم التخطيطي التالي العلاقة بين أنواع مختلفة من مساحة التخزين لقاعدة بيانات.

storage space types and relationships

الاستعلام عن قاعدة بيانات أحادية لمعلومات مساحة التخزين

يمكن استخدام الاستعلامات التالية لتحديد كميات مساحة التخزين لقاعدة بيانات أحادية.

مساحة بيانات قاعدة البيانات المستخدمة

تعديل الاستعلام التالي لإرجاع مقدار مساحة بيانات قاعدة البيانات المستخدمة. وحدات نتيجة الاستعلام بالميغابايت.

-- Connect to master
-- Database data space used in MB
SELECT TOP 1 storage_in_megabytes AS DatabaseDataSpaceUsedInMB
FROM sys.resource_stats
WHERE database_name = 'db1'
ORDER BY end_time DESC;

مساحة بيانات قاعدة البيانات المخصصة والمساحة المخصصة غير المستخدمة

استخدم الاستعلام التالي لإرجاع مقدار مساحة بيانات قاعدة البيانات المخصصة ومقدار المساحة غير المستخدمة المخصصة. وحدات نتيجة الاستعلام بالميغابايت.

-- Connect to database
-- Database data space allocated in MB and database data space allocated unused in MB
SELECT SUM(size/128.0) AS DatabaseDataSpaceAllocatedInMB,
SUM(size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128.0) AS DatabaseDataSpaceAllocatedUnusedInMB
FROM sys.database_files
GROUP BY type_desc
HAVING type_desc = 'ROWS';

الحد الأقصى لحجم بيانات قاعدة البيانات

قم بتعديل الاستعلام التالي لإرجاع الحجم الأقصى لبيانات قاعدة البيانات. وحدات نتيجة الاستعلام بالبايت.

-- Connect to database
-- Database data max size in bytes
SELECT DATABASEPROPERTYEX('db1', 'MaxSizeInBytes') AS DatabaseDataMaxSizeInBytes;

فهم أنواع مساحة التخزين لتجمّع مرن

فهم كميات مساحة التخزين التالية مهمة لإدارة مساحة الملف من تجمّع مرن.

كمية التجمعات المرنة التعريف التعليقات
مساحة البيانات المستخدمة مجموع مساحة البيانات المستخدمة من قبل جميع قواعد البيانات في التجمع المرن.
مساحة البيانات المخصصة مجموع مساحة البيانات المخصصة من قبل جميع قواعد البيانات في التجمع المرن.
مساحة البيانات المخصصة لكن غير مستخدمة الفرق بين مقدار مساحة البيانات المخصصة ومساحة البيانات المستخدمة من قبل جميع قواعد البيانات في التجمع المرن. تمثل هذه الكمية الحد الأقصى من المساحة المخصصة للتجمع المرن الذي يمكن استعادته عن طريق تقليص ملفات بيانات قاعدة البيانات.
حجم البيانات الأقصى الحد الأقصى لمقدار مساحة البيانات التي يمكن استخدامها بواسطة التجمع المرن لجميع قواعد البيانات الخاصة به. ينبغي ألا تتجاوز المساحة المخصصة للتجمع المرن الحجم الأقصى للتجمع المرن. إذا حدثت هذه الحالة، يمكن استعادة المساحة المخصصة غير المستخدمة عن طريق تقليص ملفات بيانات قاعدة البيانات.

ملاحظة

تشير رسالة الخطأ "لقد وصل التجمع المرن إلى أقصى حد التخزين الخاص به" إلى أنه تم تخصيص مساحة كافية لكائنات قاعدة البيانات لتلبية حد تخزين التجمع المرن، ولكن قد تكون هناك مساحة غير مستخدمة في تخصيص مساحة البيانات. فكر في زيادة حد تخزين التجمع المرن، أو كحل قصير الأجل، وتحرير مساحة البيانات باستخدام قسم استعادة المساحة المخصصة غير المستخدمة أدناه. يجب أيضاً أن تكون على دراية بتأثير الأداء السلبي المحتمل لتقليص ملفات قاعدة البيانات، راجع قسم صيانة الفهرس بعد تقليص أدناه.

الاستعلام عن تجمع مرن لمعلومات مساحة التخزين

يمكن استخدام الاستعلامات التالية لتحديد كميات مساحة التخزين لقاعدة بيانات أحادية.

مساحة بيانات تجمع مرن مستخدمة

تعديل الاستعلام التالي لإرجاع مقدار مساحة بيانات التجمع المرن المستخدمة. وحدات نتيجة الاستعلام بالميغابايت.

-- Connect to master
-- Elastic pool data space used in MB  
SELECT TOP 1 avg_storage_percent / 100.0 * elastic_pool_storage_limit_mb AS ElasticPoolDataSpaceUsedInMB
FROM sys.elastic_pool_resource_stats
WHERE elastic_pool_name = 'ep1'
ORDER BY end_time DESC;

مساحة بيانات قاعدة البيانات المخصصة والمساحة المخصصة غير المستخدمة

تعديل الأمثلة التالية لإرجاع جدول سرد المساحة المخصصة وغير المستخدمة المخصصة لكل قاعدة بيانات في تجمع مرن. يقوم الجدول بترتيب قواعد البيانات من قواعد البيانات التي تحتوي على أكبر قدر من المساحة المخصصة غير المستخدمة إلى أقل قدر من المساحة المخصصة غير المستخدمة. وحدات نتيجة الاستعلام بالميغابايت.

يمكن إضافة نتائج الاستعلام لتحديد المساحة المخصصة لكل قاعدة بيانات في التجمع معًا لتحديد المساحة الإجمالية المخصصة للتجمع المرن. ينبغي ألا تتجاوز المساحة المخصصة للتجمع المرن الحجم الأقصى للتجمع المرن.

هام

لا تزال الوحدة النمطيةPowerShell Azure Resource Manager مدعومة من قبل قاعدة بيانات SQL Azure، ولكن جميع التطويرات المستقبلية تكون للوحدة النمطية Az.Sql. ستستمر الوحدة النمطية AzureRM في تلقي إصلاحات الأخطاء حتى ديسمبر 2020 على الأقل. تتطابق وسائط الأوامر في الوحدة النمطية Az وفي الوحدات النمطية AzureRm بشكل كبير. لمعرفة المزيد حول توافقها، اطّلع على تقديم الوحدة النمطية Azure PowerShell Az الجديدة.

يتطلب البرنامج النصي PowerShell وحدة SQL Server PowerShell - راجع تنزيل وحدة PowerShellللتركيب.

$resourceGroupName = "<resourceGroupName>"
$serverName = "<serverName>"
$poolName = "<poolName>"
$userName = "<userName>"
$password = "<password>"

# get list of databases in elastic pool
$databasesInPool = Get-AzSqlElasticPoolDatabase -ResourceGroupName $resourceGroupName `
    -ServerName $serverName -ElasticPoolName $poolName
$databaseStorageMetrics = @()

# for each database in the elastic pool, get space allocated in MB and space allocated unused in MB
foreach ($database in $databasesInPool) {
    $sqlCommand = "SELECT DB_NAME() as DatabaseName, `
    SUM(size/128.0) AS DatabaseDataSpaceAllocatedInMB, `
    SUM(size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128.0) AS DatabaseDataSpaceAllocatedUnusedInMB `
    FROM sys.database_files `
    GROUP BY type_desc `
    HAVING type_desc = 'ROWS'"
    $serverFqdn = "tcp:" + $serverName + ".database.windows.net,1433"
    $databaseStorageMetrics = $databaseStorageMetrics + 
        (Invoke-Sqlcmd -ServerInstance $serverFqdn -Database $database.DatabaseName `
            -Username $userName -Password $password -Query $sqlCommand)
}

# display databases in descending order of space allocated unused
Write-Output "`n" "ElasticPoolName: $poolName"
Write-Output $databaseStorageMetrics | Sort -Property DatabaseDataSpaceAllocatedUnusedInMB -Descending | Format-Table

لقطة الشاشة التالية مثال على إخراج البرنامج النصي:

elastic pool allocated space and unused allocated space example

الحد الأقصى لحجم بيانات التجمع المرن

تعديل الاستعلام T-SQL التالي لإرجاع آخر حجم بيانات تجمع مرن مسجل. وحدات نتيجة الاستعلام بالميغابايت.

-- Connect to master
-- Elastic pools max size in MB
SELECT TOP 1 elastic_pool_storage_limit_mb AS ElasticPoolMaxSizeInMB
FROM sys.elastic_pool_resource_stats
WHERE elastic_pool_name = 'ep1'
ORDER BY end_time DESC;

استعادة المساحة المخصصة غير المستخدمة

هام

تؤثر أوامر تقليص أداء قاعدة البيانات أثناء التشغيل، وإذا أمكن ينبغي تشغيلها أثناء فترات الاستخدام المنخفض.

تقليص ملفات البيانات

بسبب تأثير محتمل على أداء قاعدة البيانات، لا تقوم قاعدة بيانات Azure SQL تلقائيا بتقليص ملفات البيانات. ومع ذلك، قد يقوم العملاء بتقليص ملفات البيانات عبر الخدمة الذاتية في وقت يختارونه. لا ينبغي أن تكون هذه عملية مجدولة بانتظام، بل تحدث لمرة واحدة استجابة لانخفاض كبير في استهلاك مساحة ملف البيانات المستخدمة.

تلميح

لا يوصى بتقليص ملفات البيانات إذا كان عبء عمل التطبيق العادي سيؤدي إلى زيادة حجم الملفات إلى نفس الحجم المخصص مرة أخرى.

في Azure SQL Database، لتقليص حجم الملفات، يمكنك استخدام أوامر DBCC SHRINKDATABASE أو DBCC SHRINKFILE:

  • DBCC SHRINKDATABASE يتقلص جميع البيانات وملفات السجل في قاعدة البيانات باستخدام أمر واحد. يقوم الأمر بتقليص ملف بيانات واحد في كل مرة، ما قد يستغرق وقتاً طويلاً لقواعد البيانات الأكبر حجماً. كما أنه يقلص ملف السجل، وهو أمر غير ضروري عادةً لأن Azure SQL Database تقلص ملفات السجل تلقائياً حسب الحاجة.
  • DBCC SHRINKFILE الأمر يدعم سيناريوهات أكثر تقدما:
    • يمكن أن تستهدف الملفات الفردية حسب الحاجة، بدلا من تقليص كافة الملفات في قاعدة البيانات.
    • يمكن تشغيل كل أمر DBCC SHRINKFILE بالتوازي مع أوامر DBCC SHRINKFILE أخرى لتقليص الملفات المتعددة في نفس الوقت وتقليل الوقت الإجمالي للتقليص، على حساب زيادة استخدام الموارد وفرصة أكبر لحظر استفسارات المستخدم، إذا كانت كذلك تنفيذ أثناء التقليص.
    • إذا كان الجزء الخلفي من الملف لا يحتوي على بيانات، فيمكنه تقليل حجم الملف المخصص بشكل أسرع عن طريق تحديد الوسيطة TRUNCATEONLY . لا يتطلب ذلك نقل البيانات داخل الملف.
  • لمزيد من المعلومات حول أوامر التقليص هذه، راجع قاعدة SHRINKDATABASE DBCC وDBCC SHRINKFILE.

يجب تنفيذ الأمثلة التالية أثناء الاتصال بقاعدة بيانات المستخدم الهدف، وليس master قاعدة البيانات.

لاستخدام DBCC SHRINKDATABASE لتقليص كافة البيانات وتسجيل الملفات في قاعدة بيانات معينة:

-- Shrink database data space allocated.
DBCC SHRINKDATABASE (N'database_name');

في Azure SQL Database، قد تحتوي قاعدة البيانات على ملف بيانات واحد أو أكثر، يتم إنشاؤه تلقائياً مع نمو البيانات. لتحديد تخطيط ملف قاعدة البيانات الخاصة بك، بما في ذلك الحجم المستخدم والمخصص لكل ملف، استعلام عن طريقة عرض الكتالوج sys.database_files باستخدام نموذج البرنامج النصي التالي:

-- Review file properties, including file_id and name values to reference in shrink commands
SELECT file_id,
       name,
       CAST(FILEPROPERTY(name, 'SpaceUsed') AS bigint) * 8 / 1024. AS space_used_mb,
       CAST(size AS bigint) * 8 / 1024. AS space_allocated_mb,
       CAST(max_size AS bigint) * 8 / 1024. AS max_file_size_mb
FROM sys.database_files
WHERE type_desc IN ('ROWS','LOG');

يمكنك تنفيذ تقليص لملف واحد فقط عن طريق الأمر DBCC SHRINKFILE، على سبيل المثال:

-- Shrink database data file named 'data_0` by removing all unused at the end of the file, if any.
DBCC SHRINKFILE ('data_0', TRUNCATEONLY);
GO

كن على دراية بتأثير الأداء السلبي المحتمل لتقليص ملفات قاعدة البيانات، راجع قسم صيانة الفهرس بعد تقليص أدناه.

عملية تقليص ملف سجل المعاملات

بعكس ملفات البيانات، تقوم قاعدة بيانات Azure SQL تلقائيا بعمليات تقليص ملف سجل المعاملات لتجنب الاستخدام المفرط للمساحة الذي يمكن أن يؤدي إلى أخطاء خارج المساحة. ليس من الضروري عادةً أن يقوم العملاء بتقليص ملف سجل المعاملات.

في Premium و مستويات الخدمة الهامة للأعمال، إذا أصبح سجل المعاملات كبيرا، قد يساهم بشكل كبير في استهلاك التخزين المحلي نحو الحد الأقصى للتخزين المحلي. إذا كان استهلاك التخزين المحلي قريبًا من الحد الأقصى ، فقد يختار العملاء تقليص سجل المعاملات باستخدام الأمر SHRINKFILE DBCCكما هو موضح في المثال التالي. يؤدي هذا إلى تحرير التخزين المحلي بمجرد اكتمال الأمر، دون انتظار عملية التقليص التلقائي الدورية.

يجب تنفيذ الأمثلة التالية أثناء الاتصال بقاعدة بيانات المستخدم الهدف، وليس بقاعدة البيانات الرئيسية.

-- Shrink the database log file (always file_id 2), by removing all unused space at the end of the file, if any.
DBCC SHRINKFILE (2, TRUNCATEONLY);

التقليص التلقائي

كبديل لتقليص ملفات البيانات يدوياً، يمكن تمكين التقليص التلقائي لقاعدة البيانات. ومع ذلك، يمكن أن يكون التقليص التلقائي أقل فعالية في استعادة مساحة الملف من DBCC SHRINKDATABASE و DBCC SHRINKFILE.

بشكل افتراضي، يتم تعطيل التقليص التلقائي، وهو أمر موصى به لمعظم قواعد البيانات. إذا أصبح من الضروري تمكين التقليص التلقائي، فمن المستحسن تعطيله حالما يتم تحقيق أهداف إدارة الفضاء، بدلا من الاحتفاظ بها ممكنة بشكل دائم. للحصول على مزيد من المعلومات، راجع قسم اعتبارات ل AUTO_SHRINK.

على سبيل المثال، يمكن أن يكون التقليص التلقائي مفيداً في السيناريو المحدد حيث يحتوي التجمع المرن على العديد من قواعد البيانات التي تشهد نمواً كبيراً وتقليصاً في مساحة ملف البيانات المستخدمة، ما يتسبب في اقتراب التجمع من الحد الأقصى للحجم. هذا ليس سيناريو شائع.

لتمكين التقليص التلقائي، قم بتنفيذ الأمر التالي أثناء الاتصال بقاعدة البيانات الخاصة بك (وليس قاعدة البيانات الرئيسة).

-- Enable auto-shrink for the current database.
ALTER DATABASE CURRENT SET AUTO_SHRINK ON;

للحصول على مزيدٍ من المعلومات حول هذا الأمر، راجع خيارات مجموعة قاعدة البيانات.

صيانة الفهرس بعد الانكماش

بعد اكتمال عملية تقليص مقابل ملفات البيانات، قد تصبح الفهارس مجزأة. هذا يقلل من فعالية تحسين الأداء لأحمال عمل معينة، مثل الاستعلامات التي تستخدم عمليات مسح ضوئي كبيرة. إذا حدث تدهور في الأداء بعد اكتمال عملية التقليص، خذ بعين الاعتبار صيانة الفهرس لإعادة إنشاء الفهارس. ضع في اعتبارك أن عمليات إعادة بناء الفهرس تتطلب مساحة خالية في قاعدة البيانات، وبالتالي قد تتسبب في زيادة المساحة المخصصة، ما يبطل تأثير التقليص.

لمزيد من المعلومات حول صيانة الفهرس، راجع تحسين صيانة الفهرس لتحسين أداء الاستعلام وتقليل استهلاك الموارد.

تقليص قواعد البيانات الكبيرة

عندما تكون المساحة المخصصة لقاعدة البيانات بمئات الغيغابايت أو أعلى، فقد يتطلب التقليص وقتاً طويلاً لإكماله، وغالباً ما يتم قياسه بالساعات أو بالأيام لقواعد البيانات متعددة تيرابايت. هناك تحسينات للعملية وأفضل الممارسات التي يمكنك استخدامها لجعل هذه العملية أكثر كفاءة وأقل تأثيراً على أعباء عمل التطبيق.

التقاط خط الأساس لاستخدام المساحة

قبل البدء في التقليص، التقط المساحة المستخدمة والمخصصة حالياً في كل ملف قاعدة بيانات عن طريق تنفيذ استعلام استخدام المساحة التالية:

SELECT file_id,
       CAST(FILEPROPERTY(name, 'SpaceUsed') AS bigint) * 8 / 1024. AS space_used_mb,
       CAST(size AS bigint) * 8 / 1024. AS space_allocated_mb,
       CAST(max_size AS bigint) * 8 / 1024. AS max_size_mb
FROM sys.database_files
WHERE type_desc = 'ROWS';

بمجرد اكتمال التقليص، يمكنك تنفيذ هذا الاستعلام مرة أخرى ومقارنة النتيجة بخط الأساس الأولي.

ملفات البيانات المقطوعة

يوصى بتنفيذ التقليص أولاً لكل ملف بيانات باستخدام المعاملTRUNCATEONLY. بهذه الطريقة، إذا كان هناك أي مساحة مخصصة ولكن غير مستخدمة في نهاية الملف، فستتم إزالتها بسرعة وبدون أي نقل للبيانات. يقتطع الأمر التالي عينة من ملف البيانات مع file_id 4:

DBCC SHRINKFILE (4, TRUNCATEONLY);

بمجرد تنفيذ هذا الأمر لكل ملف بيانات، يمكنك إعادة تشغيل استعلام استخدام المساحة لمعرفة تقليل المساحة المخصصة، إن وجدت. يمكنك أيضاً عرض المساحة المخصصة لقاعدة البيانات في مدخل Microsoft Azure.

تقييم كثافة صفحة الفهرس

إذا لم ينتج عن اقتطاع ملفات البيانات تقليل المساحة المخصصة بشكل كافٍ، فستحتاج إلى تقليص ملفات البيانات. ومع ذلك، كخطوة اختيارية ولكن موصى بها، يجب أولاً تحديد متوسط ​​كثافة الصفحة للفهارس في قاعدة البيانات. للحصول على نفس كمية البيانات، سيكتمل التقليص بشكل أسرع إذا كانت كثافة الصفحة عالية، لأنه سيتعين عليه نقل عدد أقل من الصفحات. إذا كانت كثافة الصفحة منخفضة لبعض الفهارس، ففكر في إجراء صيانة لهذه الفهارس لزيادة كثافة الصفحة قبل تقليص ملفات البيانات. سيسمح هذا أيضاً بالانكماش لتحقيق تقليل أعمق في مساحة التخزين المخصصة.

لتحديد كثافة الصفحة لجميع الفهارس في قاعدة البيانات، استخدم الاستعلام التالي. يتم الإبلاغ عن كثافة الصفحة في العمودavg_page_space_used_in_percent.

SELECT OBJECT_SCHEMA_NAME(ips.object_id) AS schema_name,
       OBJECT_NAME(ips.object_id) AS object_name,
       i.name AS index_name,
       i.type_desc AS index_type,
       ips.avg_page_space_used_in_percent,
       ips.avg_fragmentation_in_percent,
       ips.page_count,
       ips.alloc_unit_type_desc,
       ips.ghost_record_count
FROM sys.dm_db_index_physical_stats(DB_ID(), default, default, default, 'SAMPLED') AS ips
INNER JOIN sys.indexes AS i 
ON ips.object_id = i.object_id
   AND
   ips.index_id = i.index_id
ORDER BY page_count DESC;

إذا كانت هناك فهارس ذات عدد صفحات مرتفع بها كثافة صفحات أقل من 60-70٪، ففكر في إعادة بناء أو إعادة تنظيم هذه الفهارس قبل تقليص ملفات البيانات.

ملاحظة

بالنسبة لقواعد البيانات الأكبر حجماً، قد يستغرق استعلام لتحديد كثافة الصفحة وقتاً طويلاً (ساعات) حتى يكتمل. بالإضافة إلى ذلك، تتطلب إعادة بناء أو إعادة تنظيم الفهارس الكبيرة أيضاً استخداماً كبيراً للوقت والموارد. هناك مفاضلة بين قضاء وقت إضافي في زيادة كثافة الصفحة من ناحية، وتقليل مدة التقلص وتحقيق توفير مساحة أكبر من ناحية أخرى.

فيما يلي نموذج لأمر لإعادة إنشاء فهرس وزيادة كثافة صفحته:

ALTER INDEX [index_name] ON [schema_name].[table_name] REBUILD WITH (FILLFACTOR = 100, MAXDOP = 8, ONLINE = ON (WAIT_AT_LOW_PRIORITY (MAX_DURATION = 5 MINUTES, ABORT_AFTER_WAIT = NONE)), RESUMABLE = ON);

يبدأ هذا الأمر في إعادة بناء فهرس عبر الإنترنت وقابل للاستئناف. يتيح هذا لأحمال العمل المتزامنة الاستمرار في استخدام الجدول أثناء استمرار عملية إعادة البناء، ويسمح لك باستئناف إعادة البناء إذا تمت مقاطعتها لأي سبب من الأسباب. ومع ذلك، فإن هذا النوع من إعادة البناء يكون أبطأ من إعادة البناء دون اتصال، ما يمنع الوصول إلى الجدول. إذا لم تكن هناك أعباء عمل أخرى تحتاج إلى الوصول إلى الجدول أثناء إعادة البناء، فاضبط خياري ONLINE وRESUMABLE على OFFوأزل العبارةWAIT_AT_LOW_PRIORITY.

إذا كانت هناك عدة فهارس ذات كثافة صفحات منخفضة، فقد تتمكن من إعادة بنائها بالتوازي في جلسات قاعدة بيانات متعددة لتسريع العملية. ومع ذلك، تأكد من أنك لا تقترب من حدود موارد قاعدة البيانات من خلال القيام بذلك، واترك مساحة كافية للموارد لأحمال عمل التطبيقات التي قد تكون قيد التشغيل. راقب استهلاك الموارد (CPU،Data IO،Log IO) في مدخل Microsoft Azure أو باستخدام طريقة العرض sys.dm_db_resource_stats، وابدأ عمليات إعادة بناء متوازية إضافية فقط إذا ظل استخدام الموارد في كل من هذه الأبعاد أقل من 100٪. إذا كان استخدام CPU أو Data IO أو Log IO عند 100٪، فيمكنك توسيع نطاق قاعدة البيانات للحصول على المزيد من مراكز وحدة المعالجة المركزية وزيادة إنتاجية الإدخال والإخراج. قد يؤدي ذلك إلى تمكين عمليات إعادة البناء المتوازية الإضافية لإكمال العملية بشكل أسرع.

لمعرفة المزيد حول صيانة الفهرس، راجع تحسين صيانة الفهرس لتحسين أداء الاستعلام وتقليل استهلاك الموارد.

تقليص ملفات البيانات المتعددة

كما ذكرنا سابقاً، يعد تقليص حركة البيانات عملية طويلة الأمد. إذا كانت قاعدة البيانات تحتوي على ملفات بيانات متعددة، فيمكنك تسريع العملية عن طريق تقليص ملفات بيانات متعددة بشكل متوازٍ. يمكنك القيام بذلك عن طريق فتح جلسات قاعدة بيانات متعددة، واستخدام DBCC SHRINKFILEفي كل جلسة بقيمةfile_id مختلفة. على غرار إعادة بناء الفهارس في وقت سابق، تأكد من أن لديك مساحة كافية للموارد (وحدة المعالجة المركزية، وبيانات الإدخال/ الإخراج، وسجل الإدخال والإخراج) قبل بدء كل أمر تقليص موازٍ جديد.

يؤدي الأمر النموذجي التالي إلى تقليص ملف البيانات باستخدام file_id 4، في محاولة لتقليل حجمه المخصص إلى 52000 ميغابايت عن طريق نقل الصفحات داخل الملف:

DBCC SHRINKFILE (4, 52000);

إذا كنت ترغب في تقليل المساحة المخصصة للملف إلى الحد الأدنى الممكن، فنفّذ العبارة دون تحديد الحجم الهدف:

DBCC SHRINKFILE (4);

إذا كان حمل العمل يعمل بالتزامن مع التقليص، فقد يبدأ في استخدام مساحة التخزين التي تم تحريرها بواسطة التقليص قبل أن يكتمل تقليص الملف ويقتطع. في هذه الحالة، لن يتمكن التقليص من تقليل المساحة المخصصة للهدف المحدد.

يمكنك التخفيف من ذلك عن طريق تقليص كل ملف في خطوات أصغر. هذا يعني أنه في الأمر DBCC SHRINKFILE، تقوم بتعيين الهدف الذي يكون أصغر قليلاً من المساحة المخصصة حالياً للملف، كما هو موضح في نتائج استعلام استخدام المساحة الأساسية. على سبيل المثال، إذا كانت المساحة المخصصة للملف مع file_id 4 هي 200000 ميغابايت، وتريد تقليصها إلى 100000 ميغابايت، يمكنك أولاً تعيين المستهدف إلى 170000 ميغابايت:

DBCC SHRINKFILE (4, 170000);

بمجرد اكتمال هذا الأمر، سيكون قد اقتطع الملف وقلل حجمه المخصص إلى 170000 ميغابايت. يمكنك بعد ذلك تكرار هذا الأمر، مع ضبط المستهدف أولاً على 140.000 ميغابايت، ثم إلى 110.000 ميغابايت، وما إلى ذلك، حتى يتم تقليص الملف إلى الحجم المطلوب. إذا اكتمل الأمر ولم يتم اقتطاع الملف، فاستخدم خطوات أصغر، على سبيل المثال 15000 ميغابايت بدلاً من 30000 ميغابايت.

لمراقبة تقدم تقليص جميع جلسات تقليص التشغيل المتزامنة، يمكنك استخدام الاستعلام التالي:

SELECT command,
       percent_complete,
       status,
       wait_resource,
       session_id,
       wait_type,
       blocking_session_id,
       cpu_time,
       reads,
       CAST(((DATEDIFF(s,start_time, GETDATE()))/3600) AS varchar) + ' hour(s), '
                     + CAST((DATEDIFF(s,start_time, GETDATE())%3600)/60 AS varchar) + 'min, '
                     + CAST((DATEDIFF(s,start_time, GETDATE())%60) AS varchar) + ' sec' AS running_time
FROM sys.dm_exec_requests AS r
LEFT JOIN sys.databases AS d
ON r.database_id = d.database_id
WHERE r.command IN ('DbccSpaceReclaim','DbccFilesCompact','DbccLOBCompact','DBCC');

ملاحظة

قد يكون تقدم الانكماش غير خطي، وقد تظل القيمة الموجودة في العمود percent_completeبدون تغيير فعلياً لفترات طويلة من الوقت، على الرغم من استمرار الانكماش.

بمجرد اكتمال تقليص جميع ملفات البيانات، أعد تشغيل استعلام استخدام المساحة (أو حدد مدخل Microsoft Azure) لتحديد التخفيض الناتج في حجم التخزين المخصص. إذا كانت غير كافية ولا يزال هناك فرق كبير بين المساحة المستخدمة والمساحة المخصصة، يمكنك إعادة إنشاء الفهارس كما هو موضح سابقاً. قد يؤدي هذا إلى زيادة المساحة المخصصة بشكل مؤقت، ولكن تقليص ملفات البيانات مرة أخرى بعد إعادة بناء الفهارس يجب أن يؤدي إلى تقليل أكبر في المساحة المخصصة.

أخطاء عابرة أثناء التقليص

في بعض الأحيان، قد يفشل أمر التقليص مع العديد من الأخطاء مثل المهلات وحالات الجمود. بشكل عام، هذه الأخطاء عابرة، ولا تحدث مرة أخرى إذا تم تكرار الأمر نفسه. إذا فشل التقليص مع حدوث خطأ، فسيتم الاحتفاظ بالتقدم الذي أحرزه حتى الآن في نقل صفحات البيانات، ويمكن تنفيذ أمر التقليص نفسه مرة أخرى لمواصلة تقليص الملف.

يوضح نموذج البرنامج النصي التالي كيف يمكنك تشغيل تقليص في حلقة إعادة المحاولة لإعادة المحاولة تلقائياً إلى عدد قابل للتكوين من المرات عند حدوث خطأ انتهاء المهلة أو خطأ في حالة توقف تام. أسلوب إعادة المحاولة هذا قابل للتطبيق على العديد من الأخطاء الأخرى التي قد تحدث أثناء التقليص.

DECLARE @RetryCount int = 3; -- adjust to configure desired number of retries
DECLARE @Delay char(12);

-- Retry loop
WHILE @RetryCount >= 0
BEGIN

BEGIN TRY

DBCC SHRINKFILE (1); -- adjust file_id and other shrink parameters

-- Exit retry loop on successful execution
SELECT @RetryCount = -1;

END TRY
BEGIN CATCH
    -- Retry for the declared number of times without raising an error if deadlocked or timed out waiting for a lock
    IF ERROR_NUMBER() IN (1205, 49516) AND @RetryCount > 0
    BEGIN
        SELECT @RetryCount -= 1;

        PRINT CONCAT('Retry at ', SYSUTCDATETIME());

        -- Wait for a random period of time between 1 and 10 seconds before retrying
        SELECT @Delay = '00:00:0' + CAST(CAST(1 + RAND() * 8.999 AS decimal(5,3)) AS varchar(5));
        WAITFOR DELAY @Delay;
    END
    ELSE -- Raise error and exit loop
    BEGIN
        SELECT @RetryCount = -1;
        THROW;
    END
END CATCH
END;

بالإضافة إلى المهلات وحالات الجمود، قد يواجه التقليص أخطاء بسبب بعض المشكلات المعروفة.

تم إرجاع الأخطاء وخطوات التخفيف كما يلي:

  • رقم الخطأ: 49503، رسالة الخطأ: %. * ls: الصفحة% d:% d تعذر نقلها لأنها صفحة مخزن إصدار دائم خارج الصف. سبب تعليق الصفحة:% ls. الطابع الزمني لتعطيل الصفحة:% I64d.

يحدث هذا الخطأ عند وجود عمليات نشطة قيد التشغيل لفترة طويلة والتي أدت إلى إنشاء إصدارات الصفوف في مخزن الإصدارات الدائم (PVS). لا يمكن نقل الصفحات التي تحتوي على إصدارات الصفوف هذه عن طريق التقليص، وبالتالي لا يمكن إحراز تقدم ويفشل مع هذا الخطأ.

للتخفيف، عليك الانتظار حتى تكتمل هذه العمليات طويلة الأمد. بدلاً من ذلك، يمكنك تحديد وإنهاء هذه العمليات طويلة الأمد، ولكن هذا يمكن أن يؤثر على تطبيقك إذا لم يتعامل مع إخفاقات العمليات بأمان. تتمثل إحدى طرق العثور على العمليات طويلة الأمد في تشغيل الاستعلام التالي في قاعدة البيانات حيث قمت بتشغيل أمر التقليص:

-- Transactions sorted by duration
SELECT st.session_id,
       dt.database_transaction_begin_time,
       DATEDIFF(second, dt.database_transaction_begin_time, CURRENT_TIMESTAMP) AS transaction_duration_seconds,
       dt.database_transaction_log_bytes_used,
       dt.database_transaction_log_bytes_reserved,
       st.is_user_transaction,
       st.open_transaction_count,
       ib.event_type,
       ib.parameters,
       ib.event_info
FROM sys.dm_tran_database_transactions AS dt
INNER JOIN sys.dm_tran_session_transactions AS st
ON dt.transaction_id = st.transaction_id
OUTER APPLY sys.dm_exec_input_buffer(st.session_id, default) AS ib
WHERE dt.database_id = DB_ID()
ORDER BY transaction_duration_seconds DESC;

يمكنك إنهاء معاملة باستخدام الأمر KILLوتحديد القيمة session_idالمقترنة من نتيجة الاستعلام:

KILL 4242; -- replace 4242 with the session_id value from query results

تنبيه

قد يؤثر إنهاء المعاملة سلباً على أحمال العمل.

بمجرد إنهاء العمليات طويلة الأمد أو اكتمالها، ستقوم مهمة داخلية في الخلفية بتنظيف إصدارات الصف التي لم تعد مطلوبة بعد مرور بعض الوقت. يمكنك مراقبة حجم PVS لقياس تقدم التنظيف باستخدام الاستعلام التالي. قم بتشغيل الاستعلام في قاعدة البيانات حيث قمت بتشغيل أمر التقليص:

SELECT pvss.persistent_version_store_size_kb / 1024. / 1024 AS persistent_version_store_size_gb,
       pvss.online_index_version_store_size_kb / 1024. / 1024 AS online_index_version_store_size_gb,
       pvss.current_aborted_transaction_count,
       pvss.aborted_version_cleaner_start_time,
       pvss.aborted_version_cleaner_end_time,
       dt.database_transaction_begin_time AS oldest_transaction_begin_time,
       asdt.session_id AS active_transaction_session_id,
       asdt.elapsed_time_seconds AS active_transaction_elapsed_time_seconds
FROM sys.dm_tran_persistent_version_store_stats AS pvss
LEFT JOIN sys.dm_tran_database_transactions AS dt
ON pvss.oldest_active_transaction_id = dt.transaction_id
   AND
   pvss.database_id = dt.database_id
LEFT JOIN sys.dm_tran_active_snapshot_database_transactions AS asdt
ON pvss.min_transaction_timestamp = asdt.transaction_sequence_num
   OR
   pvss.online_index_min_transaction_timestamp = asdt.transaction_sequence_num
WHERE pvss.database_id = DB_ID();

بمجرد تقليل حجم PVS الذي تم الإبلاغ عنه في العمود persistent_version_store_size_gb إلى حد كبير مقارنةً بحجمه الأصلي، يجب أن تنجح إعادة تشغيل الانكماش.

  • رقم الخطأ: 5223، رسالة الخطأ: %. * ls: صفحة فارغة% d: تعذر إلغاء تخصيص% d.

قد يحدث هذا الخطأ إذا كانت هناك عمليات صيانة فهرس جارية مثلALTER INDEX. أعد محاولة أمر التقليص بعد اكتمال هذه العمليات.

إذا استمر هذا الخطأ، فقد يلزم إعادة بناء الفهرس المرتبط. للعثور على الفهرس المراد إعادة بنائه، قم بتنفيذ الاستعلام التالي في نفس قاعدة البيانات حيث قمت بتشغيل أمر التقليص:

SELECT OBJECT_SCHEMA_NAME(pg.object_id) AS schema_name,
       OBJECT_NAME(pg.object_id) AS object_name,
       i.name AS index_name,
       p.partition_number
FROM sys.dm_db_page_info(DB_ID(), <file_id>, <page_id>, default) AS pg
INNER JOIN sys.indexes AS i
ON pg.object_id = i.object_id
   AND
   pg.index_id = i.index_id
INNER JOIN sys.partitions AS p
ON pg.partition_id = p.partition_id;

قبل تنفيذ هذا الاستعلام، استبدل العناصر النائبة <file_id> و<page_id> بالقيم الفعلية من رسالة الخطأ التي تلقيتها. على سبيل المثال، إذا كانت الرسالة هي Empty page 1:62669 could not be deallocated، فحينئذ يكون <file_id> هو 1 ويكون <page_id> هو 62669.

أعد إنشاء الفهرس المعرف بواسطة الاستعلام، وأعد محاولة أمر التقليص.

  • رقم الخطأ: 5201، رسالة الخطأ: قاعدة SHRINKDATABASE DBCC: تم تخطي معرّف الملف% d من معرّف قاعدة البيانات% d لأن الملف لا يحتوي على مساحة خالية كافية للاسترداد.

يعني هذا الخطأ أنه لا يمكن تقليص أكثر لملف البيانات. يمكنك الانتقال إلى ملف البيانات التالي.

الخطوات التالية