To Many SPIDS Open

  • We have a lot of SPIDS that are continuing to be open for days, and originate from the following UDF

    How do we close these SPIDS

    CREATE  FUNCTION DBO.fnITEMLIST

    (

    @ITEMNAME VARCHAR(50),

    @ITEMLIST VARCHAR(500)

    )

    RETURNS @tmpTABLE TABLE(FLD VARCHAR(50))

    AS

    BEGIN

    DECLARE @CTR INT

    IF @ITEMLIST IS NULL

    BEGIN

     IF @ITEMNAME = 'MEMBERGROUP'

     BEGIN

      INSERT INTO @tmpTABLE (FLD)

      SELECT DISTINCT MEMBERGROUP FROM DSS1..ACCOUNT

     END

     IF @ITEMNAME = 'MEMBERCATEGORY'

     BEGIN

      INSERT INTO @tmpTABLE (FLD)

      SELECT DISTINCT MEMBERCATEGORY FROM vwMEMBERCATEGORIES

     END

     IF @ITEMNAME = 'WEEKDAYNUMBER'

     BEGIN

      SET @CTR = 1

      WHILE @CTR < 7

      BEGIN

      INSERT INTO @tmpTABLE (FLD) VALUES (@CTR)

      SET @CTR=@CTR + 1

      END

     END

     IF @ITEMNAME = 'LOANTYPELIST'

     BEGIN

      INSERT INTO @tmpTABLE (FLD)

      SELECT DISTINCT TYPE FROM LOAN

     END

     IF @ITEMNAME = 'SHARETYPELIST'

     BEGIN

      INSERT INTO @tmpTABLE (FLD)

      SELECT DISTINCT TYPE FROM SHARE

     END

     IF @ITEMNAME = 'GLACCOUNTLIST'

     BEGIN

      INSERT INTO @tmpTABLE (FLD)

      SELECT DISTINCT GLACCOUNT FROM GLTRANSACTION

     END

    END

    ELSE

    BEGIN

     IF CHARINDEX(',',@ITEMLIST)=0

     BEGIN

     INSERT INTO @tmpTABLE (FLD) VALUES (@ITEMLIST)

     END

     ELSE

     BEGIN

     DECLARE @ITEM VARCHAR(100)

     SET @ITEMLIST = LTRIM(RTRIM(@ITEMLIST))+','

     SET @CTR = CHARINDEX(',', @ITEMLIST, 1)

      WHILE @CTR>0

      BEGIN

       SET @ITEM = LTRIM(RTRIM(LEFT(@ITEMLIST, @CTR-1)))

       IF @ITEM <> ''

       BEGIN

        INSERT INTO @tmpTABLE (FLD) VALUES (@ITEM)

       END

       SET @ITEMLIST = RIGHT(@ITEMLIST, LEN(@ITEMLIST) - @CTR)

       SET @CTR = CHARINDEX(',', @ITEMLIST, 1)

      END

     

     END

    END

    RETURN

    END --END OF FUNCTION

     

  • Don't look at how long open look at last bacth and see if that is days or not. If not then it is because the connection is being reused enough to keep it open due to pooling which is ok. However if it has been days then what Service Pack and Hotfix level are you at?

  • Version 8.00.760 SP3

  • Many SPIDS related to the UDF are still (last batch) open since 05/27/04

    1 ProductName NULL Microsoft SQL Server

    2 ProductVersion 524288 8.00.760

    3 Language 1033 English (United States)

    4 Platform NULL NT INTEL X86

    5 Comments NULL NT INTEL X86

    6 CompanyName NULL Microsoft Corporation

    7 FileDescription NULL SQL Server Windows NT

    8 FileVersion NULL 2000.080.0760.00

    9 InternalName NULL SQLSERVR

    10 LegalCopyright NULL © 1988-2003 Microsoft Corp. All rights reserved.

    11 LegalTrademarks NULL Microsoft® is a registered trademark of Microsoft Corporation. Windows(TM) is a trademark of Microsoft Corporation

    12 OriginalFilename NULL SQLSERVR.EXE

    13 PrivateBuild NULL NULL

    14 SpecialBuild 49807360 NULL

    15 WindowsVersion 248381957 5.2 (3790)

    16 ProcessorCount 8 8

    17 ProcessorActiveMask 255 000000ff

    18 ProcessorType 586 PROCESSOR_INTEL_PENTIUM

    19 PhysicalMemory 7935 7935 (8320798720)

    20 Product ID NULL NULL

  • Now when you look at these spids which server are you talking about, the one the UDF references or the local. If the remote then I am still looking as it is not somethign I have seen. If local then are these applications connecting and did they actually close on their end. If have then have someone cycle a machine to see if maybe a poor piece of code in the app is the issue. If the remote check the local to see if any couter parts still exist.

  • The SPIDS are on the database server

    We have an app server which calls a stored procedure on the database server which uses the UDF.

    After restarting the server all the SPIDS using the UDF go away, but over time build up again.

    Is there some code we need to add to the UDF to close the connection ?

     

    Thanks in advance for you help

  • Ok so after you restart the app server? Is that what you said (in case I misread).

     

    If so then you have to close your connections between each use for best effectiveness. Connection pooling will keep the connection readily available but all reasources should free as far as UDF and server side connection. However this is an interesting bit I need to look into. Could be that it isn't the UDF that is the issue however but the Applications last batch is still recorded and since the conection is still open it does release the info, and somewhere in your app you are casuing a new connection instead of reusing the old one as should be the case (the spid may or may not change when pooling is involved).

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply