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
