June 7, 2004 at 12:25 pm
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
June 7, 2004 at 1:24 pm
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?
June 7, 2004 at 2:28 pm
Version 8.00.760 SP3
June 7, 2004 at 2:48 pm
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
June 7, 2004 at 3:35 pm
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.
June 7, 2004 at 3:46 pm
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
June 7, 2004 at 4:02 pm
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