declare @DataBaseName nvarchar(512)
declare @LogicalFileName nvarchar(512)
declare @cmd nvarchar(512)
DECLARE @Cursor CURSOR
SET @Cursor = CURSOR FOR
SELECT
ds.name DataBaseName, mf.name LogicalFileName
FROM
sys.master_files mf
LEFT JOIN
sys.databases ds
ON
mf.database_id = ds.database_id
where mf.type_desc = ‘LOG’ — ‘ROWS’
AND not ds.name in (‘master’,’tempdb’,’model’,’msdb’)
OPEN @Cursor
FETCH NEXT FROM @Cursor INTO @DataBaseName, @LogicalFileName
WHILE (@@FETCH_STATUS = 0)
BEGIN
set @cmd =
‘USE [‘+@DataBaseName+’]
DBCC SHRINKFILE (N”’ + @LogicalFileName + ”’, 0, TRUNCATEONLY)
‘
–print @cmd
exec (@cmd)
FETCH NEXT FROM @Cursor INTO @DataBaseName, @LogicalFileName
end
