January 30, 2009 at 1:15 am
I am executing a stored proc (NOT through a job).
This SP does massive updates and inserts and usually executes for about 45 min.
But it has now been executing for 1.5 hours.
How can I tell/determine if this SP is still executing or if it is hanging?
I know it is not being deadlocked - cause I executed sp_who2 and it shows no blocks. Is there anything in the result set of sp_who2 that can tell me if the SP is still executing properly.
Any help 🙂
January 30, 2009 at 1:35 am
U can check the activity monitory in SSMS. Other than that you have DMV's like SYS.DM_EXEC_SESSIONS, SYS.DM_EXEC_REQUESTS to see what is being executed inside SQL .
"Keep Trying"
January 30, 2009 at 2:44 am
Why dont you run Profiler against the troubled server?
Check the events:
TextData
ApplicationName
HostName
LoginName
EndTime
SPID
CPU
Duration
Reads
Writes
January 30, 2009 at 4:04 am
Krishna (1/30/2009)
Why dont you run Profiler against the troubled server?Check the events:
TextData
ApplicationName
HostName
LoginName
EndTime
SPID
CPU
Duration
Reads
Writes
Profiler records when a proc, query or statement starts and when a proc, query or statement ends. It doesn't record time remaining in any capacity.
sys.dm_exec_requests does have a percent_complete column that, in some cases, will show the status of a given query. You can try that.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
January 30, 2009 at 5:27 am
Well it's been over 5 hours now AND STILL running 🙁
Thanks for all your replies but I was now thinking: As mentioned it does UPDATES and INSERTS to tables. What will happen if I kill this process? Will any updates and inserts made, be rolled back? The SP does not have a specific BEGIN TRAN or COMMIT statement - just updates and inserts.
Is it safe to stop this?
January 30, 2009 at 5:41 am
Casper (1/30/2009)
Well it's been over 5 hours now AND STILL running 🙁Thanks for all your replies but I was now thinking: As mentioned it does UPDATES and INSERTS to tables. What will happen if I kill this process? Will any updates and inserts made, be rolled back? The SP does not have a specific BEGIN TRAN or COMMIT statement - just updates and inserts.
Is it safe to stop this?
If you kill it, it will rollback, but it may take as long to roll back, or even longer, as it took to run.
If you run sp_who2, is the process blocked by another one?
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
January 30, 2009 at 5:47 am
No the process is not being blocked. Here are some column values for that process for sp_who2.
Status, Command, CPUTime, DiskIO, LastBatch
Runnable, UPDATE, 17775156, 9463, 01/30 08:45:09
The COMMAND values changes from UPDATE to SELECT to EXECUTE to CONDITIONAL.
The DiskIO value keeps increasing (very slowly)
The LastBatch date is when i started the execution (this morning)
January 30, 2009 at 5:55 am
Then, whatever it's doing, it's plodding along. You can kill it, but you might be waiting for a while for the rollback to clear.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply