Truncating the logs?

ehuna

I have a question on whether I should attempt to truncate the SQL logs if I'm using SQL Backup both for full backups and log shipping.

Here's our current setup:

1) Full backup at 2:00 AM.
2) Hourly Log backups.
3) We truncate the logs after the full backup

But with SQL Backup Log shipping, I'm no longer sure whether we should truncate the logs. Here's our plan:

1) Full backup (with SQL Backup) at 2:00 AM.
2) Backup Logs for Log shipping hourly from 2:45 AM -> 00:45 AM.
3) Restore Logs on standby server hourly from 3:15 AM -> 2:15 AM.

Questions:

1) Is it ok for us to truncate the logs after the full backup at 2:00 AM or will this break log shipping?

2) If we are not truncating the logs, how do we stop the logs from growing out of control (gigabytes and gigabytes of data)?

petey

By truncating the logs, do you mean

- manually reducing the size of the database log (ldf) files by running DBCC SHRINKFILE or something similar

or

- preventing the database log files from truncating during a backup by using the NO_TRUNCATE option

I'm guessing it's the first item, but because you use the term 'truncate' and 'logs' in the same sentence ... But if it is the second item, I would certainly like to know why you are doing it that way.

Looking at your log shipping plan for SQL Backup, I guess you are concerned with the accumulated logs from 00:45 AM to 2:00 AM, and from 2:00 AM to 2:45 AM. Whatever you do, SQL Server will only 'truncate' your log files so that it will still contain log data that needs to be backed up.

So say the transaction log backup ran at 00:45 AM, and there are some updates made before 2:00 AM. 2:00 AM comes around and you run DBCC SHRINKFILE on the log file. When 2:45 AM comes around and the transaction log backup runs, it will contain the log data from 00:45 to 2:45. A DBCC SHRINKFILE or a full backup does not break the log sequence.

See 'Shrinking the Transaction Log' in SQL Server 2000 Books Online for a more thorough explanation.

ehuna

Peter, thanks for the fast response. I think I understand, but let me be a bit more specific.

Suppose I'm using only the SQL Backup UI to configure my SQL full backups and log shipping.

Is there anything else I need to do to make sure that the logs don't just keep growing every day.

In other words, should I have a manual step to "manually reduce the size of the database log (ldf) files by running DBCC SHRINKFILE or something similar", or does master..sqlbackup do it for me?

If master..sqlbackup doesn't do it, when should I do it and what exact command should I use? (e.g. "DBCC SHRINKFILE ...")

petey

Technically, the log files shouldn't be growing every day. Everytime you make a transaction log backup, previously used space would be made available for new log entries, unless you are using the NO_TRUNCATE option (but the Log Shipping Wizard does not provide an option to use it).

The sqlbackup command will not shrink the log files. You would need to manually add a step to run DBCC SHRINKFILE if that's what you want. As for the parameters, you need to know what 'default' size you are comfortable with. See Books Online for details.

Also consider the points in this article: http://www.karaszi.com/SQLServer/info_dont_shrink.asp

You might also consider making the transaction log backups in shorter time intervals, as this will 'flush' out the log files more frequently, allowing you to 'recycle' the space without having to grow the log files.

ehuna

Thanks Peter, that's excellent info. I will attempt not to shrink the LDF files and see how that goes. Since the space is recovered after every log backup (configured through SQL Backup) we should be ok.

petey

the space is recovered after every log backup (configured through SQL Backup) we should be ok.

It's not configured by SQL Backup. It is performed by SQL Server automatically.

ehuna

I meant the log backup is configured by SQL backup. And therefore since the NO TRUNCATE option is not used, the space is recovered automatically (by SQL Server itself indeed).