August 5, 2011 at 8:45 am
I have SQL Server 2008R2 on a pc with bare minimum resources for temporary. I'm running a backup job but there are two waits showing in Activity Monitor. BACKUPBUFFER and ASYNC_IO_COMPLETION. It's a very big database, approximately 241Gb. I can wait while this runs, but is it really running, or is it hung up?
Other jobs have been causing PAGEIOLATCH_SH wait types. I have stopped them though. Do these waits mean the jobs are working but taking a long time, or are they just hung and need to be stopped?
I may have to put this database somewhere else, but it's only needed for a couple weeks.
Thanks very much.
August 5, 2011 at 9:00 am
What does sp_who2 show?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
August 5, 2011 at 9:10 am
These will help you debug this.
When will my backup finish?
SELECT r.[session_id]
, c.[client_net_address]
, s.[host_name]
, c.[connect_time]
, [request_start_time] = s.[last_request_start_time]
, [current_time] = CURRENT_TIMESTAMP
, r.[percent_complete]
, [estimated_finish_time] = DATEADD(MILLISECOND, r.[estimated_completion_time], CURRENT_TIMESTAMP)
, [estimated_seconds_left] = DATEDIFF(s, CURRENT_TIMESTAMP, DATEADD(MILLISECOND, r.[estimated_completion_time], CURRENT_TIMESTAMP))
, current_command = SUBSTRING ( t.[text], r.[statement_start_offset]/2, COALESCE(NULLIF(r.[statement_end_offset], -1)/2, 2147483647) )
, module = COALESCE(QUOTENAME(OBJECT_SCHEMA_NAME(t.[objectid], t.[dbid])) + '.' + QUOTENAME(OBJECT_NAME(t.[objectid], t.[dbid])), '<ad hoc>')
FROM sys.dm_exec_requests AS r
INNER JOIN sys.dm_exec_connections AS c
ON r.[session_id] = c.[session_id]
INNER JOIN sys.dm_exec_sessions AS s
ON r.[session_id] = s.[session_id]
CROSS APPLY sys.dm_exec_sql_text(r.[sql_handle]) AS t
WHERE r.[percent_complete] <> 0;
Blocking chain from Activity Monitor.
--Borrowed from the activity monitor
SELECT
[Session ID] = s.session_id,
[User Process] = CONVERT(CHAR(1), s.is_user_process),
[Login] = s.login_name,
[Database] = ISNULL(db_name(p.dbid), N''),
[Task State] = ISNULL(t.task_state, N''),
[Command] = ISNULL(r.command, N''),
[Application] = ISNULL(s.program_name, N''),
[Wait Time (ms)] = ISNULL(w.wait_duration_ms, 0),
[Wait Type] = ISNULL(w.wait_type, N''),
[Wait Resource] = ISNULL(w.resource_description, N''),
[Blocked By] = ISNULL(CONVERT (varchar, w.blocking_session_id), ''),
[Head Blocker] =
CASE
-- session has an active request, is blocked, but is blocking others or session is idle but has an open tran and is blocking others
WHEN r2.session_id IS NOT NULL AND (r.blocking_session_id = 0 OR r.session_id IS NULL) THEN '1'
-- session is either not blocking someone, or is blocking someone but is blocked by another party
ELSE ''
END,
[Total CPU (ms)] = s.cpu_time,
[Total Physical I/O (MB)] = (s.reads + s.writes) * 8 / 1024,
[Memory Use (KB)] = s.memory_usage * 8192 / 1024,
[Open Transactions] = ISNULL(r.open_transaction_count,0),
[Login Time] = s.login_time,
[Last Request Start Time] = s.last_request_start_time,
[Host Name] = ISNULL(s.host_name, N''),
[Net Address] = ISNULL(c.client_net_address, N''),
[Execution Context ID] = ISNULL(t.exec_context_id, 0),
[Request ID] = ISNULL(r.request_id, 0)
-- [Workload Group] = ISNULL(g.name, N'')
FROM sys.dm_exec_sessions s LEFT OUTER JOIN sys.dm_exec_connections c ON (s.session_id = c.session_id)
LEFT OUTER JOIN sys.dm_exec_requests r ON (s.session_id = r.session_id)
LEFT OUTER JOIN sys.dm_os_tasks t ON (r.session_id = t.session_id AND r.request_id = t.request_id)
LEFT OUTER JOIN
(
-- In some cases (e.g. parallel queries, also waiting for a worker), one thread can be flagged as
-- waiting for several different threads. This will cause that thread to show up in multiple rows
-- in our grid, which we don't want. Use ROW_NUMBER to select the longest wait for each thread,
-- and use it as representative of the other wait relationships this thread is involved in.
SELECT *, ROW_NUMBER() OVER (PARTITION BY waiting_task_address ORDER BY wait_duration_ms DESC) AS row_num
FROM sys.dm_os_waiting_tasks
) w ON (t.task_address = w.waiting_task_address) AND w.row_num = 1
LEFT OUTER JOIN sys.dm_exec_requests r2 ON (s.session_id = r2.blocking_session_id)
--LEFT OUTER JOIN sys.dm_resource_governor_workload_groups g ON (g.group_id = s.group_id)--TAKE THIS dmv OUT TO WORK IN 2005
LEFT OUTER JOIN sys.sysprocesses p ON (s.session_id = p.spid)
ORDER BY s.session_id;
August 5, 2011 at 9:17 am
From sp_who2:
Status Dbname Command CPUTime DiskIO LastBAtch
SUSPENDED par_fy2011 BACKUPDATABASE 298 123 08/05 09:17:55
RUNNABLE par_fy2011 BACKUPDATABASE 5656 0 08/05 09:17:55
SUSPENDED par_fy2011 BACKUPDATABASE 11578 79907 08/05 09:17:55
Is this enough, Anything else to help troubleshoot this? Thanks.
August 5, 2011 at 9:19 am
Denise McMillan (8/5/2011)
From sp_who2:Status Dbname Command CPUTime DiskIO LastBAtch
SUSPENDED par_fy2011 BACKUPDATABASE 298 123 08/05 09:17:55
RUNNABLE par_fy2011 BACKUPDATABASE 5656 0 08/05 09:17:55
SUSPENDED par_fy2011 BACKUPDATABASE 11578 79907 08/05 09:17:55
Is this enough, Anything else to help troubleshoot this? Thanks.
Seems to be running, but only 80K diskIO seems low for a backup. What did my queries return?
August 5, 2011 at 9:53 am
Results from the first query show the backup is 43.73% done and est finish time of 14:43, the % done has been changing.
From the second query sent in attachment called backup_query_results.txt. (Hope that worked)
August 5, 2011 at 10:01 am
Taking a stab at it but I'm out of my confort zone.
Both of those mean waiting on disks. Suggested to check with perfmon to find the exact issue.
BACKUPBUFFER seems to be ONLY linked to waiting on tape drive to be mounted. Maybe that can ring a bell for you.
I got that info from the official MS whitepaper here : http://download.microsoft.com/download/4/7/a/47a548b9-249e-484c-abd7-29f31282b04d/Performance_Tuning_Waits_Queues.doc
August 8, 2011 at 9:08 am
I have a question about the query you sent to check if the backup was running. It worked great, I could see its' progress and knowing it was actually working I had the confidence to let it run. I had been worried that it was just hung up. Thanks very much for it!
Can this query be used to monitor any long running query?
Thanks again.
August 8, 2011 at 9:10 am
Denise McMillan (8/8/2011)
I have a question about the query you sent to check if the backup was running. It worked great, I could see its' progress and knowing it was actually working I had the confidence to let it run. I had been worried that it was just hung up. Thanks very much for it!Can this query be used to monitor any long running query?
Thanks again.
Works only for a few specific process like backup, restore, checkdb. Maybe a few others.
For long running queries >
And free book on how to tune queries (don't confuse free with useless here ;-))
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply