April 19, 2011 at 8:50 am
I could use some assistance chasing down a backup performance issue - I'm really just looking for more ideas on where to look at this point.
One of our databases usually takes 2 hours and 5-6 minutes to backup. It's been like clockwork for months.
Last Monday morning, this suddenly because a 5-6 HOUR process on both Wed and Thurs am. We had some major network issues happen the day prior that took a couple of days to resolve, so I put the delay down to those issues, and Fri/Sat am we were back to my usual just over 2 hour process.
Monday morning, and I'm back to a 6 hour backup. I set the 3004 and 3605 trace flags, and reviewed the new logs this morning - but there's really nothing of use in there. Just a much longer time between Started File and Completed File for the .mdf file for this database.
The backup is being run to a storage server that is sitting right next to the SQL server using a UNC filename. I can't find anything unusual in the SQL logs or the event logs on the SQL Server itself. (SQL 2008 SP 1)
My best guess at this point is that this is somehow related to either our domain controller (which was taken offline and replaced with a new one during the network issue or with DNS), but I could use some references to help me figure out what else our team can look at.
(We're currently without a network admin, so there are several of us being "accidental" network admins until we hire a new network admin.)
Anyplace someone can point me or ideas on what to check would be helpful.
Thanks in advance!
-Ki
April 19, 2011 at 9:01 am
Database Size ?
Log Size ?
you are taking backup as Network ,Tape or SAN
example
Network Backup like
\\servername\sharedfolder\filename
this storage is SAN , NAS or network path , IF this SAN did you check HBA card IO reads and writes
Regards,
Syed Jahanzaib Bin Hassan
MCTS | MCITP | OCA | OCP | OCE | SCJP | IBMCDBA
My Blog
Regards,
Syed Jahanzaib Bin Hassan
BSCS | MCTS | MCITP | OCA | OCP | OCE | SCJP | IBMCDBA
My Blog
www.aureus-salah.com
April 19, 2011 at 9:07 am
You have most likely already verified this, but do you have another other database maintenance running during the backup window? Any bulk imports, ETL, etc.?
April 19, 2011 at 9:13 am
Syed Jahanzaib Bin hassan (4/19/2011)
Log Size ?
you are taking backup as Network ,Tape or SAN
example
Network Backup like
\\servername\sharedfolder\filename
this storage is SAN , NAS or network path , IF this SAN did you check HBA card IO reads and writes
Regards,
Syed Jahanzaib Bin Hassan
MCTS | MCITP | OCA | OCP | OCE | SCJP | IBMCDBA
My Blog
http://www.aureus-salah.com[/quote%5D
Database size: 125 GB (it's not that big of a database)
Log size: 8 GB. Log is about 10% full when the backup starts.
It's a network backup - I thought I'd remembered to include that in the original post. Sorry if I didn't.
The database size has stayed consistent - there's been no major changes there. Similarly with log size and log use. I monitor that pretty closely - it's been consistent.
-Ki
April 19, 2011 at 9:14 am
nivek-224024 (4/19/2011)
You have most likely already verified this, but do you have another other database maintenance running during the backup window? Any bulk imports, ETL, etc.?
Yup. I checked, but when asking for another set of eyes I'm quite happy to go back to basics and make sure there's power to the building and the equipment is turned on.
During the normal backup window, there's nothing else happening - and wasn't going on during these backups for the first 4 1/2 hours. Past that, we start running into normal daily processing, which is why the change in duration is an issue.
-Ki
April 19, 2011 at 11:40 am
My thought is that the slowdown is occurring either as the backup traverses the network or on the storage on which it is being created. Is that SAN storage behind the UNC path? If those underlying disks are shared, there may be another system (Exchange, etc.) using a lot of IO while you are attempting to write your backup.
Though not a fix, have you considered striping the backup across multiple files if you are not already? That will provide a performance boost.
April 19, 2011 at 11:47 am
nivek-224024 (4/19/2011)
My thought is that the slowdown is occurring either as the backup traverses the network or on the storage on which it is being created. Is that SAN storage behind the UNC path? If those underlying disks are shared, there may be another system (Exchange, etc.) using a lot of IO while you are attempting to write your backup.Though not a fix, have you considered striping the backup across multiple files if you are not already? That will provide a performance boost.
I would agree with your thought. I've been hesitant to set up a windows perfmon session on that server and leave it unattended, but after talking with some of my teammates I'm going to set one up tonight and let it run. There are just too many questions and not enough hard information at the moment - and the only way we're going to get that data is to monitor the process.
Sometimes, you want a quick lightbulb to go on over your head. My question here was a long shot - I'm going to buckle down and chase this down the "hard" - aka "normal" way. 🙂
-Ki
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply