SQL Server 2005 – Shrink log file of all databases

One of database server was running out of disk space. The culprit was the very large SQL server log files.
Using following script i was able to shrink log files of all databses in a SQL server instance so creating lots of disk space which was earlier used by those SQL server log files.

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 + ', 1)')
    PRINT('USE ' + @DBName + ' DBCC SHRINKFILE(' + @FileName + ', 1)')
    FETCH NEXT FROM MyDatabases INTO @DBName, @FileName
END
CLOSE MyDatabases
DEALLOCATE MyDatabases
SELECT * FROM #clearlogs
DROP TABLE #clearlogs
SET NOCOUNT OFF
This entry was posted in Knowledge Sharing, SQL Server, Troubleshooting. Bookmark the permalink.

Leave a Reply