sys.dm_exec_requests is blocked

Question

Hi,

I have a server where sys.dm_exec_requests is blocked (i.e. when I do a SELECT from sys.dm_exec_requests, it never returns). The same applies to sp_who, sp_who2. 

1) Do you know why ? (Note that I don't have permissions to create a dump so this isn't the answer I'm looking for.)

2) Is it possible to resolve it ? (Of course without the restart)

Many thanks

Answer 1

Would you please check-out below thread

http://social.msdn.microsoft.com/Forums/en-US/sqldatabaseengine/thread/1eee5121-a02a-42e2-9f96-872a7e69a921

---

Regards,
Ahmed Ibrahim
SQL Server Setup Team
My Blog
This posting is provided "AS IS" with no warranties, and confers no rights. Please remember to click "Mark as Answer" and "Vote as Helpful" on posts that help you.
This can be beneficial to other community members reading the thread.

Answer 2

Hi Ahmed,

Thank you. Unfortunately I have the required permissions.

Answer 3

what is the error mesaage you are getting ?

---

Rama Udaya.K ramaudaya.blogspot.com ---------------------------------------- Please remember to mark the replies as answers if they help and un-mark them if they provide no help.

Answer 4

Hi Rama,

I don't get any error message. I kept the query run for several hours and it just runs without any error message - it apparently is blocked. (I aborted it then.)

(I don't have permissions to check error logs.)

Thank you

Answer 5

Can you show us the entire statement?

select session_id, Text
from sys.dm_exec_requests r
cross apply sys.dm_exec_sql_text(sql_handle) t ?????????

---

Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

MS SQL optimization: MS SQL Development and Optimization
MS SQL Blog: Large scale of database and data cleansing
MS SQL Consultants: Improves MS SQL Database Performance

Answer 6

Thanks MD,

Might be u have to look activity monitor to see what it blocked or any issues incase if you are not able to get from sp_who2...!

other useful information-see below

http://blogs.msdn.com/b/mangeshd/archive/2009/10/02/sp-who2-or-sysprocesses-might-shows-your-thread-being-blocked-by-spid-4.aspx
http://sqlblog.com/blogs/aaron_bertrand/archive/2008/07/01/sys-dm-exec-requests.aspx

---

Rama Udaya.K ramaudaya.blogspot.com ---------------------------------------- Please remember to mark the replies as answers if they help and un-mark them if they provide no help.

Answer 7

Hi Rama,

Thank you for trying to help. The thing is that sys.dm_exec_requests, sp_who and sp_who2 are blocked. So I can't troubleshoot blocking because these SP's, DMV's are used to troubleshoot blocking. What a catch situation. :-O (Activity monitor also uses these things so it can't be used to resolve this.)

(Even if I trace locks acquired, etc., it doesn't show me anything useful to find out what's going on.)

sys.dm_exec_requests is pretty low level and it shouldn't block so I don't quite understand why it's blocked.

Thank you

Answer 8

Hi Uri,

When it didn't return, I  simplified it to

select * from sys.dm_exec_requests;

but still the same

I even tried select count(*) from sys.dm_exec_requests;

Thank you

Answer 9

I have a server where sys.dm_exec_requests is blocked (i.e. when I do a SELECT from sys.dm_exec_requests, it never returns). The same applies to sp_who, sp_who2.

Very strange.  Do you see anything unusual in the SQL Server error log (e.g. exception/dump messages)?

---

Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/

Answer 10

Hi Dan,

I don't have access to the error log. (It's a shared dev. SQL Server and I'm only a user with VIEW SERVER STATE.) In last years it happened a couple of times on that server. I ruled out also insufficient number of workers because I can run other queries but sys.dm_exec_requests is just blocked.

Thank you

Answer 11

I think we'll need more info to troubleshoot this further.  Can someone with sysadmin permissions see if the errorlog has more info?

---

Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/

Answer 12

Hi Dan,

I've asked our DBA's and they don't want to check the error log :-(. (The company has rigid rules ... )

Thanks

Answer 13

I've asked our DBA's and they don't want to check the error log :-(. (The company has rigid rules ... )

I can understand the DBAs might not be able to provide the entire error log but they need at least help with troubleshooting your problem.  Are you able to engage them for assistance with this issue?  I'm afraid there's not much we can do without their involvement.

---

Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/

Answer 14

They don't want to troubleshoot it or do anything with it - I've tried ... . It's a dev. server so they don't care. Let me not comment why but if I want to be positive, I'd say they're busy with production servers ... .

Answer 15

They don't want to troubleshoot it or do anything with it - I've tried ... . It's a dev. server so they don't care. Let me not comment why but if I want to be positive, I'd say they're busy with production servers ... .

If it's a dev box and they don't have the bandwidth to help you, I don't undertand their concerns with providing you the error log so that you might be at least able to help yourself.  Unfortunately, we can't help with organizational disfunctionality in this forum :-(

---

Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/

Answer 16

Thank you all for trying to help. Unfortunately I can't get more details. But if you ever encountered this situation I'd gladly hear what was the issue / how you resolved it.

(I've seen it happening only on one server. It happened a few times in the last year.)