Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Maintinace Backup job taking longer than expected.. Expand / Collapse
Author
Message
Posted Thursday, March 14, 2013 10:40 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, October 27, 2014 9:43 AM
Points: 19, 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

Post #1431112
Posted Thursday, March 14, 2013 6:58 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, October 27, 2014 9:43 AM
Points: 19, Visits: 181
Does any one have any idea about this?
Post #1431316
Posted Friday, March 15, 2013 1:48 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, November 18, 2014 6:44 PM
Points: 1,280, Visits: 1,760
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

Post #1431390
Posted Saturday, March 23, 2013 9:58 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, November 13, 2014 9:15 AM
Points: 6, Visits: 145
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.
Post #1434660
Posted Wednesday, April 3, 2013 2:37 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, October 27, 2014 9:43 AM
Points: 19, 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.
Post #1438575
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse