Backup taking too much time
-
Hi Experts,
One of my Large database having about 4TB in size is taking almost 3 days to complete the backup . To reduce the backup I stripped the same to 4 different cluster drives and still its taking the same time. The data stored is all documents and it uses filestream.
Below are the waittypes observed.
lastwaittype
BACKUPTHREAD
BACKUPBUFFER
BACKUPBUFFER
BACKUPBUFFER
BACKUPBUFFER
PREEMPTIVE_OS_FINDFILE
MEMORY_ALLOCATION_EXT
Initant file initiatization is enabled, backup is taking using ola script
sqlcmd -E -S $(ESCAPE_SQUOTE(SRVR)) -d ADMIN -Q "EXECUTE [dbo].[DatabaseBackup] @databases = N'DocDB',@Directory = N'N:\Backup,O:\Backup,U:\Backup,V:\Backup',@BufferCount = 100,@MaxTransferSize = 2097152,@BackupType = 'FULL', @verify = 'Y',@CleanupTime = 24, @checksum = 'Y',@Compress = 'Y', @LogToTable = 'Y'" -b
Can anyone shed some light to resolve this?
- This topic was modified 1 month ago by VastSQL.
-
It sounds like you need to get your network, SAN, disks tested & fixed. Your network and/or disk I/O sound abysmal.
4TB isn't very big. I have 4-14TB backups completing in minutes on AWS EBS.
-
If those backup drives are on the same system as your SQL Server, you need to move them to a different machine, ASAP.
Also, why are you using SQLCMD to do the backups instead of using SQL Agent?
Also... make sure that even if the backup drives are on a different system, make sure that different system on still on the same domain if all this is happening "on-prem".
If any of the drives involved are "spinning rust", that would explain a whole lot.
How many NIC cards does your server and the backup system have (again, please tell us that that backups are on a different physical system).
Do you have anti-virus code running on either system? If so, what is it and which EXclusions have you used?
Is your backup system being used for anything else?
Has it always been this way (slow)?
And those are just starter questions.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Thank you Jeff, Please find my inline response below.
Jeff Moden wrote:If those backup drives are on the same system as your SQL Server, you need to move them to a different machine, ASAP.: These are cluster disks on same server. You mean to backup to a network share?
Also, why are you using SQLCMD to do the backups instead of using SQL Agent?: The same will be executed as SQL Agent job , this is Ola's maintenance scripts.
Also... make sure that even if the backup drives are on a different system, make sure that different system on still on the same domain if all this is happening "on-prem".
If any of the drives involved are "spinning rust", that would explain a whole lot.
How many NIC cards does your server and the backup system have (again, please tell us that that backups are on a different physical system).One Nic,how can we specify the NIC if we hav multiple or will it automatically use all available?.
Do you have anti-virus code running on either system? If so, what is it and which EXclusions have you used? Yes I have added Defender exclusion and stopped another AV, this speed up the things and now backup is completing 3 hours.
Is your backup system being used for anything else? Its Native SQL backup.
Has it always been this way (slow)?
And those are just starter questions.
-
4 TB SQL backup may take a long time
Have you looked at backup snapshot technologies? Like Veeam / SAN ...
- Jo Pattyn wrote:
4 TB SQL backup may take a long time
Have you looked at backup snapshot technologies? Like Veeam / SAN ...
We have Avamar here and we are testing it out? As per their admin , it may have monthly maintenance windows of about 6 hours and I am afraid it might impact my RPO.
- VastSQL wrote:
Hi Experts,
One of my Large database having about 4TB in size is taking almost 3 days to complete the backup . To reduce the backup I stripped the same to 4 different cluster drives and still its taking the same time. The data stored is all documents and it uses filestream.
Below are the waittypes observed.
lastwaittype BACKUPTHREAD BACKUPBUFFER BACKUPBUFFER BACKUPBUFFER BACKUPBUFFER PREEMPTIVE_OS_FINDFILE MEMORY_ALLOCATION_EXT
Initant file initiatization is enabled, backup is taking using ola script
sqlcmd -E -S $(ESCAPE_SQUOTE(SRVR)) -d ADMIN -Q "EXECUTE [dbo].[DatabaseBackup] @databases = N'DocDB',@Directory = N'N:\Backup,O:\Backup,U:\Backup,V:\Backup',@BufferCount = 100,@MaxTransferSize = 2097152,@BackupType = 'FULL', @verify = 'Y',@CleanupTime = 24, @checksum = 'Y',@Compress = 'Y', @LogToTable = 'Y'" -b
Can anyone shed some light to resolve this?
just out of interest, how long does the backup take with verify set to no
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs"
-
From your command it looks like you are using SQL native backup. There are other backup products out there (eg SQL Litespeed) that have as part of their USP the ability to do a backup faster than native backup.
It may be worth checking these out, to see if the reduction in backup time is worth the cost of the product. Litespeed integrates well with Oola's toolset, along with a couple of others.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
- EdVassie wrote:
From your command it looks like you are using SQL native backup. There are other backup products out there (eg SQL Litespeed) that have as part of their USP the ability to do a backup faster than native backup.
It may be worth checking these out, to see if the reduction in backup time is worth the cost of the product. Litespeed integrates well with Oola's toolset, along with a couple of others.
If they change the format of the data in the file for a backup that's faster than a "Native Backup", make sure you have a copy of their software squirreled away in a safe place in case the build gets blown off the face of the planet. AND, make sure you test it (as with native backups) for restores as often as you can.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
My inline responses are in Bold'n'Black print below....
VastSQL wrote:Thank you Jeff, Please find my inline response below.
Jeff Moden wrote:If those backup drives are on the same system as your SQL Server, you need to move them to a different machine, ASAP.: These are cluster disks on same server. You mean to backup to a network share?
Yes. Backup to a network share in a different rack preferably in a different building and make a copy to a DR site in a different city (at least).
Also, why are you using SQLCMD to do the backups instead of using SQL Agent?: The same will be executed as SQL Agent job , this is Ola's maintenance scripts.
Ola's Scripts do NOT require SQLCMD so, again I ask, why are you using SQLCMD?.
Also... make sure that even if the backup drives are on a different system, make sure that different system on still on the same domain if all this is happening "on-prem".
If any of the drives involved are "spinning rust", that would explain a whole lot.
How many NIC cards does your server and the backup system have (again, please tell us that that backups are on a different physical system).One Nic,how can we specify the NIC if we hav multiple or will it automatically use all available?.
I'm no longer a good example of a knowledgeable hardware guy when it comes to those kinds of details. I DO know the we have at least 2 NICs in our production server. There may be a way to "dedicate a connection" to the backup system but I don't know it. Again, two network cards really does help from what I've seen (long ago, now).
Do you have anti-virus code running on either system? If so, what is it and which EXclusions have you used? Yes I have added Defender exclusion and stopped another AV, this speed up the things and now backup is completing 3 hours.
Sounds good there. Again, I'm not an SME there so you might want to double check there.
Is your backup system being used for anything else? Its Native SQL backup.
Understood but that wasn't the question. I've seen people use the "backup system" by adding other software to it like using it for a fax and print server. You want something super secure and dedicated to doing just one thing... backup files. Period.
Has it always been this way (slow)?
The reason why I asked this question is because, way to often, people had a decent speed for backups and then they slowed down a lot. This can be because someone decided to change the domain they were on or add some other software to the system or the "cache battery" failed (which drags down the system even when powered up from the correct source), or other reasons.
And those are just starter questions.
Also, to answer one of your other questions, Verify and Cjecksum DO take a bit of extra time. IMHO, they've very well worth it but turn those off for a day and compare the times. If they're sudden two or three times faster due to the temporary turnoff, there may be a bigger issue that we don't have enough information to speculate on here.
You might also want to get a copy of "Crystal Disk Mark" (it's free) and check your disk speeds both at the source and at the destination. Then, have them check your network speed between the two boxes.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply