transaction log!!! urgent help!!! 1

[jabroni]

hi there,

i'm a newbie to this.
my transaction log is running out of control!!!
i've set transaction log backups mon-fri running every hour during working hours. with the option "remove inactive entries from transaction log" ticked (is this the same as "truncating the log"???)
i'm running sql 2000. i've set my database options to "full recovery", "auto shrink" and "automatically grow file".
before i leave work, the .LDF file is around 300mb. but the next morning it's ballooned to over 3gb!! i manually shrink the file using "DBCC SHRINKDATBASE" but the next day the same thing happens!!
i also have snapshot replication running as well.
how can i control the transaction log?? this is very frustrating!!

need urgent help
cheers

 

[lemosley01]

Jabroni,
what is going on overnight that is causing the transaction log to grow so much? That's the first thing you need to discover. Perhaps there are bulk-inserts going on overnight in which case you might want to consider using the BULK-LOGGED recover model (or at least switching the DB to this model overnight, then switching it to full the next morning).

See the section on recovery models in SQL Books Online for an overview of these models. It may be that you don't need the full recovery model.

 

[MikeTom]

You mention you have snapshot replication running. is the datbase with the problem a subscriber. if so that is where all the bulk inserts are happening when the replication task is running. in wich case i dont think you need the full recovery option as you donot really need the transaction logs. because the database is a snapshot of another.

 

[MikeTom]

I forgot to mention. The autoshrink option does not work unless you have the simple recovery method selected.

 

[b3ge]

If you want to shrink your log down to 1k, you need to use something like this

George Esser

CREATE PROCEDURE TruncateExportLog
AS
SET NOCOUNT ON
DBCC SHRINKFILE (ExportDoc_Log, TRUNCATEONLY )
BACKUP LOG ExportDoc WITH TRUNCATE_ONLY
if exists (select * from dbo.sysobjects where id = object_id(N'DummyTbl') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table DummyTbl
CREATE TABLE DummyTbl (
MyField varchar (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
PK int NULL )
INSERT DummyTbl (PK) Values(1)
DECLARE @Index int
Set @Index=0
WHILE (@Index<2000)
BEGIN
UPDATE DummyTbl
SET MyField=MyField
WHERE PK = 1
SELECT @index=@Index + 1
END
DROP TABLE DummyTbl
DBCC SHRINKFILE (ExportDoc_Log, TRUNCATEONLY)
BACKUP LOG ExportDoc WITH TRUNCATE_ONLY
SET NOCOUNT OFF
DBCC LOGINFO(ExportDoc)
RETURN