MSSQL Spazio Disco - Cronologia

WikiSysop il 13:28, 22 lug 2026

2026-07-22T13:28:31Z

← Versione meno recente		Versione delle 13:28, 22 lug 2026
Riga 1:		Riga 1:
			==elenca datafiles e spazio liberabile==
			<pre>
			-- 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;
			</pre>


			esegue shrink su tutti i datafiles

	<pre>		<pre>
	SET NOCOUNT ON;		SET NOCOUNT ON;

WikiSysop il 09:18, 13 lug 2026

2026-07-13T09:18:59Z

← Versione meno recente		Versione delle 09:18, 13 lug 2026
Riga 41:		Riga 41:

	[[Categoria:Database]]		[[Categoria:Database]]
			[[Categoria:MS SQL Server]]

WikiSysop: Creata pagina con "

 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_S..."

2026-07-13T09:18:22Z

Creata pagina con "<pre> 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_S..."

Nuova pagina

<pre>
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

</pre>

[[Categoria:Database]]