Dave's Techytips

Error in SQL Server Jobs: SQL Server blocked access to procedure 'dbo.sp_get_sqlagent_properties' of component 'Agent XPs' because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of 'Agent XPs' by using sp_configure. For more information about enabling 'Agent XPs', see "Surface Area Configuration" in SQL Server Books Online. Resolve by: sp_configure 'show advanced options', 1; GO; RECONFIGURE; GO; sp_configure 'Agent XPs', 0; GO; RECONFIGURE; GO;

I recently made a small error in a SQL Server parsing function that caused an endless loop. Unfortunately SQL doesn't escalate an error and will allow these transactions to go on endlessly. I quickly realized the error and attempted to correct the SQL function. However since there were users stuck in this endless loop, my ALTER FUNCTION transaction was being blocked. KILLing individual SPIDs didn't work since I could not keep up with the number of backup of user requests. To circumvent this, I ran a process to KILL all queries blocking my SPID: declare @bCont bit, @i int, @s varchar(100) set @bCont = 1 while @bCont = 1 Begin select @i = blocked from master..sysprocesses where spid = 113 if isnull(@i , 0) 0 BEGIN set @bCont = 1 print @s SET @s = 'kill ' + convert(varchar(12), @i) exec (@s) waitfor delay '00:00:00.05' END else SET @bcont = 0 end