January 23, 2009 at 8:50 am
Hello, we are having a problem with the scheduled transaction log backups that doesn't make sense. This is a SQL Server 2000 database. On Sunday night there is a job run that runs a database check and a full backup with verification; there is a second job that runs Mon - Sat that runs an integrity check, a differential backup with verification; then there is a third job that runs Mon - Sat every 10 minutes to do a transaction log backup [between 1:00 AM till 9:00 PM] --- this 3rd job is the one that isn't making sense.
the sql message is similar each time to the following: "Executed as user: dba. Processed 195 pages for database 'BCDB', file 'BCDBLog2' on file 1057. [SQLSTATE 01000] (Message 4035) BACKUP LOG successfully processed 195 pages in 5.869 seconds (0.271 MB/sec). [SQLSTATE 01000] (Message 3014). The step succeeded."
The problem is the duration of the job will be like 4 minutes 29 seconds; if the job statement is run through from query analyzer it also takes minutes rather than seconds to say the tran log backup is complete. I've just noticed the job duration has been climbing over the past week -- but am not sure when it started.
Any ideas what could cause this and what can be done to get the job back down to a normal duration?
Thank you in advance for any help.
Additional info in case it will help: DB size is approx. 58 GB, full backup and differentials write out to a set of four logical devices; the transaction log backup writes out to it's own one logical device; the database itself has the data in only two filegroups that each have 4 files; the transaction log is setup as four files and collectively is about 12 Gb in size
January 23, 2009 at 1:28 pm
Few questions.
Why is the tran log set up as 4 files?
Are the log files on a separate physical drive from the data files?
Likewise, is the drive that the log backups are writing to a separate physical drive, or just a different partition?
Are you seeing full or differential backups getting longer too, or is it just the logs?
Is the size of the log backup around the same size each time, or is it growing?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 23, 2009 at 1:36 pm
Gail:
First, thank you for replying.
I don't know why the original DBA created the log with 4 files. They are/were on the same physical drive so there was nothing gained. I have shrunk, emptied and removed 2 of the files today just to see if there would be any effect. There wasn't.
The full database backup takes 28 minutes and has for months. The differentials nightly take about 10 minutes, and have not changed either.
The trans log backups are on a different drive from the actual transaction logs.
January 23, 2009 at 2:07 pm
Ellen (1/23/2009)
I don't know why the original DBA created the log with 4 files. They are/were on the same physical drive so there was nothing gained. I have shrunk, emptied and removed 2 of the files today just to see if there would be any effect. There wasn't.
You gain nothing from multiple log files anyway, even if they are on different drives. The log is written sequentially, not in parallel across all files
Is the log backup the same size each time?
Can you check sysprocesses while a log backup is running. See if there's a wait time and, if so, what the last wait type is.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 23, 2009 at 3:18 pm
I have watched the Activity Monitor while the job was running and it does not show any waittype -- I assume sysprocesses should have the same info
When I tried to look at the Media Contents for the device it just got lost in time and never came back with the contents
The transaction log backup file is appended to with each log backup, it has grown from around 5 Gb to 8 Gb from Wednesday till today.
One thing I am finding is that the system tables with the backup info have old old data in them. It includes backups for logical files that no longer exist. I wonder if I should clean up these tables?
To answer an earlier question, the database data files are on a separate physical drive from the transaction log files.
January 24, 2009 at 1:21 am
Ellen (1/23/2009)
The transaction log backup file is appended to with each log backup, it has grown from around 5 Gb to 8 Gb from Wednesday till today.
That may be the cause. There are a number of reasons why it's not a good idea to append backups to one file. One is reliability - damage the file, lose everything.
Try changing the log backups so that each goes to it's own file and see if that helps.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 25, 2009 at 8:49 am
Not to be dumb but, is the syntax to backup without append 'with init' or do I still use 'with noinit' but have additional syntax for individual log file backup instead of appending?
Thank you much.
January 25, 2009 at 10:37 am
Well, if you do with init, you'll overwrite the old backup and hence break the log chain. Not a good idea.
I just change the backup command so that each log file gets a different name, usually by appending the date and time to the name. That way it doesn't matter what switches you use, the filename differs for every backup
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 26, 2009 at 9:31 am
That is what is different ... right now the log backup writes to a logical device so the only choice was to append or overwrite... I will switch that to write to a file with datetime included in the name.
Thank you.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply