February 24, 2015 at 5:43 am
Hi,
Currently, working with SQL backups.
I am looking for the route cause of slow backups. Our current environment is 208R2. We have a database around 450GB. Our current backup is fullbackup everyday. Previously, it took only 3 hrs but now Our backups are running very long hours most of the time 7-10hr.
We are taking the backups to remote server, which has the firewall restrictions. Previous also it is the same process (Same servers) but just now it is happening.
What could be the problem? How to identify the cause for slow backup
We are using Native compression backup no 3rd party tools
Can some one give me ideas to identify this
February 24, 2015 at 5:48 am
ramana3327 (2/24/2015)
Hi,Currently, working with SQL backups.
I am looking for the route cause of slow backups. Our current environment is 208R2. We have a database around 450GB. Our current backup is fullbackup everyday. Previously, it took only 3 hrs but now Our backups are running very long hours most of the time 7-10hr.
We are taking the backups to remote server, which has the firewall restrictions. Previous also it is the same process (Same servers) but just now it is happening.
What could be the problem? How to identify the cause for slow backup
We are using Native compression backup no 3rd party tools
Can some one give me ideas to identify this
You take a full backup every day, but do you take any log backups throughout the day?
February 24, 2015 at 11:12 am
Yes. We have 1hr t-logs but no differentials
February 24, 2015 at 12:16 pm
ramana3327 (2/24/2015)
Hi,Currently, working with SQL backups.
I am looking for the route cause of slow backups. Our current environment is 208R2. We have a database around 450GB. Our current backup is fullbackup everyday. Previously, it took only 3 hrs but now Our backups are running very long hours most of the time 7-10hr.
We are taking the backups to remote server, which has the firewall restrictions. Previous also it is the same process (Same servers) but just now it is happening.
What could be the problem? How to identify the cause for slow backup
We are using Native compression backup no 3rd party tools
Can some one give me ideas to identify this
First, something has to have changed somewhere and you need to identify it. Is something else also writing to that location now? Did permissions change? Did a network setting get changed on the SQL Server, backup location, firewall, or switch/router?
When a backup is running I'd recommend using Adam Machanic's sp_whoisactive to determine what the backup process is waiting on and then research that wait type to determine where to look for the issue.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
February 24, 2015 at 12:58 pm
Take a look at the wait statistics on the server to see what is causing things to run slow. Or, better still, capture the waits prior to a backup and then again after the backup. The best way to troubleshoot why something within the system like backups, where your T-SQL generally won't be a positive or a negative, is to understand what it is within the system is running slow. Wait stats are the way to get that done.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
February 24, 2015 at 1:32 pm
If network speed is not the issue - Stripe your DB backup to multiple files.
February 24, 2015 at 1:56 pm
ramana3327 (2/24/2015)
Hi,Currently, working with SQL backups.
I am looking for the route cause of slow backups. Our current environment is 208R2. We have a database around 450GB. Our current backup is fullbackup everyday. Previously, it took only 3 hrs but now Our backups are running very long hours most of the time 7-10hr.
We are taking the backups to remote server, which has the firewall restrictions. Previous also it is the same process (Same servers) but just now it is happening.
What could be the problem? How to identify the cause for slow backup
We are using Native compression backup no 3rd party tools
Can some one give me ideas to identify this
In my opinion, that's pretty big to do a full backup daily. It is time to re-visit your backup procedure.
February 24, 2015 at 3:09 pm
But we have some constraints to change the full backup to diff. Some other servers needs to refresh with everyday backup but there is no communication b/n server to set up replication. So we need everyday refresh that server using this backups
February 24, 2015 at 6:29 pm
You should determine the minimum time that a backup could take to run by running a test backup to the NUL device.
This will give you the shortest possible time that a backup could run in before you involve the network and disk speed on the backup server and help determine where the bottleneck is.
BACKUP
DATABASE [MyDatabase] TO DISK = N'NUL'
WITH
COMPRESSION,
COPY_ONLY,
INIT,
NAME = N'MyDatabase - Null Database Backup to NUL device'
As other people have suggested, you should consider doing the full backups to multiple backup files. Five output backup files is a good starting point.
I have found that it is usually faster than going to a single file, even if all of the backup files are in the same folder.
February 25, 2015 at 7:55 am
You haven't discussed the disk technology that is in place on your backup server yet.
SAN, RAID, SSD?
Disk controllers in good shape and all running - no battery problems?
The other posters have set you off in a couple of good directions for investigation, but one of the main routes of inquiry will be to simply have a chat with your infrastructure engineers - has anything changed or, in the case of SSDs, have they received any maintenance recently, or just been building up?
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply