Results 1 to 7 of 7
  1. #1
    Join Date
    Feb 2004
    Location
    HONG KONG
    Posts
    4

    Unhappy Unanswered: SP Timeout Problem

    I got a funny SP timeout problem recently. I cannot find the solution. Please help.

    I have a SQL 2000 Server running for 2 years and I didn't install any patches recently. However, about 2 months ago, 1 of the sp used by the IIS (ASP and ISAPI) has a timeout problem. The timeout problem is so funny that only occurs for 1 or 2 users (timeout), all other users can access the web server normally. I have to run sp_recompile for this sp to solve the problem.
    After the SP has been recompiled, same problem will occur suddenly for different Stored Procedures. I cannot find the reason for this........ Please help.

  2. #2
    Join Date
    Feb 2004
    Location
    india
    Posts
    67

    Re: SP Timeout Problem

    I think this could be a case of orphanated sessions or processes

    Iam not sure whether u r restarting the services.
    try restarting services.this should kill orphanated sessions

  3. #3
    Join Date
    Feb 2004
    Location
    HONG KONG
    Posts
    4

    Re: SP Timeout Problem

    Originally posted by vrsrinivas
    I think this could be a case of orphanated sessions or processes

    Iam not sure whether u r restarting the services.
    try restarting services.this should kill orphanated sessions
    I checked the processes when the problem occurs. It doesn't have any orphanated sessions. Also, at the same time, when I use Query Anaylzer and run the same SP with same arguments, it works. I has restarted the application at that time also but it doesn't help. I use profiler to monitor it, it only shows timeout and the CPU time of the SQL is low. All other users running the same SP with different arguments are working too. Any idea? Thanks.

  4. #4
    Join Date
    Feb 2004
    Location
    india
    Posts
    67

    Re: SP Timeout Problem

    Check whether duplicate stored procedures are there?

    select a.name,a.uid from sysobjects a,sysobjects b
    where a.name=b.name and a.uid<>b.uid

  5. #5
    Join Date
    Feb 2004
    Location
    HONG KONG
    Posts
    4

    Re: SP Timeout Problem

    Originally posted by vrsrinivas
    Check whether duplicate stored procedures are there?

    select a.name,a.uid from sysobjects a,sysobjects b
    where a.name=b.name and a.uid<>b.uid
    I just checked. It doesn't have any duplicate stored procedures. Thanks.

  6. #6
    Join Date
    Feb 2004
    Location
    india
    Posts
    67

    Re: SP Timeout Problem

    try this script and tell me the output

    DECLARE @MV_PROCESS CHAR(10), @MV_COMMAND2 CHAR(254), @MV_BLK CHAR(10), @MV_OID INT
    SET NOCOUNT ON
    PRINT 'LOCK PROFILE'
    DECLARE CUR_TABLE_LIST CURSOR FOR
    SELECT P.SPID, P.BLOCKED
    FROM MASTER..SYSPROCESSES P
    WHERE P.BLOCKED <> 0
    OPEN CUR_TABLE_LIST
    FETCH NEXT FROM CUR_TABLE_LIST INTO @MV_PROCESS, @MV_BLK
    WHILE (@@FETCH_STATUS <> -1)
    BEGIN
    IF (@@FETCH_STATUS <> -2)
    BEGIN

    PRINT 'PROCESS BLOCKED: ' + @MV_PROCESS + ' BY PROCESS: ' + @MV_BLK
    DECLARE LOCK_TABLE_LIST CURSOR FOR
    SELECT RSC_OBJID
    FROM MASTER.DBO.SYSLOCKINFO
    WHERE MASTER.DBO.SYSLOCKINFO.REQ_STATUS = 3
    AND REQ_SPID = @MV_PROCESS
    OPEN LOCK_TABLE_LIST
    FETCH NEXT FROM LOCK_TABLE_LIST INTO @MV_OID
    WHILE (@@FETCH_STATUS <> -1)
    BEGIN
    IF (@@FETCH_STATUS <> -2)
    BEGIN
    SELECT NAME AS 'TABLE(S) CAUSING BLOCK' FROM SYSOBJECTS WHERE ID = @MV_OID
    END
    FETCH NEXT FROM LOCK_TABLE_LIST INTO @MV_OID
    END
    DEALLOCATE LOCK_TABLE_LIST
    PRINT 'STATEMENT BEING BLOCKED'
    SELECT @MV_COMMAND2 = 'DBCC INPUTBUFFER ( ' + @MV_PROCESS + ')'

    EXEC (@MV_COMMAND2)
    END
    FETCH NEXT FROM CUR_TABLE_LIST INTO @MV_PROCESS, @MV_BLK
    END
    DEALLOCATE CUR_TABLE_LIST

  7. #7
    Join Date
    Feb 2004
    Location
    HONG KONG
    Posts
    4

    Re: SP Timeout Problem

    Originally posted by vrsrinivas
    try this script and tell me the output

    DECLARE @MV_PROCESS CHAR(10), @MV_COMMAND2 CHAR(254), @MV_BLK CHAR(10), @MV_OID INT
    SET NOCOUNT ON
    PRINT 'LOCK PROFILE'
    DECLARE CUR_TABLE_LIST CURSOR FOR
    SELECT P.SPID, P.BLOCKED
    FROM MASTER..SYSPROCESSES P
    WHERE P.BLOCKED <> 0
    OPEN CUR_TABLE_LIST
    FETCH NEXT FROM CUR_TABLE_LIST INTO @MV_PROCESS, @MV_BLK
    WHILE (@@FETCH_STATUS <> -1)
    BEGIN
    IF (@@FETCH_STATUS <> -2)
    BEGIN

    PRINT 'PROCESS BLOCKED: ' + @MV_PROCESS + ' BY PROCESS: ' + @MV_BLK
    DECLARE LOCK_TABLE_LIST CURSOR FOR
    SELECT RSC_OBJID
    FROM MASTER.DBO.SYSLOCKINFO
    WHERE MASTER.DBO.SYSLOCKINFO.REQ_STATUS = 3
    AND REQ_SPID = @MV_PROCESS
    OPEN LOCK_TABLE_LIST
    FETCH NEXT FROM LOCK_TABLE_LIST INTO @MV_OID
    WHILE (@@FETCH_STATUS <> -1)
    BEGIN
    IF (@@FETCH_STATUS <> -2)
    BEGIN
    SELECT NAME AS 'TABLE(S) CAUSING BLOCK' FROM SYSOBJECTS WHERE ID = @MV_OID
    END
    FETCH NEXT FROM LOCK_TABLE_LIST INTO @MV_OID
    END
    DEALLOCATE LOCK_TABLE_LIST
    PRINT 'STATEMENT BEING BLOCKED'
    SELECT @MV_COMMAND2 = 'DBCC INPUTBUFFER ( ' + @MV_PROCESS + ')'

    EXEC (@MV_COMMAND2)
    END
    FETCH NEXT FROM CUR_TABLE_LIST INTO @MV_PROCESS, @MV_BLK
    END
    DEALLOCATE CUR_TABLE_LIST
    The problem is not occuring now. Let me try later. However, when it has problem, I checked the blocked process (throught Enterprise Manasger) also but it didn't have. In the processes, I found the connection is active but doing nothing and no blockec processes. At that time, I can run the same sp with same argument in query analyzer successfully. Thanks.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •