Single, huge transaction log file 2

[jheaton]

We have maintenance plans set on our SQL server that backup the database every night at 11pm. Transaction logs are set to backup on an hourly basis from 6am to 10:45pm. Everything appears to be running correctly. The database in question is about 330 MB. The transaction logs are between 16k - 4Mb each. Now for the weird part.... At 10:45pm I have a transaction log file that has grown to 19GB!!!. This always occurs for the previous night, but is gone 48 hours later, only to be replaced by another at 10:45pm the night before. My big question is why is this transaction log so large?

SQL Server = SQL Server 2000, Windows 2000 sp4

 

[JayKusch]

You have some scheduled job, application, 3rd party tool or something of that nature that is scheduled to run at that time frame.

Thanks

J. Kusch

 

[SQLBill]

Are you defragging your indexes or re-indexing as part of your maintenance plan? Are you shrinking the database? Those can cause the log to expand.

-SQLBill

 

[SQLBill]

You could also use Profiler and create a trace to 'capture' what is happening during that time.

-SQLBill

 

[jheaton]

I saw no other jobs running at the same time. In fact, during the that period of time, the SQL server appears to be fairly quiet. However, I adjusted the transaction log running time (it now stops at 9:30pm, instead of 10:45) a little and the problem appears to be gone. I suppose this means, that despite my best efforts to discover what it was, something must be running between 9:30pm & 10:45. Thank you guys for your suggestions. Also, to SQLBill, thank you for reminding me about Profiler. It's a tool that I rarely use, and had forgotten was available.