Script for shrinking DataBase Logs SharePoint 2010

The below script when run on the DataBase Server creates a temporary table, gets all the logs of all the sites of the Web Application in that table, shrinks all the logs & then deletes the temporary table.

CREATE TABLE #TransactionLogFiles (DatabaseName VARCHAR(150), LogFileName VARCHAR(150) )

-- step 1. get hold of the entire database names from the database server

DECLARE DataBaseList CURSOR FOR

SELECT name FROM SYS.sysdatabases

WHERE NAME NOT IN ('master','tempdb','model','msdb','distribution')

DECLARE @DataBase VARCHAR(128)

DECLARE @SqlScript VARCHAR(MAX)

-- step 2. insert all the database name and corresponding log files' names into the temp table

OPEN DataBaseList FETCH

NEXT FROM DataBaseList INTO @DataBase

WHILE @@FETCH_STATUS <> -1

BEGIN

SET @SqlScript = 'USE [' + @DataBase + '] INSERT INTO #TransactionLogFiles(DatabaseName, LogFileName) SELECT '''

+ @DataBase + ''', Name FROM sysfiles WHERE FileID=2'

--SELECT @SqlScript

EXEC(@SqlScript)

FETCH NEXT FROM DataBaseList INTO @DataBase END

DEALLOCATE DataBaseList

-- step 3. go through the each row and execute the shrinkfile script against each database log file on the server

DECLARE TransactionLogList CURSOR FOR

SELECT DatabaseName, LogFileName FROM #TransactionLogFiles

DECLARE @LogFile VARCHAR(128)

OPEN TransactionLogList FETCH

NEXT FROM TransactionLogList INTO @DataBase, @LogFile

WHILE @@FETCH_STATUS <> -1

BEGIN

SELECT @SqlScript = 'USE [' + @DataBase + '] '

+ 'ALTER DATABASE [' + @DataBase + '] SET RECOVERY SIMPLE WITH NO_WAIT '

+ 'DBCC SHRINKFILE(N''' + @LogFile + ''', 1) '

+ 'ALTER DATABASE [' + @DataBase + '] SET RECOVERY FULL WITH NO_WAIT'

EXEC(@SqlScript)

FETCH NEXT FROM TransactionLogList INTO @DataBase, @LogFile END

DEALLOCATE TransactionLogList

--SELECT * FROM #TransactionLogFiles

-- step 4. clean up

DROP TABLE #TransactionLogFiles