SQL Tips

SQL Log Truncation Script

1) Individual content Databases.

In order to truncate sql log for individual content databases use the below script:

USE WSS_Content_Sites_XYZ
    GO
    ALTER DATABASE WSS_Content_Sites_XYZ SET RECOVERY SIMPLE
    GO
    backup log WSS_Content_Sites_XYZ with truncate_only
    GO
    DBCC SHRINKFILE (WSS_Content_Sites_XYZ_log,1)
    GO
    ALTER DATABASE WSS_Content_Sites_XYZ SET RECOVERY FULL
    GO

2) All SQL log databases from SQL Server Instance

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