locked
stored procedure takes forever to run!!! RRS feed

  • Question

  • Following is a stored procedure to reads the error log file and sends out an email, if there is any deadlock,error or any other informational message.

    In the job, we configured it as

    step 1: Execute spdeadlockerrorlog 0,1,'error','error'

    step 2: Execute spdeadlockerrorlog 0,1,'dead','deadlock'

    step3 Execute spdeadlockerrorlog 0,1,'information','informational message'

    It runs well but sometimes it keeps running forever, if I happen to cancel the job, the  server does not respond and had to manually stop and start the sql server service. As I said, it works well, only sometimes it does not run. I added some print statements inside the procedure to see where it is erroring out and tired to run all three statements at one go. the procedure was running forever and it did not print any print statements, whic  makes me think that the procedure is not doing anything.

    ALTER Procedure [dbo].[SPDeadlockErrorLog] (@lognum int,@logtype int,@searchstring varchar(255),@ErrorType nvarchar(40))

    as

    Begin Try

    print 1

    declare @date1 datetime,@date2 datetime

    select  @date1 =MAX(logdate) from test.dbo.errorlog where ErrorType=@ErrorType

    select @date2=GETDATE()

    select @date1=DATEADD(MIlliSECOND,2,@date1)

    print 2

    INSERT INTO test.dbo.ErrorLog(logdate,Procinfo,ERRORLOG)

    EXEC master.dbo.xp_readerrorlog @lognum,@logtype,@searchstring,NULL,@date1, @date2,N'desc'

    Update test.dbo.ErrorLog set ErrorType = @ErrorType where ErrorType is null

    print 3

    if (Select COUNT(procinfo) from test.dbo.ErrorLog where Emailflag =0 and ErrorType = @ErrorType) >  0

    begin 

    declare @servername nvarchar(150)

    set @servername = @@servername 

    declare @mysubject varchar(200)

    declare @mybody varchar(200)

    declare @myquery varchar(1000)

    set @mysubject =  @ErrorType+' event notification on server '+@servername+'.'

    set @mybody= @ErrorType+' has occured.View attachment to see the ' +@ErrorType+' info'

    set @myquery = 'set nocount on;

    select logdate, procInfo, ERRORLOG from test.dbo.ErrorLog where Emailflag =0 and ErrorType = ''' + @ErrorType+''' ;

    update test.dbo.ErrorLog set Emailflag=1'

    EXEC msdb.dbo.sp_send_dbmail @recipients='test@test.com', 

    @subject = @mysubject,

    @body = @mybody,

    @query = @myquery,

    @query_result_width = 600,

    @attach_query_result_as_file = 1

    end

    End TRY

    Begin Catch

    SELECT

            ERROR_NUMBER() AS ErrorNumber,ERROR_SEVERITY() AS ErrorSeverity,ERROR_STATE() as ErrorState,

            ERROR_PROCEDURE() as ErrorProcedure, ERROR_LINE() as ErrorLine,ERROR_MESSAGE() as ErrorMessage;

    END Catch

     Any help is greatly appreciated. Thanks,

     

     

     


    Please mark as 'Answer', if the solution solves your problem.

    Monday, June 13, 2011 1:45 AM

Answers

  • Here's something you can do to better troubleshoot. The output of PRINT statements are batched and don't display until a threshold (1KByte?) is reached. So your stored procedure could have already completed several statements, but since the output of the PRINT statements combined still don't reach the threshold, nothing is displayed. Instead, replace your print statements with something like:

    RAISERROR('1', 10, 1) WITH NOWAIT;
    
    
    HTH


    Vern Rabe
    • Proposed as answer by Naomi N Monday, June 13, 2011 3:10 AM
    • Marked as answer by Stan210 Tuesday, June 14, 2011 3:53 PM
    Monday, June 13, 2011 2:51 AM
  •  The tip Vern Rable gave helped us to trouble shoot the issue and after some more search, we found out that this is an issue in executing xp_readerrorlog and microsoft is aware of the situation and released a hotfix for this http://support.microsoft.com/kb/973524.But whats interesting is that, it happens randomly, I installed the fix and did not have an issue.so far good.

     


    Please mark as 'Answer', if the solution solves your problem.
    • Marked as answer by Stan210 Tuesday, June 14, 2011 3:53 PM
    Tuesday, June 14, 2011 3:52 PM

All replies

  • Here's something you can do to better troubleshoot. The output of PRINT statements are batched and don't display until a threshold (1KByte?) is reached. So your stored procedure could have already completed several statements, but since the output of the PRINT statements combined still don't reach the threshold, nothing is displayed. Instead, replace your print statements with something like:

    RAISERROR('1', 10, 1) WITH NOWAIT;
    
    
    HTH


    Vern Rabe
    • Proposed as answer by Naomi N Monday, June 13, 2011 3:10 AM
    • Marked as answer by Stan210 Tuesday, June 14, 2011 3:53 PM
    Monday, June 13, 2011 2:51 AM
  • Can you capture block/lock info while procedure runs forever? Waits .....

    I would use Adam's utility http://sqlblog.com/blogs/adam_machanic/archive/2011/04/30/twenty-nine-days-of-activity-monitoring-a-month-of-activity-monitoring-part-30-of-30.aspx


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Monday, June 13, 2011 5:12 AM
    Answerer
  • hi Uri Dimant,

    wondering if this could be the cause http://support.microsoft.com/kb/973524 ..but it is working fine sometimes.. i am downloading the fix and will try 


    Please mark as 'Answer', if the solution solves your problem.
    Monday, June 13, 2011 5:28 AM
  • Try to allocate SP parameters to local parameters and then use it. If print command is giving a problem then try to use "SELECT"
    -ankur Please mark post as answered as applicable
    Monday, June 13, 2011 7:18 AM
  •  The tip Vern Rable gave helped us to trouble shoot the issue and after some more search, we found out that this is an issue in executing xp_readerrorlog and microsoft is aware of the situation and released a hotfix for this http://support.microsoft.com/kb/973524.But whats interesting is that, it happens randomly, I installed the fix and did not have an issue.so far good.

     


    Please mark as 'Answer', if the solution solves your problem.
    • Marked as answer by Stan210 Tuesday, June 14, 2011 3:53 PM
    Tuesday, June 14, 2011 3:52 PM