SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Maintinace Backup job taking longer than expected..


Maintinace Backup job taking longer than expected..

Author
Message
ajay.bhardwaj13
ajay.bhardwaj13
Valued Member
Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)

Group: General Forum Members
Points: 57 Visits: 181
Hi,

In one of my production envirnment Maintinace Backup job is taking longer than expected.
I have maintinace plan to take the full backup on weekly baisis.Datasize is around 3.5TB (174 DBs).
Previousaly This job used to take around 10-11 hours and now its taking more than 24 hrous.

Below is the configuration.
SQL Server 2005 EE + SP4,
Windows Server 2003
SAN disk on 3PAR

I checked the disk latency and that is also below 10ms.We have seprate disk for backup.
Could you Please guide me to find the root cause of this issue.Its very urgent..


Regards
Ajay Bhardwaj
ajay.bhardwaj13
ajay.bhardwaj13
Valued Member
Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)

Group: General Forum Members
Points: 57 Visits: 181
Does any one have any idea about this?
Andrew G
Andrew G
SSCrazy
SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)

Group: General Forum Members
Points: 2475 Visits: 2254
Do you know how long each database backup is taking? Has the time increased for a single backup or all of them?

Apart from the slowdown issue, with 3.5TB of data, it's time to stop using maintenance plans and move to a custom scripted backup solution.
There is a commonly used solution here (I have not used it personally but it's widely used elsewhere) http://ola.hallengren.com/

That will at least give you some better logging. I'm not sure of your level of experience, but it sounds like you'll need to research more about backup performance tuning. What is the best performance you can achieve by backing up to a NUL device? There are switches like BLOCKSIZE and BUFFERCOUNTS which can make a huge difference in backup times and throughput. Actually what throughput are you achieving to the SAN in MB/s or IOPS?

You'll need to investigate the wait activity occuring at the time of the backup too. You can use this code, but remember you will lose all the wait statistics when running the DBCC clear.
-- Clear Wait Stats
DBCC SQLPERF('sys.dm_os_wait_stats', CLEAR);

-- Isolate top waits for server instance since last restart or statistics clear
WITH Waits AS
(SELECT wait_type, wait_time_ms / 1000. AS wait_time_s,
100. * wait_time_ms / SUM(wait_time_ms) OVER() AS pct,
ROW_NUMBER() OVER(ORDER BY wait_time_ms DESC) AS rn
FROM sys.dm_os_wait_stats
WHERE wait_type NOT IN ('CLR_SEMAPHORE','LAZYWRITER_SLEEP','RESOURCE_QUEUE','SLEEP_TASK'
,'SLEEP_SYSTEMTASK','SQLTRACE_BUFFER_FLUSH','WAITFOR', 'LOGMGR_QUEUE','CHECKPOINT_QUEUE'
,'REQUEST_FOR_DEADLOCK_SEARCH','XE_TIMER_EVENT','BROKER_TO_FLUSH','BROKER_TASK_STOP','CLR_MANUAL_EVENT'
,'CLR_AUTO_EVENT','DISPATCHER_QUEUE_SEMAPHORE', 'FT_IFTS_SCHEDULER_IDLE_WAIT'
,'XE_DISPATCHER_WAIT', 'XE_DISPATCHER_JOIN', 'SQLTRACE_INCREMENTAL_FLUSH_SLEEP'))
SELECT W1.wait_type,
CAST(W1.wait_time_s AS DECIMAL(12, 2)) AS wait_time_s,
CAST(W1.pct AS DECIMAL(12, 2)) AS pct,
CAST(SUM(W2.pct) AS DECIMAL(12, 2)) AS running_pct
FROM Waits AS W1
INNER JOIN Waits AS W2
ON W2.rn <= W1.rn
GROUP BY W1.rn, W1.wait_type, W1.wait_time_s, W1.pct
HAVING SUM(W2.pct) - W1.pct < 99; -- percentage threshold


djyoungberg
djyoungberg
Grasshopper
Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)

Group: General Forum Members
Points: 10 Visits: 156
If you have a budget for it, you should consider using a product like Redgate SQL Backup. It compresses your backup file as it writes it to disk. This has helped us to greatly reduce the time it takes to create a backup, and to restore it. It also reduces the space needed to store it, which has a lot of other benefits.
ajay.bhardwaj13
ajay.bhardwaj13
Valued Member
Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)

Group: General Forum Members
Points: 57 Visits: 181
Thanks for your response and sorry for late response.

@foxxo

I investiagtae futher and there is no pattern for backup dealy.While investigating on this i found that backup set verification is taking longer than backup task but always not the same dbs.

I have also think about the solutions like BLOCKSIZE ,BUFFERCOUNTS and temporay change in MAXDOP during backup options.

But its a production box and before provideing any action plan i need to provide the root cause for any issue then pros and cons for solutions also.

As well as i also gone through the PSSDiag blocking log and no process is blocking the backup and verifiaction task.

So first I am more insterseted in Root cause

@Djyoungberg :- As of now my company is not looking for any tool.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search