Transaction logs continually growing... 3

[jballum]

I have read a number of posts concerning this problem and even retrieved a very neat stored procedure that when run will trim the transaction logs down to a minimal size.

The problem is that at one client site the logs grow so large and so quickly that having them kick all of their world wide customers off of the database at least once a month to manually truncate the logs is unacceptable.

What are the correct settings for their databases so that the log will truncate itself of not needed data (ie. more of an automated process rather than manual)?

NT 4 (with required service packs)
SQL Server 7 (with required service packs)
Veritas Backup Exec (does a full database backup of databases every night)

Thanks for any help.

JB

 

[TomSark]

I would like to know what your recovery strategy is for this database, but...

If I understand your question, and IF you don't need to use the transaction log as part of your recovery process (you don't need to restore the database from a particular point in time), then you can set the transaction log to truncate when a checkpoint occurs using the following T-SQL command:

sp_dboption 'pubs', 'trunc. log on chkpt.', true

This would set the 'trunc. log on chkpt.' for the pubs database.

Performing a backup will truncate the active portion of the log as well...


Tom

 

[jballum]

Tom,

Thanks for your prompt reply.

This site's recovery process is currently set up to only recover to the point in time of the last full backup (was decided before I came and they aren't ready to change to my recommendations).

As for setting the 'trunc. log on chkpt' for the database. They tried this but the utility they are using to perform their nightly backups (Veritas Backup Exec) does not complete backups successfully when this option is set (as I have been told).

Now, if I get them to add to their current backups to backup the transactions log as well and have them set the 'auto shrink' flag will this cause the transaction logs to shrink on their own. (Note: This site is looking to have as much of an automated database as possible)

Thanks again,

JB

 

[TomSark]

Hmmm... I use this option on a database and use veritas and don't see this as an issue because I back up the bak files to tape, not the mdfs, ndfs, and ldfs...

So... the truncate log on checkpoint option shouldn't be an issue, but, if for some reason it is, then back up the log and this will remove the inactive portion of it...


Here's a blurb from books-online:

Log truncation occurs at these points:

At the completion of a BACKUP LOG statement.

Every time a checkpoint is processed, if the database is in truncate mode. This includes both explicit checkpoints resulting from a CHECKPOINT statement and implicit checkpoints generated by the system. For more information about the interval between automatic checkpoints, see Checkpoints and the Active Portion of the Log..

Hope this helps...

Tom

 

[jballum]

Tom,

Just to clarify your response. Are you saying that what I suggested will work (backing up transaction logs and setting database to auto shrink)? When I say work I mean keep the transaction logs from growing out of control and hogging all disk space and not cause any problems to their existing recovery process (as minimal as it currently is).

Thanx,

JB

 

[TomSark]

One more thing, are you using replication?

Tom

 

[jballum]

No replication at this point, but they are talking about mirroring in the future.

 

[TomSark]

Well.. you can either back up the log or use truncate log on checkpoint, or truncate the log... those are your choices... you could truncate the log in this case because you don't use it for recovery purposes, even though you mentioned you didn't want to...

FYI... You can issue a backup log with truncate_only while your users are using the database.. you don't have to be in single user mode to do this...


Tom

 

[jballum]

Tom,

Thanks for all your suggestions.

But will this automate the release of space acquired by the transaction log or just clear out the used space in the transaction log while keeping the allocated space for the file itself?

JB

 

[TomSark]

You could shrink the file (i.e. as in turning the auto shrink option on), or using DBCC SHRINKFILE. I was assuming you ran out of space on your log because I was thinking the log filled up or possibly you had an old transaction.

With this new info in mind, .. here's a BOL blurb about this:

DBCC SHRINKFILE
( {file_name | file_id }
{ [, target_size]
| [, {EMPTYFILE | NOTRUNCATE | TRUNCATEONLY}]
}
)

For log files, SQL Server uses target_size to calculate the target size for the entire log; therefore, target_size is the amount of free space in the log after the shrink operation. Target size for the entire log is then translated to target size for each log file. Unlike data files, the shrinking of log files is not immediate. Each log file is marked with the target size of the shrink operation. Each subsequent log backup or log truncation attempts to shrink the file and bring its size as close to the target size as possible. Because a log file can only be shrunk to a virtual log file boundary, it may not be possible to shrink a log file to a size smaller than the size of a virtual log file, even if it is not being used. For example, a database with a log file of 1 GB can have the log file shrunk to only 128 MB. For more information on when truncation occurs, see Truncating the Transaction Log. For more information about determining virtual log file sizes, see Virtual Log Files.


Tom

 

[jballum]

Tom,

They didn't run out of space in their log file but as the log files are growing they are running out of space on their server.

It sounds like that what I initially asked about is a solution for them:

1. Set auto shrink on for the database.
2. Backup up the transaction log as well nightly.

Thanks again for your suggestion and help.

JB

 

[calahans]

Hi JB,

You must be extremely carefule when deciding on what stategy to use on log files. If you select the "truncate log on checkpoint" option, then if you lose you database during the day, your log will be useless to bring you back to the current situation. This is often not acceptable from most DBAs view points.

If you don't truncate a log, you can synchronise a log and last nights database to bring you back to within the last checkpoint as opposed to the last backup.

As tom says above then you should get the auto truncate on the backup - is this nightly?



Cal

 

[jballum]

Cal,

I am in a situation where things are in place already and all I can do is recommend (and I have). The "truncate log on checkpoint" option is not set (as I said above).

The backups are done nightly with a tool that I mentioned above as well and they said they are doing complete backups (which I assume include transaction logs) but I am thinking from all that I have been told and read that maybe they are just doing the database because there has never been any autotruncate happening to the logs.

Thanx,

JB

 

[lsm]

I'm having the same problem with my log files. I have taken steps to prevent any further growth, but have been unable to physically reduce the size of the log file. I have tried 'DBCC shrinkfile' but it doesn't seem to be doing anything. Does anyone have any ideas?

 

[foxdev]

Keep in mind that if the active portion of the log is physically at the end of the log file, the shrinkage will not happen immediately. As log writes occur, SQLS will eventually wrap the active portion back to the beginning of the file and will, someday, truncate the file and release the space to the OS.

Robert Bradley

http://www.foxdev.com/