Resource ID : 1. The request limit for the database is 180 and has been reached.

How to find root cause for SQL Azure DB error -

"Resource ID : 1. The request limit for the database is 180 and has been reached."

I have ran the following two queries to find long running queries that causing connection limit issue. When you run below queries the result set 1 shows all current running query on DB with active session ids. If result set1 has non-zero values in “blocking_session_id” column, you can find same session id in 2^nd result set and see the query text. Based on query text, you can guess which job is running this query & kill/suspend the job/query to release the server resources.

select r.session_id,r.blocking_session_id,r.wait_type,r.wait_time,r.wait_resource,r.total_elapsed_time,r.cpu_time,r.reads,r.writes,

s.nt_user_name,s.program_name,s.total_elapsed_time

from sys.dm_exec_requests r

join sys.dm_exec_sessions s

on s.session_id=r.session_id

SELECT session_id, TEXT

FROM sys.dm_exec_connections

CROSS APPLY sys.dm_exec_sql_text(most_recent_sql_handle) AS ST 

Below shows the sample of blocking session Id & wait type LCK_M_U. 

 

You may consider to Kill the lock session by passing session Id -

Kill <SessionId>

Kill <SessionId> WITH STATUSONLY…If we need to do a rollback.

http://msdn.microsoft.com/en-us/library/windowsazure/jj937138.aspx 

Ref Url -

http://blogs.msdn.com/b/sqlblog/archive/2013/11/01/is-my-query-running-fine-in-the-cloud.aspx

http://blogs.msdn.com/b/sqlblog/archive/2013/11/01/do-i-need-to-upgrade-my-dba-skills-for-the-cloud.aspx