November 4, 2013 at 7:32 am
On one of our prod server, every sunday we perform full backups and few other archival processes. We use third party tool to take the backups. During this time the jobs which used to take just few minutes to run are taking 2-3 hrs time to finish. The process are going to suspended mode and waiting on resources to acquire. Also on normal weekdays we perform differential backups every 4 hrs. Sometimes the backups take too long to finish. It's not because of the size but the spid goes into suspended mode. How to fix this behaviour of the server ? Can we introduce resource governor for backup process?
November 5, 2013 at 2:18 am
Time for the backup also vary depending on the processes going on the server. There might be some temporary blocking while taking the backup which may inturn increase the time of backup complition.
As alternate solution try to find out which process is creating the problem and try to schedule the out of the backup slot if possible.
HTH.
---------------------------------------------------
"Thare are only 10 types of people in the world:
Those who understand binary, and those who don't."
November 5, 2013 at 2:53 am
USe below query to troubleshoot the resource crunch
--== Blocking queries in database ==--
SELECT DES.Session_ID AS [Root Blocking Session ID], DER.STATUS AS [Blocking Session Request Status],
DES.Login_Time AS [Blocking Session Login Time], DES.Login_Name AS [Blocking Session Login Name],
DES.Host_Name AS [Blocking Session Host Name], Coalesce(DER.Start_Time, DES.Last_Request_Start_Time) AS [Request Start Time],
CASE WHEN DES.Last_Request_End_Time >= DES.Last_Request_Start_Time THEN DES.Last_Request_End_Time ELSE NULL END AS [Request End Time],
Substring(TEXT, DER.Statement_Start_Offset / 2, CASE WHEN DER.Statement_End_Offset = - 1 THEN DataLength(TEXT) ELSE DER.Statement_End_Offset / 2 END) AS [Executing Command],
CASE WHEN DER.Session_ID IS NULL THEN 'Blocking session does not have an open request and may be due to an uncommitted transaction.'
WHEN DER.Wait_Type IS NOT NULL THEN 'Blocking session is currently experiencing a ' + DER.Wait_Type + ' wait.'
WHEN DER.STATUS = 'Runnable' THEN 'Blocking session is currently waiting for CPU time.'
WHEN DER.STATUS = 'Suspended' THEN 'Blocking session has been suspended by the scheduler.'
ELSE 'Blocking session is currently in a ' + DER.STATUS + ' status.'
END AS [Blocking Notes]
FROM Sys.DM_Exec_Sessions DES(READUNCOMMITTED)
LEFT JOIN Sys.DM_Exec_Requests DER(READUNCOMMITTED) ON DER.Session_ID = DES.Session_ID
OUTER APPLY Sys.DM_Exec_Sql_Text(DER.Sql_Handle)
WHERE DES.Session_ID IN (SELECT Blocking_Session_ID
FROM Sys.DM_Exec_Requests(READUNCOMMITTED)
WHERE Blocking_Session_ID <> 0 AND Blocking_Session_ID
NOT IN (SELECT session_id
FROM Sys.DM_Exec_Requests(READUNCOMMITTED)
WHERE Blocking_Session_ID <> 0
)
);
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply