msdb..backupset.backup_finish_date -- changes from SQL2000?

It appears that the behavior has changed for the backupset table in the MSDB database between versions.

In SQL2000 backup_start_date & backup_finish_date were populated correctly. In 2005, backup_finish_date is the same as the backup_start_date value. Is this a bug or should I be looking for my backup timing elsewhere in 2005?

Thank you.

I can't repro the problem. It works fine for me.

I use this query to get a quick look at timings.

select top 100 database_name, backup_set_id, type, backup_finish_date, backup_start_date,
datediff (second,backup_start_date, backup_finish_date) secondsToComplete
,convert (bigint, backup_size / 1048576 ) sizeInMB

from msdb..backupset
where type = 'D'
order by backup_finish_date desc

If you can describe a repro, please let us know.

|||

It appears to work fine for database backups, I see timing differences in them. However for log backups (type = 'L') it appears that it's not changing, but I'm still investigating.

I was originally pursuing a long running t-log backup time and that's how I noticed this behavior. I've since discovered that if a backup device has a large # of backups appended to it, it dramaticly effects the time it takes to perform the backup. I was suprised to find that the finish time was being reported as the same as the start time when it clearly was taking much longer to perform the backup...

Currently (and with a small # of appends), my backups are averaging around 1/10th second so I'll have to try to dummy up some logspace and see if it's just that my backups are running quicker than the process can account for, or if it is indeed a bug.

I'll have more in a day or so once I can get some testing done. Thank you for looking into this.

|||

Your info helps.

Our start/finish times do NOT include the time it takes to open the media set and prepare it for writing.

The time only includes the time that is actually spent transferring data. This is the same logic as exists in sql2000.

For example, it may take many minutes to open a tape drive and seek to the end when appending data. That time is not included.

So if you want to also include that time, you'll need to add your own start/stop time.

This might already work if you use sqlagent jobs, and look up the start/top time in the job history.

Are you backing up to tape? That will always be relatively slow, and gets worse when appending backups.

If you have a lot of backups to append in a batch, such as a nightly job, you can use BACKUP WITH NOREWIND which avoids all the time to REWIND/SEEK to end that happens in such a scenario.

If you are backing up to disk, then having a lot of backups in the file shouldn't really matter...to a point.

1000's of backups WILL result in significant delay. The reason is that when setting up for the backup, we perform a synchronous read of all the marks before/after each backup set.

For disk backups, my recommendation is to prefer the use of a single filesystem directory, then write each backup into a separate disk file. That gives you better control over retention and space management. But I'd agree that it might be slightly more work to wrap this in your backup jobs. If you are using our management tools, they already do this by naming the log backups as ***.TRN where the *** includes the database name and timestamp of the backup.

Hope that helps.