I have a SAP ECC instance which runs on SQL Server 2000. The database is set up with the Full recovery model, but a transaction log backup is never done (not my choice). Instead, a full backup is saved daily.
This of course implies that the transaction log file keeps growing. To control its size, once in a month the transaction log is truncated, and then shrunk with dbcc shrinkfile
(again, not my choice).
As you can easily guess, this doesn't work very well because sometimes the log file grows too fast, fills its partition, and the database hangs. I was asked to make this situation better.
If the choice would be mine I'd do a transaction log backup multiple times per day, but the person that's actually in charge doesn't want that.
The second obvious choice would be to run the truncate & shrink job more often. But from what I understand it's not a good choice. One thing I've read here worries me the most:
by truncating the log at (time), you've just completely invalidated the consistency of your log and there's NO way to recover ANY data past (time of the last full backup).
Is this true? Does it mean the log file in use now is completely useless? If I started to regularly backup it now would it become usable for a point-in-time recovery? Would the log backups be consistent? Am I better off using the Simple recovery model?
Thanks.