sql2000 transaction log keeps filling up

Question

sql2000 transaction log keeps filling up

71 camaro

SSC Eights!

Points: 911
More actions
March 14, 2008 at 7:26 am

#183344

I have a sql2000 dbase in simple recovery mode that keep filling up. The last 2 days I had to manually do a backup log with truncate_only to it. It just started
This is a projectserver dbase I was wondering if anyone else ran into this and why.
I tracked the growth and it all occurred in one hour at midnight.
I am sure it is orphaned transactions but need to know why ??
I have read articles supporting this.
any input would be appreciated

Viewing 7 posts - 1 through 6 (of 6 total)

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

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 1

Do you have users using the database at midnight? If not, it's probably a scheduled job that's doing it. If you do have users on at that time, try running Profiler to see what transactions are run. I think you can also use Perfmon to monitor when and how often the log is growing. It sounds like you have a process that's happening that needs to be split into batches in order to give the transaction log a chance to clear down.

John

MarkusB SSC-Dedicated Points: 37370 More actions · Answer 2

Do you run a reindexing job at midnight? that would explain the growth of the tlog.

[font="Verdana"]Markus Bohse[/font]

71 camaro SSC Eights! Points: 911 More actions · Answer 3

the problem is the transaction log is sized at 12 gig and 10 gig of it is used. In simple recovery it should check point and clear when completed the above Correct ?? ( Note db is in simple recovery)

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 4

Is your server involved in replication or log shipping? If not, it sounds as if you have 10GB of uncommitted transaction(s). Try using DBCC OPENTRAN and sp_who2 to get information on transactions and processes that have been running the longest.

John

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 5

Have you tried simply issuing a "checkpoint" command? See if it just needs to be reminded to move along?

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

71 camaro SSC Eights! Points: 911 More actions · Answer 6

I am hoping this has something to do with it but earlier in the same day our system dept. applied MS patches.

I am scheduling a reboot and hopefully everything will be fine.