How to shrink the log file on all databases

Who I am
Martí Micolau
@martimicolau
Author and references
 Here is a way to do it shrink del transaction log (file .ldf) which often grows disproportionately in Sql Server.

 To avoid repeating the statement on every database on the server, the underlying script works on the all databases on the 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: is an undocumented microsoft stored procedure that allows you to execute code T-SQL on all databases on a server. ?: is the name of the database returned by the stored procedure. file_name(2): is a function that returns the logical name of the database log file. Another solution is this other script, which shrinks the log on the db through a 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 



add a comment of How to shrink the log file on all databases
Comment sent successfully! We will review it in the next few hours.