Resource Monitoring

aarons44aarons44 Posts: 32
edited June 26, 2007 9:21PM in SQL Backup Previous Versions
Recently, our backups went from taking 2 hours to taking 8 hours. Nothing has changed in the amount of data being backed up. The only change is upgrading from SQL Backup 4 to SQL Backup 5.1. I'm not sure that is the problem, so I was wondering if there is any perfmon counters we should add to monitor SQL Backup and SQL Server, or if SQL Backup had any advanced logging that we could enable. We are running SQL Backup 5.1 with SQL Server 2005 SP2 x64 on Windows 2003 SP2 x64.
· Share on Twitter

Comments

  • Brian DonahueBrian Donahue Posts: 6,590 Bronze 1
    I can't think of a reason for this, but then again it may simply be a change in the environment, for instance, if you're backing up to a network share, can you find any issues that would cause packet loss, such as a flaky router?

    You may also want to examine your logs in %allusersprofile%\application data\red gate\sql backup\instance\logs. In the past there have been issues with Backup's peripheral activities such as copyto, backup history update, and the like. The logs will tell you how much time is being spent doing peripheral and housekeeping tasks like this as part of the backup.

    To be honest, I don't see anything different in the new version that would cause this. The backup engine has only had some minor changes done. But if you do have any more useful information about the environment, maybe I can offer some better suggestions.
    · Share on Twitter
  • soonyusoonyu Posts: 8
    "You may also want to examine your logs in %allusersprofile%\application data\red gate\sql backup\instance\logs."

    will the logs auto purge? how long it will?

    Cheers,
    Liang Yew
    · Share on Twitter
  • peteypetey Posts: 2,358 New member
    You can configure SQL Backup to delete log files older than a certain period. You do this using the SQL Backup GUI.

    - select a server
    - right click, select Options
    - under the 'SQL Backup log files' section, select the 'Delete log files in this folder' option, and specify the duration
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
    · Share on Twitter
  • aarons44aarons44 Posts: 32
    It's been happening with both the local drive and the SAN - there are two separate backups. The first backup is supposed to run from 12:00AM - 2:00AM, and writes to a SCSI attached storage device. The second backup is supposed to run from 2:15AM - 4:00AM, and writes to a fibre channel attached SAN. The server is running and backing up 800 databases. We rebooted the server, and the SCSI attached storage backup worked fine the next day, backing up in 3 hours, but the SAN backup was slow. Every day after that both were slow, with the SCSI attached storage backup taking 8 hours. We eliminated everything else as the issue, by switching back to the SQL Server Maintenence Plans. SQL Server was able to backup the same databases in 3 hours consistently.

    Looking at the SQL Backup logs, it appears that deleting the backups is what is taking so long. On the "fast" day, it took 6 seconds to delete a 4.438MB database. On the "slow" day, it took 2 minutes and 52 seconds. We have three large databases ( > 15GB), and they are also slower, but the small databases show the highest percentage of change in delete time. The small databases are taking anywhere from 10 seconds to 3 minutes or more longer to delete. I think that what is occuring is that the first backup after a reboot works fine, and then every backup after that is slow, regardless of destination. The first SCSI backup was normal, but then the SAN backup that occurs right after it was slow, and then after that they were both slow. I've set perfmon counters for processors, memory, disk queue length, and they show no difference between a fast day and a slow day.

    So, just to summarize and recap, we're running Windows 2003 x64 SP2, SQL Server 2005 x64 SP2, 32 GB of RAM, SQL Backup 5. The first backup after a reboot appears to be fast, with every backup after that becoming slow on the deletes. Perfmon counters show no difference between a fast backup and a slow backup in terms of CPU, memory or disk queue length. We're running / backing up 800 databases.
    Brian Donahue wrote:
    I can't think of a reason for this, but then again it may simply be a change in the environment, for instance, if you're backing up to a network share, can you find any issues that would cause packet loss, such as a flaky router?

    You may also want to examine your logs in %allusersprofile%\application data\red gate\sql backup\instance\logs. In the past there have been issues with Backup's peripheral activities such as copyto, backup history update, and the like. The logs will tell you how much time is being spent doing peripheral and housekeeping tasks like this as part of the backup.

    To be honest, I don't see anything different in the new version that would cause this. The backup engine has only had some minor changes done. But if you do have any more useful information about the environment, maybe I can offer some better suggestions.
    · Share on Twitter
  • peteypetey Posts: 2,358 New member
    Could you pls send me the log files of a 'slow' backup and a 'fast' backup? Email address is as per my profile. Thanks.
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
    · Share on Twitter
Sign In or Register to comment.