July 11, 2005 at 10:16 am
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
July 11, 2005 at 11:06 pm
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
July 12, 2005 at 6:40 am
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
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
July 12, 2005 at 9:28 am
Hiya,
Thank you all very much. I'll give your ideas a shot and see what happens.
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