Microsoft SQL Server 2005/2008:Shrink Database transaction Logs script

1.Take Database backup.

2.Backup Transaction Logs.

Use this link,Click Here



3.Use this script to shrink transaction log.

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE procedure Trunc_Transaction_log

as

begin

--Check Transaction log Usage
DBCC SQLPERF(logspace)




-- step 1. get hold of the entire database names from the database server
CREATE TABLE #TransactionLogFiles (DatabaseName VARCHAR(150), LogFileName VARCHAR(150) )

DECLARE DataBaseList CURSOR FOR
SELECT name FROM SYS.sysdatabases

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


-- step 2. insert all the database name and corresponding log files' names into the temp table
DECLARE @DataBase VARCHAR(128)
DECLARE @SqlScript VARCHAR(MAX)

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'

print 'Shrinking...'+@LogFile
print '       '

EXEC(@SqlScript)
print '       '
print 'shrank...'+@LogFile
print '       '

FETCH NEXT FROM TransactionLogList INTO @DataBase, @LogFile END
DEALLOCATE TransactionLogList
--SELECT * FROM #TransactionLogFiles

-- step 4. clean up
DROP TABLE #TransactionLogFiles

--Check Transaction log Usage after shrink
DBCC SQLPERF(logspace)


end

Execute this ..

Exec Trunc_Transaction_log

Check Messages...

Shrinking...1_log
      
Cannot shrink log file 2 (1_log) because total number of logical log files cannot be fewer than 2.

(1 row(s) affected)

DBCC execution completed. If DBCC printed error messages, contact your system administrator.
      
shrank...MediaSurvey_log
      
Shrinking...TRO_Log
      

(1 row(s) affected)
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
      
shrank...TRO_Log
      
Shrinking...NEWRM_log
      

(1 row(s) affected)
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
      
shrank...NEWRM_log
      
Shrinking...2_log
      

(1 row(s) affected)

DBCC execution completed. If DBCC printed error messages, contact your system administrator.
      
shrank..2_log
      
Shrinking...3_log
      

(1 row(s) affected)

DBCC execution completed. If DBCC printed error messages, contact your system administrator.
      
shrank...3_log

About Me