Question about transaction log growing...

[bessebo]

I have noticed something rather peculiar yesterday and today. We perform transaction log backups every 15 minutes. We also perform full backups every night. In the morning I also perform a transaction log backup with init to clean out the old transaction log backup file and start with the new transaction log backup.

One thing I have noticed is that our transaction log has not gotten smaller like it always does after the full backup is complete. It isn't exhorbitant (only 1.3GB and we have it sized at 50GB) but I am concerned that it has not gone down.

Does anyone out there have any idea why this is happening? We are using SQL2000 and service pack 3. All of the backups have been successful. Any help would be appreciated.

Regards,
Bessebo

 

[mutley1]

Firstly, are your tran log backups working? If they are failing, then the DB is probably in siple recovery mode.

Secondly, when you say it is 1.3GB, is that the size of the actual log file or the amount of data?

If you mean the size of the actual file is 1.3GB, is it constantly growing?

If you can answer these it will help to give a more accurate response.

Cheers,

M.

 

[bessebo]

The transaction log backups are all successful. No problems there. After I perform the backup log/init at 5AM every morning the actual size of the transaction log backup file gets smaller as it should. I was thinking that maybe the GUI (Enterprise Manager) had a false reading but I just did a DBCC SQLPERF(LOGSPACE) and it shows as using 2.75% of the log space which is roughly (1368MB used/50000MB total) so it looks like it is reading correctly.

Yes, the transaction log is growing slowly and it doesn't appear to be getting smaller even with the transaction log backups running successfully every 15 minutes. This is my concern. Obviously I am not in a critical state as there is more than 48GB available but this is not typical that the transaction log is not getting smaller. It is almost like a checkpoint is not being issued. I am at a loss.

I may just restore our existing database to our test environment to see if I can reproduce this. Or maybe if I stop and restart SQLServer the issue will be resolved. Any suggestions?

Regards,
Bessebo

 

[onpnt]

you have to back the log up and then shrink it. A backup is not enough to shrink the log. Also you should get to SP4.

[sub]____________ signature below ______________
I am Tedward Keyboardhands!!!

http://www.lessthandot.com

coming to your keyboards soon[/sub]

 

[bessebo]

I understand about getting to SP4 but we are running an older ERP system and we will be upgrading over the next year and we really didn't want to upset the apple cart. I really don't have any interest to shrink the transaction log because we use almost the full 50GB every week when we rebuild indexes. I just want the space that is used to shrink. Maybe shrinking it is the only alternative and then let it expand on its own when we rebuild indexes.

Do you have any explanation as to why the space that is being used for the transaction log is not getting smaller after the transaction log backups take place? It always has until now.