Transaction Log Filling Up...HELP!!

Transaction Log Filling Up...HELP!!

Post by Melvin Marin » Tue, 15 Sep 1998 04:00:00



Hi,

I have a problem with SQL Server 6.5(SP4) on Compaq, NT 4.0(SP3) where
the transaction log fills up every few days. The tran log is on a
separate device and I have increased the log size up to 1GB and it
still fills up! The actual database is only about 6MB in size. I do a
'dump transaction with truncate only' to get around the problem. My
question is does anyone know of any tips or tricks that could be used
to trap or trace the transaction that is filling up the log?

Thanks in advance for any responses.

--

Melvin Mariney

 
 
 

Transaction Log Filling Up...HELP!!

Post by Melvin Marin » Tue, 15 Sep 1998 04:00:00



>Hi,

>I have a problem with SQL Server 6.5(SP4) on Compaq, NT 4.0(SP3) where
>the transaction log fills up every few days. The tran log is on a
>separate device and I have increased the log size up to 1GB and it
>still fills up! The actual database is only about 6MB in size. I do a
>'dump transaction with truncate only' to get around the problem. My
>question is does anyone know of any tips or tricks that could be used
>to trap or trace the transaction that is filling up the log?

I forgot to add that I do back up the tran log every night and this is
not a high update database.

thanks

--

Melvin Mariney


 
 
 

Transaction Log Filling Up...HELP!!

Post by RobertSimmon » Wed, 16 Sep 1998 04:00:00


Hello,
There are a few things you an do:

1. If you have no plan to perform any maintanence on a regular basis on
this database ( ie. dump the transaction log ) you need to set the
'trunc log on chkpt' option on. This will automatically truncate
committed transaction about every minute.

2. Use the scheduler to dump that transaction log to disk, tape or
truncate_only on a regular basis.

3. I think it is possible to imitate Sybase's threshold stored procedure
but using the SQL Executive or the SQL Monitor determine the size of the
transaction log and have it start an ISQL session that can dump the
transaction log. You have to write the bat file and sql script for isql
to execute.

Remember that dumping the database needs to be followed or preceeded
with a transaction log dump. As I recall, dump database doesn't clear
out the syslogs table.
If you do this an still have problems with the log filling up, I'd say
that you have an application that fails to commit transactions but
continues to run until the log is full.

Good luck, Robert


> Hi,

> I have a problem with SQL Server 6.5(SP4) on Compaq, NT 4.0(SP3) where
> the transaction log fills up every few days. The tran log is on a
> separate device and I have increased the log size up to 1GB and it
> still fills up! The actual database is only about 6MB in size. I do a
> 'dump transaction with truncate only' to get around the problem. My
> question is does anyone know of any tips or tricks that could be used
> to trap or trace the transaction that is filling up the log?

> Thanks in advance for any responses.

> --

> Melvin Mariney


 
 
 

Transaction Log Filling Up...HELP!!

Post by Neil Pik » Thu, 17 Sep 1998 04:00:00


 Melvin,

  You can look at the log with tools from www.dbsg.com and
www.platinum.com.  Also run dbcc opentrans to see if there are
unfinished transactions.  Also have you eve used replication on this
system?

 Neil Pike MVP/MCSE
 Protech Computing Ltd
 (Please post ALL replies to the newsgroup only unless indicated
otherwise)

 
 
 

1. transaction log filling up and transaction replication

Our transaction log is filling up on the server being replicated to
(subscriber). We tried to backup the db, but it never seems to reach a
checkpoint. I was able to truncate the log, but of course that hosed
replication. What is the strategy for preventing the log from filling up.
Right now we have the db and log set to grow.

Thoughts,
Geoffrey

2. crack password FM5

3. Help - Queries filling transaction log

4. converting text to speech using oracle-D2K

5. Urgent help needed Unable to clear transaction log which is filled and causing problems

6. VFP50 problems opening and closing free tables

7. transaction log fills every 30 days log file size is 500MB is this normal

8. US, CA, Oracle Designer 2000

9. Transaction log fills up HELP!

10. Help - Queries filling transaction log

11. Transaction log filling up:Design Help required

12. transaction log backup filling drive

13. Transaction log fills up