ApsaraDB RDS:Problèmes d'espace insuffisant pour RDS for SQL Server

La base de données tempdb est une base de données système dans SQL Server qui stocke des données temporaires. L'espace du fichier de données dans tempdb est fréquemment utilisé dans de nombreux scénarios, tels que :

• Objets utilisateur : Tables temporaires créées par les utilisateurs.

• Objets internes : Tables temporaires générées en interne par SQL Server.

• Magasin de versions : Lorsque l'isolation instantanée ou l'instantané de lecture validée est activée pour une base de données, les informations de versioning sont stockées dans tempdb.

Si certaines opérations, telles que des transactions de longue durée, la création de nombreuses tables temporaires ou l'isolation instantanée, utilisent une grande quantité d'espace, le fichier de données peut devenir volumineux. Cela signifie que la taille du fichier augmente considérablement au-delà de sa plage normale. Pour plus d'informations, consultez le tutoriel officiel Microsoft pour la base de données tempdb.

Archiver ou supprimer des données

Supprimez ou migrez les données rarement consultées — par exemple, les anciens enregistrements historiques — vers une autre instance ou un format d'archive. Cela réduit directement l'espace de données utilisé et constitue la solution à long terme la plus efficace, bien qu'elle nécessite des modifications de la logique applicative et de la conception de la base de données.

Compresser les données

Les instances exécutant SQL Server 2016 ou versions ultérieures, ainsi que les instances Enterprise Edition exécutant des versions antérieures, prennent en charge la compression de ligne et de page au niveau de la table, de l'index ou de la partition. Pour plus de détails, consultez Compression des données.

Les économies varient considérablement (10–90 %) selon le schéma, les types de colonnes et la distribution des données. Utilisez sp_estimate_data_compression_savings pour estimer les économies avant d'activer la compression.

La modification des paramètres de compression est une opération DDL (Data Definition Language). Sur les grandes tables, cela peut provoquer des verrous de table de longue durée. Exécutez-la pendant les heures creuses.

Sur les instances Enterprise Edition, définissez ONLINE = ON pour minimiser l'impact sur l'activité.

La compression augmente la surcharge CPU. Activez-la uniquement sur les grandes tables où les économies d'espace justifient le coût.

Défragmenter les index

Une fragmentation élevée des index ralentit les requêtes et gaspille du stockage. Pour afficher les taux de fragmentation, accédez à Autonomy Services > Performance Optimization et cliquez sur l'onglet Index Usage. La console affiche le taux de fragmentation par table et suggère s'il faut reconstruire ou réorganiser chaque index.

Le taux de fragmentation des index mesure le pourcentage de pages d'index logiquement adjacentes qui ne sont pas physiquement adjacentes. Cela diffère du pourcentage d'espace libre dans les pages. Pour mesurer l'espace libre moyen par page, interrogez sys.dm_db_index_physical_stats en mode SAMPLED ou DETAILED et vérifiez la colonne avg_page_space_used_in_percent . Pour plus de détails, consultez sys.dm_db_index_physical_stats (Transact-SQL) . Cette requête lit de nombreuses pages d'index — exécutez-la pendant les heures creuses.

Reconstruction d'index (REBUILD) : Recrée complètement l'index. Plus efficace que la réorganisation pour les index fortement fragmentés. Par défaut, verrouille la table pendant l'opération. Sur les instances Enterprise Edition, définissez ONLINE = ON pour éviter les verrous de table prolongés.

ALTER INDEX <IX_YourIndexName> ON <YourTableName> REBUILD WITH (ONLINE = ON);

Après la reconstruction, la console met à jour les statistiques d'index de manière asynchrone. Cliquez sur Recollect pour obtenir les dernières données, puis cliquez sur Export Script pour télécharger et vérifier le taux de fragmentation.

Réorganisation d'index (REORGANIZE) : Restructure les pages de niveau feuille sans verrouiller la table. Moins efficace que la reconstruction, mais appropriée pour les index faiblement fragmentés.

-- This script applies only to SQL Server 2012 and later versions. Specify the following parameters before running.
DECLARE @dbName NVARCHAR(128) = 'YourDBName'  -- Database name
DECLARE @fileName NVARCHAR(128)               -- Data file name
DECLARE @targetSize INT = 2000                -- Target size (MB)
DECLARE @shrinkSize INT = 5120                -- Size to shrink per iteration (MB). 5 GB recommended.
DECLARE @currentSize INT                      -- Current file size
DECLARE @freeSize INT                         -- Unallocated space
DECLARE @usedSize INT                         -- Used space

DECLARE @sql NVARCHAR(500)
DECLARE @waitTime INT = 10                    -- Wait between iterations (seconds)

-- Get the data file name
SELECT @fileName = name
FROM sys.master_files
WHERE database_id = DB_ID(@dbName)
AND type_desc = 'ROWS'

-- Shrink in a loop
WHILE 1 = 1
BEGIN
    -- Get current size and space breakdown
    DECLARE @sql0 NVARCHAR(MAX) = N'
    USE [' + @dbName + '];
    SELECT
      @currentSize = (SUM(total_page_count) * 1.0 / 128),
      @freesize = (SUM(unallocated_extent_page_count) * 1.0 / 128)
    FROM sys.dm_db_file_space_usage
    WHERE database_id = DB_ID();'

    EXEC sp_executesql
      @sql0,
      N'@currentSize INT OUTPUT, @freesize INT OUTPUT',
      @currentSize OUTPUT,
      @freesize OUTPUT

    PRINT 'Current size:' + CAST(@currentSize AS VARCHAR(10)) + 'MB'
    PRINT 'Free size:' + CAST(@freeSize AS VARCHAR(10)) + 'MB'

    SET @usedSize = @currentSize - @freeSize
    PRINT 'Used size:' + CAST(@usedSize AS VARCHAR(10)) + 'MB'

    -- Validate target size
    IF @targetSize <= @usedSize
    BEGIN
        PRINT 'The target size is too small. Specify a new size. The target size cannot be smaller than: ' + CAST(@usedSize AS VARCHAR(20)) + 'MB'
        BREAK
    END

    -- Exit when target is reached
    IF @currentSize <= @targetSize
    BEGIN
        PRINT 'Shrink completed. Current size: ' + CAST(@currentSize AS VARCHAR(20)) + 'MB'
        BREAK
    END

    -- Calculate the new size for this iteration
    DECLARE @newSize INT = @currentSize - @shrinkSize
    IF @newSize < @targetSize
        SET @newSize = @targetSize

    -- Run the shrink
    SET @sql = 'USE [' + @dbName + '];DBCC SHRINKFILE (N''' + @fileName + ''', ' + CAST(@newSize AS VARCHAR(20)) + ')'
    PRINT 'Executing shrink: ' + @sql
    EXEC (@sql)

    -- Wait before the next iteration
    PRINT 'Waiting ' + CAST(@waitTime AS VARCHAR(10)) + ' seconds to continue...'
    WAITFOR DELAY '00:00:10'
END

DBCC SHRINKFILE(<File ID>, <Target size in MB>)

Résoudre les problèmes courants d'espace de données

SELECT name, log_reuse_wait, log_reuse_wait_desc FROM sys.databases;

Scénario 1 : tempdb est actuellement volumineux (instance active)

Étape 1 — Vérifiez l'espace par type d'objet

Exécutez la requête suivante pour voir combien d'espace chaque catégorie (objets utilisateur, objets internes, magasin de versions) consomme :

SELECT
    SUM(version_store_reserved_page_count) AS [version store pages used],
    (SUM(version_store_reserved_page_count) * 1.0 / 128) AS [version store space (MB)],
    SUM(user_object_reserved_page_count) AS [user object pages used],
    (SUM(user_object_reserved_page_count) * 1.0 / 128) AS [user object space (MB)],
    SUM(internal_object_reserved_page_count) AS [internal object pages used],
    (SUM(internal_object_reserved_page_count) * 1.0 / 128) AS [internal object space (MB)]
FROM
    sys.dm_db_file_space_usage;

Pour référence, consultez sys.dm_db_file_space_usage.

Étape 2a — Si les objets utilisateur ou les objets internes sont volumineux

Trouvez quelles sessions utilisent le plus d'espace tempdb :

SELECT
    session_id,
    SUM(user_objects_alloc_page_count) AS [user object pages used],
    (SUM(user_objects_alloc_page_count) * 1.0 / 128) AS [user object space (MB)],
    SUM(internal_objects_alloc_page_count) AS [internal object pages used],
    (SUM(internal_objects_alloc_page_count) * 1.0 / 128) AS [internal object space (MB)]
FROM
    sys.dm_db_session_space_usage
GROUP BY
    session_id;

Pour référence, consultez sys.dm_db_session_space_usage.

Ensuite, utilisez le session_id pour trouver l'instruction SQL que la session exécute :

SELECT
    r.session_id AS [SPID],
    r.start_time AS [Start Time],
    r.status AS [Status],
    r.command AS [Command Type],
    r.wait_type AS [Wait Type],
    r.wait_time AS [Wait Time (ms)],
    r.last_wait_type AS [Last Wait Type],
    t.text AS [Executing Statement]
FROM
    sys.dm_exec_requests r
CROSS APPLY
    sys.dm_exec_sql_text(r.sql_handle) t
WHERE
    r.session_id = <session_id>; -- Replace with the session_id from the previous query

Étape 2b — Si le magasin de versions est volumineux

La base de données a probablement l'isolation instantanée activée avec des transactions de longue durée empêchant le nettoyage.

Vérifiez quelles bases de données ont l'isolation instantanée activée :

SELECT
    name,
    is_read_committed_snapshot_on,
    snapshot_isolation_state
FROM sys.databases;

Si is_read_committed_snapshot_on ou snapshot_isolation_state est 1, l'isolation instantanée est active.

Trouvez les sessions avec des transactions non validées de longue durée qui bloquent le nettoyage du magasin de versions :

SELECT * FROM sys.dm_tran_active_snapshot_database_transactions ORDER BY elapsed_time_seconds DESC;

Utilisez le session_id pour vérifier ce que fait la session :

SELECT * FROM sys.sysprocesses WHERE spid = <session_id>;

Si l'état de la session est sleeping, vérifiez la dernière instruction exécutée :

DBCC INPUTBUFFER(<session_id>);

Scénario 2 : Investigation de la croissance passée de tempdb après un redémarrage

Si l'instance a déjà été redémarrée et que les données en direct ne sont pas disponibles, utilisez les Sessions Actives Moyennes (AAS), les journaux de requêtes lentes et les journaux d'audit pour reconstituer ce qui s'est passé.

Étape 1 — Trouvez la fenêtre de croissance

Sur la page des détails de l'instance, accédez à Monitoring and Alerts > Standard Monitoring. Dans la section Instance Storage, notez quand la métrique tmp_size a commencé et arrêté d'augmenter. Gardez à l'esprit que tempdb peut réutiliser d'abord l'espace libre existant — l'expansion réelle du fichier se produit uniquement après l'épuisement de l'espace existant, donc la croissance dans le graphique peut être en retard par rapport au début de l'opération SQL.

Étape 2 — Analysez à l'aide d'AAS

Accédez à Autonomy Services > Performance Optimization > Performance Insight. Sélectionnez la fenêtre de temps, en étendant l'heure de début plus tôt pour capturer toutes les opérations connexes. Recherchez les instructions SQL qui utilisent fortement les tables temporaires basées sur le disque (par exemple, création fréquente de tables #).

Étape 3 — Filtrez les journaux de requêtes lentes par mot-clé

Accédez à Autonomy Services > Slow Query Logs. Recherchez par mot-clé (par exemple, un nom de table temporaire). Vérifiez les heures de début et les durées des requêtes pour voir si l'heure de fin de la requête correspond au moment où tempdb a cessé de croître.

USE tempdb;
DBCC SHRINKFILE(templog, 1);
GO