Contrôler la taille des bases, des fichiers et du journal de transactions
Requêtes T-SQL pour la taille de chaque base, de ses fichiers de données et de journal, l'espace libre par fichier, l'espace du journal et les dernières sauvegardes.
Ces requêtes T-SQL donnent la taille des bases et de leurs fichiers, l'espace libre dans chaque fichier, l'espace utilisé par le journal de transactions, ainsi que les dernières sauvegardes complètes et de journal. La première requête travaille sur la base courante ; les autres couvrent toutes les bases de l'instance.
Taille de la base courante
SELECT
database_name = DB_NAME(database_id)
, log_size_mb = CAST(SUM(CASE WHEN type_desc = 'LOG' THEN size END) * 8. / 1024 AS DECIMAL(18,2))
, row_size_mb = CAST(SUM(CASE WHEN type_desc = 'ROWS' THEN size END) * 8. / 1024 AS DECIMAL(18,2))
, total_size_mb = CAST(SUM(size) * 8. / 1024 AS DECIMAL(18,2))
FROM sys.master_files WITH(NOWAIT)
WHERE database_id = DB_ID() -- for current db
GROUP BY database_id
Taille et espace libre de chaque fichier
La procédure sp_msforeachdb n'est pas documentée. Le bloc exécute une requête dans chaque base et stocke le résultat dans une table temporaire.
CREATE TABLE #FileSize
(dbName NVARCHAR(128),
FileName NVARCHAR(128),
type_desc NVARCHAR(128),
CurrentSizeMB DECIMAL(10,2),
FreeSpaceMB DECIMAL(10,2)
);
INSERT INTO #FileSize(dbName, FileName, type_desc, CurrentSizeMB, FreeSpaceMB)
exec sp_msforeachdb
'use [?];
SELECT DB_NAME() AS DbName,
name AS FileName,
type_desc,
size/128.0 AS CurrentSizeMB,
size/128.0 - CAST(FILEPROPERTY(name, ''SpaceUsed'') AS INT)/128.0 AS FreeSpaceMB
FROM sys.database_files
WHERE type IN (0,1);';
SELECT *
FROM #FileSize
WHERE dbName NOT IN ('distribution', 'master', 'model', 'msdb')
--AND FreeSpaceMB > ?;
DROP TABLE #FileSize;
Taille des données et du journal par base
WITH fs
AS
(
SELECT database_id, TYPE, SIZE * 8.0 / 1024 SIZE
FROM sys.master_files
)
SELECT
name,
(SELECT SUM(SIZE) FROM fs WHERE TYPE = 0 AND fs.database_id = db.database_id) DataFileSizeMB,
(SELECT SUM(SIZE) FROM fs WHERE TYPE = 1 AND fs.database_id = db.database_id) LogFileSizeMB
FROM sys.databases db
Espace utilisé dans le journal et dans la base courante
sp_spaceused
DBCC SQLPERF(LOGSPACE);
sp_spaceused affiche l'espace utilisé par la base courante. DBCC SQLPERF(LOGSPACE) affiche, pour chaque base, la taille du journal de transactions et le pourcentage utilisé.
Taille, espace utilisé et dernières sauvegardes par base
Cette requête combine les tailles des fichiers, l'espace utilisé dans chaque base en ligne et les dernières sauvegardes complètes et de journal enregistrées dans msdb.
IF OBJECT_ID('tempdb.dbo.#space') IS NOT NULL
DROP TABLE #space
CREATE TABLE #space (
database_id INT PRIMARY KEY
, data_used_size DECIMAL(18,2)
, log_used_size DECIMAL(18,2)
)
DECLARE @SQL NVARCHAR(MAX)
SELECT @SQL = STUFF((
SELECT '
USE [' + d.name + ']
INSERT INTO #space (database_id, data_used_size, log_used_size)
SELECT
DB_ID()
, SUM(CASE WHEN [type] = 0 THEN space_used END)
, SUM(CASE WHEN [type] = 1 THEN space_used END)
FROM (
SELECT s.[type], space_used = SUM(FILEPROPERTY(s.name, ''SpaceUsed'') * 8. / 1024)
FROM sys.database_files s
GROUP BY s.[type]
) t;'
FROM sys.databases d
WHERE d.[state] = 0
FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 2, '')
EXEC sys.sp_executesql @SQL
SELECT
d.database_id
, d.name
, d.state_desc
, d.recovery_model_desc
, t.total_size
, t.data_size
, s.data_used_size
, t.log_size
, s.log_used_size
, bu.full_last_date
, bu.full_size
, bu.log_last_date
, bu.log_size
FROM (
SELECT
database_id
, log_size = CAST(SUM(CASE WHEN [type] = 1 THEN size END) * 8. / 1024 AS DECIMAL(18,2))
, data_size = CAST(SUM(CASE WHEN [type] = 0 THEN size END) * 8. / 1024 AS DECIMAL(18,2))
, total_size = CAST(SUM(size) * 8. / 1024 AS DECIMAL(18,2))
FROM sys.master_files
GROUP BY database_id
) t
JOIN sys.databases d ON d.database_id = t.database_id
LEFT JOIN #space s ON d.database_id = s.database_id
LEFT JOIN (
SELECT
database_name
, full_last_date = MAX(CASE WHEN [type] = 'D' THEN backup_finish_date END)
, full_size = MAX(CASE WHEN [type] = 'D' THEN backup_size END)
, log_last_date = MAX(CASE WHEN [type] = 'L' THEN backup_finish_date END)
, log_size = MAX(CASE WHEN [type] = 'L' THEN backup_size END)
FROM (
SELECT
s.database_name
, s.[type]
, s.backup_finish_date
, backup_size =
CAST(CASE WHEN s.backup_size = s.compressed_backup_size
THEN s.backup_size
ELSE s.compressed_backup_size
END / 1048576.0 AS DECIMAL(18,2))
, RowNum = ROW_NUMBER() OVER (PARTITION BY s.database_name, s.[type] ORDER BY s.backup_finish_date DESC)
FROM msdb.dbo.backupset s
WHERE s.[type] IN ('D', 'L')
) f
WHERE f.RowNum = 1
GROUP BY f.database_name
) bu ON d.name = bu.database_name
ORDER BY t.total_size DESC
Sauvegardes listées par sys.dm_database_backups
SELECT
backup_file_id,
backup_start_date,
backup_finish_date,
CASE backup_type
WHEN 'D' THEN 'Full'
WHEN 'I' THEN 'Differential'
WHEN 'L' THEN 'Transaction log'
END AS backup_type,
in_retention,*
FROM sys.dm_database_backups
--where backup_type != 'L'
--ORDER BY backup_finish_date DESC;
Remarques
- La requête basée sur
sp_msforeachdbrepose sur une procédure non documentée. - Vérifier que
sys.dm_database_backupsest disponible sur la plateforme avant de s'en servir. - Les requêtes de taille s'inspirent d'une question publique Stack Overflow sur la taille des bases.