Come eseguire lo shrink del file di log su tutti i database

 
Ecco un sistema per effettuare lo shrink del transaction log (file .ldf) che spesso cresce spropositatamente in Sql Server.

 
Per evitare di ripetere l’istruzione su ogni database presente sul server , lo script sottostante lavora contemporaneamente su tutti i database presenti sul server.
 
EXECUTE sp_msforeachdb
'USE ?;
DUMP TRANSACTION ? WITH NO_LOG;
DECLARE @LogLogicalName nvarchar(100);
SELECT @LogLogicalName = file_name(2);
DBCC SHRINKFILE(@LogLogicalName, 100);'

 
sp_msforeachdb: è una stored procedure microsoft non documentata che permette di eseguire codice T-SQL su tutti i database presenti su un server.
 
?: è il nome del database restituito dalla stored procedure.
 
file_name(2): è una funzione che restituisce il nome logico del file di log del database.
 
 
Altra soluzione è quest’ altro script, che esegue lo shrink del log sui db attraverso un loop:
 
CREATE TABLE #TDatabases(
DBName nvarchar(128),
DBLogicalName nvarchar(128)
)
INSERT INTO #TDatabases
SELECT db.name DBName, mf.name DBLogicalName
FROM sys.databases db join sys.master_files mf
on db.database_id = mf.database_id
WHERE db.name not in ('master', 'tempdb', 'model', 'msdb',
'distribution') AND type_desc LIKE 'log'

SET NOCOUNT ON
DECLARE @VarDBLogicalName nvarchar(128)
DECLARE @VarDBName nvarchar(128)
DECLARE @VarRowCount int

SELECT top 1 @VarDBName = DBName, @VarDBLogicalName = DBLogicalName
FROM #TDatabases
SET @VarRowCount = @@rowcount
WHILE @VarRowCount <> 0
BEGIN
PRINT @VarDBLogicalName
EXEC(' use ' + @VarDBName + ' backup log '+ @VarDBName + ' with no_log
dbcc shrinkfile(''' + @VarDBLogicalName + ''', TRUNCATEONLY) WITH
NO_INFOMSGS')
DELETE
FROM #TDatabases
WHERE DBName = @VarDBName
SELECT top 1 @VarDBName = DBName, @VarDBLogicalName =
DBLogicalName
FROM #TDatabases
SET @VarRowCount = @@ROWCOUNT
END
DROP TABLE #TDatabases
SET NOCOUNT OFF
 

Ciao Fab


Fabrizio Cannatelli

Autore e Founder di Informarea, sono un appassionato di informatica e tecnologia da sempre. La voglia di comunicare e di condividere sul Web le mie curiosità e le mie conoscenze, mi ha spinto a lanciarmi nel progetto di questo sito. Nato un po' per gioco e un po' per passione, oggi è diventato una grande realtà.

Fabrizio Cannatelli

Approfondimenti