Truncating the Transaction Log

Question

Post reply

Truncating the Transaction Log

Maverick100877

SSCertifiable

Points: 5240
More actions
November 8, 2005 at 12:41 pm

#115487

Hi All
I need a procedure which will truncate the transaction log for all the databases on the server and then run the dbCC shrinkfile.Can Anybody help me on this...

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Maverick100877 SSCertifiable Points: 5240 More actions · Answer 1

I had got this sql from the site only but it doesnt works

use

master

DECLARE

@Statement varchar (2000)

SELECT

@Statement = ''

SELECT

@Statement = @Statement + 'USE ?; '

SELECT

@Statement = @Statement + 'SELECT ''?''; '

SELECT

@Statement = @Statement + 'DECLARE @Log_Logical_FileName varchar (30); '

SELECT

@Statement = @Statement + 'SELECT @Log_Logical_FileName = rtrim(name) FROM dbo.sysfiles WHERE (status & 0x40) <> 0; '

SELECT

@Statement = @Statement + 'dbcc shrinkfile (@Log_Logical_FileName, 30,truncateonly); '

SELECT

@Statement = @Statement + 'SELECT fileid, name, filename, size, growth, status, maxsize FROM dbo.sysfiles WHERE (status & 0x40) <> 0; '

SELECT

@Statement

EXEC

sp_MSforeachdb @command1=@Statement

GO

Graeme100 SSCrazy Eights Points: 8054 More actions · Answer 2

Hi,

I use this

DECLARE @DBName VarChar (255), @Cmd VarChar (500)

DECLARE DBCursor CURSOR

FOR

SELECT [name] FROM sysdatabases WHERE [name] IN ('List','Your','databases')

OPEN DBCursor

FETCH NEXT FROM DBCursor INTO @DBName

WHILE @@FETCH_STATUS = 0

BEGIN

SET @Cmd = 'BACKUP LOG ' + @DBName + ' WITH TRUNCATE_ONLY'

EXEC (@Cmd)

PRINT @DBName + ' Log truncated.'

SET @Cmd = 'DBCC SHRINKDATABASE (' + @DBName + ')'

EXEC (@Cmd)

FETCH NEXT FROM DBCursor INTO @DBName

END

CLOSE DBCursor

DEALLOCATE DBCursor

HTH....Graeme

SQLPhil SSCarpal Tunnel Points: 4204 More actions · Answer 3

Don't forget to actually back up the log before you truncate it!