Truncate Trans Log Every Night

[JohnSouth]

Hi

I've a SQL 7 Application program that clears some Tables and Reloads them with new data every night. The Transaction Log grows at an alarming rate! How can I ensure that the Transaction Log is cleared down every night? I've tried various combinations of backup but it's still growing.

Cheers

John South
JS Associates
Pangbourne UK

 

[jkb3]

I found this in BOOKS ONLINE:

CHECKPOINT (T-SQL)
Forces all dirty pages for the current database to be written to disk. Dirty pages are data or log pages that have been modified after they were read into the buffer cache, but the modifications have not yet been written to disk. If the current database is in log truncate mode, CHECKPOINT also truncates the inactive portion of the log. For more information about log truncation, Truncating the Transaction Log.

Syntax
CHECKPOINT

Remarks
The CHECKPOINT statement saves time in a subsequent recovery by creating a point at which all modifications to data and log pages are guaranteed to have been written to disk.

A typical checkpoint takes 1 second to complete, although this figure varies depending on the amount of activity on SQL Server and the size of the data cache.

Checkpoints also occur:

When a database option is changed with sp_dboption. A checkpoint is executed in the database in which the option is changed.
When a server is stopped, a checkpoint is executed in each database on the server. These methods of stopping SQL Server 7.0 checkpoint each database:
Using SQL Server Service Manager.
Using SQL Server Enterprise Manager.
Using the SHUTDOWN statement.
Using the Windows NT command net stop mssqlserver on the command prompt.
Using the services icon in the Windows NT control panel, selecting the mssqlserver service, and clicking the stop button.
The SHUTDOWN WITH NOWAIT statement shuts down SQL Server without executing a checkpoint in each database. This may cause the subsequent restart to take a longer time than usual to recover the databases on the server.

SQL Server 7.0 also automatically checkpoints any database where the lesser of these two conditions occur:

The active portion of the log exceeds the size that the server could recover in the amount of time specified in the recovery interval server configuration option.
If the database is in log truncate mode and the log becomes 70% full.
A database is in log truncate mode when both these conditions are true:

The trunc. log on chkpt. database option is set to TRUE.
One of these events has occurred after the last BACKUP DATABASE statement referencing the database was executed:
A BACKUP LOG statement referencing the database is executed with either the NO_LOG or TRUNCATE_ONLY clauses.
A nonlogged operation is performed in the database, such as a nonlogged bulk copy operation or a nonlogged WRITETEXT statement is executed.
An ALTER DATABASE statement that adds or deletes a file in the database is executed.
Permissions
Permission to use the CHECKPOINT statement defaults to members of the db_owner fixed database role, and is not transferable.


End of Text

So it seems that if you set your database to 'truncate log on checkpoint', and then set up a stored procedure containing the CHECKPOINT statement, and finally add a job on the server that kicks off that stored procedure every night, your problem should be solved. I would also remember to back up often.

jkb3