Long Transaction Log Back-ups

[BeckahC]

I hope someone can help me with this,

I have my DB set to back-up the Transaction Log every hour. Over the past few weeks the time it takes to run the back-up (according to job history) has been increasing by approx. a minute per week. I checked for open transactions using DBCC OPENTRAN, and none showed up. I left the back-up witht the default as far as truncating goes (so it should be truncating the log every time after backing it up).

Does anyone have any ideas?

All help is appreciated!

Thank you in advance BeckahC

 

[lory]

Is your transaction log growing? We had this happen to us where our transaction log was bigger than our database. Does it appear from the size of the trans log that it is actually truncating?

 

[BeckahC]

I'll have to keep track of that for a few days and see. The database is definitely larger than the log. They are both rather small for now. (under 22 mg and under 4 mg.) I know it's grown since it was first implemented, but the back-up of the whole database only takes less than 30 seconds and this week the log back-up has been taking over 11 minutes. We seem to have the resources to cover it, no one has complained about a slower response time, but I'd like to take the worry and have it now while there's time for it, before it becomes a larger issue.

Also, please forgive my not knowing, but how does a transaction log grow when it's being truncated after every back-up, including those occuring during off hours when no one should be using the system? It's been awhile since I've set this up and I'm just hoping I didn't forget something important... BeckahC

 

[lory]

Well, I'm rather new at this SQL stuff too, but it can happen that your transaction log keeps growing without truncating based on the properties. As long as you back up your log with truncate your log should be releasing committed data. If you set truncate log on checkpoint, then the log will write all committed data to the database and remove it from the log, but you cannot backup the log and use it to help you if there is a system failure. We do have ours set and have RAID 5 in case there is a hard drive failure. Not sure what we would do if there was database corruption.

 

[BeckahC]

I have discovered that even though my log back-ups have been set-up with the default - to truncate the log after te back-up - they are actually growing, even after a back-up!

Has anyone else noticed something like this? lory, is this what you meant?

How do you get the transaction log to truncate after the back-up like it's supposed to? Is this a bug? We're up to 11+ minutes per hourly back-up and it fluctuates with-in about 40 seconds, up and down.... BeckahC

 

[tlbroadbent]

There must be something wrong on the backup process, the server or disks. We backup 100's of megabytes of data in minutes.

Are you writing to tape or disk? If backing up to disk, how large is the transaction backup file? Are you appending to the backup file or creating a new file for each backup? If appending, how often is the backup file intialized? Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.

 

[lory]

In your database properties - Options (tab), do you have "Autoshrink" and "Truncate log on checkpoint" turned on?

 

[BeckahC]

tlbroadbent -
We backup to disk (RAID5), the backup is 1,943,860KB and is set to append. The log itself is only 5.38mb, but has been growing about 0.1mb every 20-30 minutes while I monitored it today. The back-up of the log occurs every hour.


lory -
Neither option is checked.


As it has been awhile since I took my SQL Server 7.0 Admin. class, and almost as long since I have really had to do anything with the administrator's side of it, am I doing something wrong? BeckahC

 

[lory]

I would definitely check the "Autoshrink" feature.

Here is the blurb from BOL: "The only way to free space in the log files so that they can be shrunk is to back up the transaction log, or set trunc. log on chkpt to true. The log files shrink when the log is backed up or truncated. Therefore, setting autoshrink to true will cause the log to shrink only if the log is backed up or truncated."

Since your "Autoshrink" is not set that must be the reason why it is getting larger.

 

[BeckahC]

Will this cause any loss of data? or will it affect recovery if it is ever necessary? Does this just remove the "extra" space that was left by committed transactions that were backed-up and truncated from the log? BeckahC

 

[lory]

Actually, this just removes any unused space.

Here is the blurb from BOL: "The autoshrink option causes files to be shrunk when more than 25 percent of the file contains unused space. The file is shrunk to a size where 25 percent of the file is unused space, or to the size of the file when it was created, whichever is greater."

Hope this helps.

 

[BeckahC]

Unfortunately, when I created the file I over-compensated for space, so if it won't get smaller than it's original size, that won't help me because there is always a lot of free space... but that's another story...

Everyone, Have a Happy Turkey Day!!! and a great 4 day week-end!!! (to all this applies to) BeckahC

 

[tlbroadbent]

I believe the size of the log backup file is the cause of the increasing time required to do the log backup. A log file of 5.3 MB is miniscule! Even on a slow server it can be backed up in seconds.

A backup file of 1.9GB for that log is huge. From your description, it appears to hold every log backup ever made. Usually, after doing a full backup the log backup file should be reinitialized.

Here is the general backup scenario we use. It is not the only backup strategy nor can I say it is best for your situation. However, it should be better than what you are currently doing.[ol][li]Daily Full Backup at 8:00PM.

The backup file name contains the date and time of the backup. This isn't essential but we do it for convenience.

[li]Backup server to tape 9:00PM.

[li]1st Transaction backup at 5:00AM. You should adjust this schedule according to expected activity.

The transaction log backup files also contain the date and time of the backup. Alternately, you can have the first log backup initialize the backup file.

[li]Transaction backups every 30 or 60 minutes through the day.

These backups can either be on separate files or appended to the file initialized earlier in the day. In either case, the backups will be very quick compared to what you see currently.[/ol]Let me know if you have questions about this.

Additional comments:

Do not set "trunc. log on checkpoint" or you'll not be able to recover transactions! Shrinking a database won't necessarily shrink the MDF and LDF files. You can shrink database and transaction log files smaller than the initial size by using DBCC SHRINKFILE. We find that Auto Shrink doesn't always shrink the files as we desire so we run DBCCC SHRINKDATBASE and DBCC SHRINKFILE during nightly maintenance on selected databases.

HTH Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.

 

[BeckahC]

I currently run the back-ups as jobs. Maybe I could create a step 2 after the full back-up and have that step truncate the Transaction log? Or do I need to delete the actual back-up file? Or maybe the step 2 can be a transaction log back-up that overwrites, and then it will go back to the usual appending until the next time? I think I like this last idea best....

Thank you so much for your ideas and suggestions, please let me know if anything else comes to mind... I'll let you all know if this works. BeckahC