Transaction Logs not Truncating

Question

Transaction Logs not Truncating

Eric Lahti

SSC Enthusiast

Points: 143
More actions
July 11, 2005 at 10:16 am

#110327

Hiya,
I don't know if anyone has encountered this, but I just inherited a whack of old databases with with all kinds of fun jobs. One of our common jobs is to log ship and the log shipping is accomplished through a custom script with a standard backup log statement. Now, as I recall, this should truncate the log, yet none of our logs are getting truncated. Any ideas where else I should look?

Thanks,
Eric Lahti

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

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

David A. Long SSCrazy Eights Points: 8421 More actions · Answer 1

Check to see if there are open transactions using EM, Kill any long running processes that might be holding your log hostage. You can also use DBCC OPENTRAN('mydatabase') to check your log file for an open transaction.

Since you state that you are doing log shipping, you may find issues if you force the log truncation!

For the worst case, for a non-replicated or otherwise independant database, sometimes it is a matter of:

ALTER DATABASE mydatabase SET RECOVERY SIMPLE

GO

BACKUP LOG 'mydatabase' WITH TRUNCATE_ONLY

GO

DBCC SHRINKFILE (N'mydatabase_Log')

GO

ALTER DATABASE mydatabase SET RECOVERY FULL

GO

BACKUP DATABASE mydatabase TO DISK = N'C:\mydatabase.bak' WITH INIT, NOUNLOAD, NAME = N'Full backup', SKIP, STATS = 10, FORMAT, MEDIANAME = N'Full Backup'

GO

In your case you would add the steps to re-establish log shipping. So I would only do this if you have no other options. Even then look up these statements in BOL before you perform them to make sure you understand how your "system" will be affected.

Andy

Antonio Caldeira Newbie Points: 7 More actions · Answer 2

Hi, You Can use this Stored Procedure for replicated Database or otherwise Database. In your case is necessary Add Schedule Job to execute.

use MyDataBase

go

dbcc shrinkfile ('MyDataBase_log',emptyfile)

go

CREATE PROCEDURE TRANSACTION_LOG_SHRINK

AS

-- ISSUE COMMAND DBCC SHRINKFILE (Database_Log, TRUNCATEONLY) before this runs!

-- create table virtual_log_file_wrapper (char1 char(4000))

dbcc loginfo ('MyDataBase')

DECLARE

@i INT,

@j-2 INT

SET @i = 0

SET @j-2 = 0

WHILE (@j < 100)

BEGIN

SET @i = 0

WHILE (@i < 100)

BEGIN

INSERT INTO virtual_log_file_wrapper(char1)

VALUES ( 'a' )

SET @i = @i + 1

END

TRUNCATE TABLE virtual_log_file_wrapper

BACKUP LOG MyDataBase WITH TRUNCATE_ONLY

SET @j-2 = @j-2 + 1

END

dbcc loginfo ('MyDataBase')

Antonio Caldeira

Antonio Caldeira
e-mail: antoniocaldeira@msn.com
celular phone: 55-11-9568-5346
home phone: 55-11-6991-3562

Eric Lahti SSC Enthusiast Points: 143 More actions · Answer 3

Hiya,

Thank you all very much. I'll give your ideas a shot and see what happens.

Thanks,

Eric Lahti