MSSQL Spazio Disco

elenca datafiles e spazio liberabile

-- Soluzione compatibile SQL Server 2008 senza tabella temporanea fissa
-- Utilizziamo una variabile di tipo tabella in memoria

DECLARE @Results TABLE (
    DatabaseName SYSNAME,
    LogicalFileName SYSNAME,
    FileType NVARCHAR(60),
    TotalSizeMB DECIMAL(18,2),
    UsedSpaceMB DECIMAL(18,2),
    FreeSpaceMB DECIMAL(18,2),
    PhysicalPath NVARCHAR(520)
);

-- Iteriamo su tutti i database online
DECLARE @DbName SYSNAME;
DECLARE @SQL NVARCHAR(MAX);

DECLARE db_cursor CURSOR LOCAL FAST_FORWARD FOR 
    SELECT name FROM sys.databases WHERE state_desc = 'ONLINE';

OPEN db_cursor;
FETCH NEXT FROM db_cursor INTO @DbName;

WHILE @@FETCH_STATUS = 0
BEGIN
    SET @SQL = N'
    USE ' + QUOTENAME(@DbName) + N';
    SELECT 
        DB_NAME() AS DatabaseName,
        f.name AS LogicalFileName,
        f.type_desc AS FileType,
        CAST((f.size * 8.0) / 1024 AS DECIMAL(18,2)) AS TotalSizeMB,
        CAST((CAST(FILEPROPERTY(f.name, ''SpaceUsed'') AS BIGINT) * 8.0) / 1024 AS DECIMAL(18,2)) AS UsedSpaceMB,
        CAST(((f.size - CAST(FILEPROPERTY(f.name, ''SpaceUsed'') AS BIGINT)) * 8.0) / 1024 AS DECIMAL(18,2)) AS FreeSpaceMB,
        f.physical_name AS PhysicalPath
    FROM sys.database_files AS f;';

    INSERT INTO @Results
    EXEC sp_executesql @SQL;

    FETCH NEXT FROM db_cursor INTO @DbName;
END

CLOSE db_cursor;
DEALLOCATE db_cursor;

-- Mostra il risultato finale completo
SELECT * FROM @Results ORDER BY FreeSpaceMB DESC;

esegue shrink su tutti i datafiles

SET NOCOUNT ON;

DECLARE @DbName NVARCHAR(255);
DECLARE @LogFileName NVARCHAR(255);
DECLARE @SQL NVARCHAR(MAX);

DECLARE db_cursor CURSOR FOR 
SELECT d.name, f.name 
FROM sys.databases d
JOIN sys.master_files f ON d.database_id = f.database_id
WHERE f.type = 1 
  AND d.name NOT IN ('master', 'model', 'msdb', 'tempdb')
  AND d.state_desc = 'ONLINE'; -- Lavoriamo solo su DB online

OPEN db_cursor;
FETCH NEXT FROM db_cursor INTO @DbName, @LogFileName;

WHILE @@FETCH_STATUS = 0
BEGIN
    PRINT 'Tentativo di shrink per il database: ' + @DbName;

    -- Eseguiamo il checkpoint per forzare il flush delle pagine
    SET @SQL = 'USE [' + @DbName + ']; CHECKPOINT;';
    EXEC sp_executesql @SQL;

    -- Eseguiamo lo shrink del file di log
    -- Non forziamo 1MB, lasciamo che SQL decida quanto è effettivamente restringibile
    SET @SQL = 'USE [' + @DbName + ']; DBCC SHRINKFILE (''' + @LogFileName + ''');';
    EXEC sp_executesql @SQL;

    FETCH NEXT FROM db_cursor INTO @DbName, @LogFileName;
END

CLOSE db_cursor;
DEALLOCATE db_cursor;
GO