Recovery mode of a MSSQL 2000 SP3 database is set to full.
Size of database is about 80GB.
Backup of the transaction log takes place every 2 hours daily.
Transaction log file is set to grow by 500MB at a time, with no restriction on size.
The transaction log file seems to be growing out of control. Presently at about 45GB! and growing (or possibly stabilising at this size). I once shrank it, using the shrink-file option in the data maintenance wizard, to 1GB. But within a fortnight, it re-generated to its present size.
I had always thought that by regular backups of the transaction log, it will keep the physical file small.Without the need to manually shrink it. And if I do, how would I know what size would be appropriate? I have tried arbitrarily setting a maximum size to allow it to grow to (1GB). But within hours the application crash, complaining of inadequate log space.
Would really appreciate some expert advice, for which my thanks in advance.