SELECT
‘USE [‘ + d.name + N’]’ + CHAR(13) + CHAR(10)
+ ‘DBCC SHRINKFILE ( ‘+ s.name + ‘ , 1)’
+ CHAR(13) + CHAR(10)
+ ‘BACKUP LOG ‘ + d.name + ‘  TO DISK = ”nul”’
+ CHAR(13) + CHAR(10)
+ ‘DBCC SHRINKFILE ( ‘+ s.name + ‘ , 1)’
+ CHAR(13) + CHAR(10)
+ ‘GO’
FROM sys.databases D,master..sysaltfiles S
WHERE S.dbid=d.database_id
AND d.database_id > 4
AND S.filename LIKE ‘%.ldf’
ORDER BY S.size DESC

 

Version 2:

 

ALTER DATABASE [TootleCFD]
SET RECOVERY SIMPLE;
GO
— Shrink the truncated log file to 1 MB.
DBCC SHRINKFILE (TootleCFD_Log, 1);
GO
— Reset the database recovery model.
ALTER DATABASE [TootleCFD]
SET RECOVERY FULL;
GO 

 

Advertisements