Periodically, when I try to open a database, I get the error message Log file is full. Cannot open database. After I truncate the transaction log, I can open the database. The problem occurs with some databases and not others. Why is this happening?
You receive this error message because your database has been set up to retain transaction-log records until you back them up. Microsoft strongly recommends that you set up transaction-log retention on production systems because it provides maximum data protection. To back up the log file, use the BACKUP LOG command, then store these backup files on tape or on another server. Combined with the database backup, these transaction-log files will let you restore your database in the event of hardware or software failure.
If you want to turn off transaction-log backup and restore for SQL Server 7.0, in Enterprise Manager, navigate to the database you want to change. Right-click the database, select Properties, then on the Options tab, select the Truncate Log on Checkpoint option. For SQL Server 2000, in the Properties window's Options tab, select the Simple recovery model. Note that although you're truncating the log, uncommitted transactions will still be logged in the transaction log because you can't completely turn off transaction logging; it's an essential part of SQL Server's data-integrity scheme.