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

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

Segui Informarea su Telegram

 
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


Segui Informarea su Telegram

Fabrizio Cannatelli

Fondatore di Informarea.it, è un appassionato di informatica. Ha lavorato per molti anni come Analista Programmatore presso varie aziende utilizzando diversi linguaggi di sviluppo, oggi svolge un lavoro completamente diverso ma la voglia di comunicare e di condividere con il web i suoi studi e le sue curiosità lo hanno spinto a far nascere questo blog non solo per esprimere e mostrare la passione per questo mondo, ma anche per confrontarsi con nuove esperienze di sviluppo.

Fabrizio Cannatelli

Lascia un commento

Il tuo indirizzo email non sarà pubblicato. I campi obbligatori sono contrassegnati *

Questo sito usa Akismet per ridurre lo spam. Scopri come i tuoi dati vengono elaborati.