One of the capabilities of the Cotega service is the ability to schedule execution of stored procedures in Azure SQL. This is handy for many administrators because Azure SQL does not have a SQL Agent capability. One of the interesting side effects I have seen from this is that long running stored procedures can cause issues when they are scheduled to run frequently. If the previous execution of the stored procedure had not completed before the next one starts, there can be issues such as locking or even extreme cases where they pile up and eventually cause connection drops due to reaching the maximum number of request counts.
One solution that I have come up with is to add code to the start of a stored procedure that detects if it is already running. If it is, it simply returns. It was interesting to see that there was not a lot of information that I could find on the internet to detect if an Azure SQL stored procedure is running, so I decided to build one. This is what I came up with which will return a count of the number of times a specific stored procedure is currently running:
CREATE FUNCTION [dbo].[CheckStoredProcActive] (@StoredProcnvarchar(255)) RETURNS int as begin declare @spCount int; set @spCount=(select count(*) FROM sys.dm_exec_sessionss INNER JOIN sys.dm_exec_requestsr ON r.session_id=s.session_id CROSSAPPLY sys.dm_exec_sql_text(r.sql_handle) AS SQL INNER JOIN sys.objects o ON SQL.objectid=o.object_id WHERE s.is_user_process= 1 AND r.database_id=db_id() AND [email protected]); RETURN @spCount; end;
Then you can simply add the following to the start of your procedure where you replace [ENTER_STORED_PROC_NAME] with your stored procedure name:
if (dbo.CheckStoredProcActive(‘[ENTER_STORED_PROC_NAME]’)> 0) return; I hope others find this helpful. Liam