/*
Santomieri Systems
Josh Santomieri
http://www.santsys.com

Truncate and shrink the log files of all databases on a SQL server instance.
*/

SET NOCOUNT ON

DECLARE @DBName VARCHAR(100)
DECLARE @FileName VARCHAR(255)

USE master

CREATE TABLE #clearlogs
(
	dbname VARCHAR(100),
	fn VARCHAR(255)
)

INSERT INTO #clearlogs (dbname)
SELECT db.name FROM master.dbo.sysdatabases AS db

DECLARE MyLogs CURSOR FOR 
(SELECT dbname FROM #clearlogs)
OPEN MyLogs
FETCH NEXT FROM MyLogs INTO @DBName
WHILE (@@FETCH_STATUS = 0)
BEGIN
	EXEC('UPDATE #clearlogs SET fn = (SELECT f.name FROM ' + @DBName + '.dbo.sysfiles AS f WHERE filename LIKE ''%.ldf%'') WHERE dbname = ''' + @DBName +'''')
	FETCH NEXT FROM MyLogs INTO @DBName

END

CLOSE MyLogs
DEALLOCATE MyLogs

DECLARE MyDatabases CURSOR FOR
(SELECT dbname, fn FROM #clearlogs)

OPEN MyDatabases
FETCH NEXT FROM MyDatabases INTO @DBName, @FileName
WHILE (@@FETCH_STATUS = 0)
BEGIN
	EXEC('BACKUP LOG ' + @DBName + ' WITH TRUNCATE_ONLY')
	EXEC('USE ' + @DBName + ' DBCC SHRINKFILE(' + @FileName + ', 5)')

	FETCH NEXT FROM MyDatabases INTO @DBName, @FileName
END

CLOSE MyDatabases
DEALLOCATE MyDatabases
DROP TABLE #clearlogs

SET NOCOUNT OFF