Transaction Log file continues to grow after full backups.

herzenberg

We are new users of your software and might not have set up our backups properly because our trx log files are not truncating after a complete or differential backup. How can we get this to happen?
TIA.

Jason Cook

SQL Backup behaves much the same as Native Backups in this regard. By default the log will be truncated when a log backup is performed, but will not be truncated during a full/complete or differential backup.

--

If you need to truncate the log only and don't want to perform transaction log backups, natively the TRUNCATE_ONLY keyword exists.

However since this breaks the log chain, Microsoft are planning to deprecate this keyword in a future release - a full or differential backup must be performed immediately after executing the command, otherwise a break in the backup chain will occur.


If you are sure that is what you want to do, SQL Backup can perform a similar operation, using the following command:

execute master..sqlbackup '-SQL "BACKUP LOG [dbname] TO DISK = ''c:\backup.sqb'' WITH NOWRITE"';

The DISK location is required but can be set to an arbitrary value (e.g. 'c:\backup.sqb'). Likewise, a full or differential *must* be performed immediately after the truncation.

More information on the above is provided in Books Online here: http://msdn2.microsoft.com/en-us/library/ms189085.aspx

Hope that helps,
Jason

herzenberg

Thanks for the answer. Now another question. We use your front end to create and monitor the backup operation. I don't see the option to add steps to the jobs so how do we add the code you provided to the job your front end creates?

Jason Cook

The created job can be edited through Enterprise Manager or Management Studio in the same way as any other job created outside the SQL Backup interface.

The following example shows a full backup of the database 'mydatabase' generated as a job by SQL Backup 4. The *** Insert *** comment is where you would need to add the NOWRITE statement mentioned previously.

The job would then perform the truncation of the log, and then the full backup of the database 'mydatabase' at the scheduled times.

DECLARE @exitcode int
DECLARE @sqlerrorcode int
*** Insert NOWRITE command here ***
EXECUTE master..sqlbackup N'-SQL "BACKUP DATABASE [mydatabase] TO DISK = ''<AUTO>'' WITH COMPRESSION = 1"', @exitcode OUT, @sqlerrorcode OUT
IF (@exitcode >= 500) OR (@sqlerrorcode <> 0)
BEGIN
RAISERROR ('SQL Backup failed with exit code: %d  SQL error code: %d', 16, 1, @exitcode, @sqlerrorcode)
END

Alternatively, you can create a new job in Enterprise Manager or Management Studio and for the "steps" section, paste in the NOWRITE code mentioned before.

Hope that helps,
Jason