Hello,
The following information assumes that the SQL Server database option "Truncate log on checkpoint" is disabled (SQL7), or is set to full recovery mode (SQL2000)
When SQL tancsaction logs are backed up the transaction logs are flushed as follows:
First, back up the database, then the transaction log, then BE sends a signal to the SQL server that BE has finished a successful backup, and SQL should truncate its logs. It is important to note BE does not truncate the transaction logs, rather BE sends the signal to SQL, and SQL truncates its own transaction logs.
When the transaction logs are not set to truncate on checkpoint, the logs will continue to grow until receiving a command from the SQL server to truncate, or until they run out of hard drive space. In the SQL Enterprise Manager, go into the databases folder, right-click , and select properties on one of the databases. This shows more detailed information about the database and how it is set to handle transaction logs (on the Transaction Log tab). Transaction logs can be set to unrestricted growth, or a hard limit may be imposed on how large they can get. By default, transaction logs are set to unrestricted file growth. This insures the database won't crash just because some arbitrary size of file is reached.
The other scenario is when the truncate on checkpoint option is enabled (which you set on the database properties "Options" tab). When this is enabled, the SQL server regularly truncates the transaction log. At specific intervals, the SQL server parses the transaction log, determines what transactions have been committed to the database, and truncates those transactions. So, in effect, the transaction log is in continual use, and nothing can back it up. This does not mean the databases can not be restored, it means what can be restored is the full database from the last good backup.
NOTE : If we do not receive your reply within two business days, this post would be marked assumed answeredand would be moved toanswered questions pool.
