sp_xml_removedocument problem?
-
I have developed a web service (C#.NET) that takes an XML document and blows it into SQL Server using sp_xml_preparedocument and sp_xml_removedocument in a stored proc. Everything seems to be working fine, but after a day or two, we get an error stating "XML document could not be created because server memory is low. Use sp_xml_removedocument to release XML documents." According to everything I can find via Google, we are using sp_xml_removedocument correctly. We are also running the latest SP (3A) for SQL Server 2000.
Anyone have a clue what I should do to debug or fix this? This is clearly a memory bleed of some sort. sp_xml_removedocument is being called every time. I will state that the XML file size is about a meg each, but when this is working, this works well.
Any help would be appreciated. I can't seem to scale this web service much past two "consumers" if this is going to keep happening. I need to get this up to about 50 consumers.
:s
Michael
-
Are you at the latest service pack? I know there were some leakage issues with the XML stuff. PSS might have a special hot fix for it as well.
-
Is there something beyond 3A that I am not aware of??
-
If you have OPENXML inside your transaction, you can potentially have memory leak.
1) XACT_ABORT set to ON. When PK or constarint error occurs, the statment is terminated immediately.
2) XACT_ABORT set to OFF. The error in 1) can be captured by @@ERROR and the memory can be released in error handling. But the timeout still cannot be captured and causing memory leak.
To preven this happening,
1) Define @idoc as an output parameter of the SP;
2) SET XACT_ABORT OFF
3) Define table variables and parsing the xml into those table variables
4) Release the xml memory
5) Start transaction processing
6) In the client side code, check the output parameter @idoc. If it's not 0, call another SP to release it.
Sample code:
CREATE PROCEDURE usp_InsertXMLData @idoc int=0 OUTPUT, @xmlData ntext
AS
SET NOCOUNT ON
SET @idoc=0
DECLARE @ParsedxmlData TABLE (...)
EXEC sp_xml_preparedocument @idoc OUTPUT, @xmlData
IF @@ERROR<>0 GOTO EXIT_FAILED
INSERT @ParsedxmlData (...)
SELECT ... FROM OPENXML (...)
-- if timeout here, the @idoc output will be >0
IF @@ERROR<>0 GOTO EXIT_FAILED
EXECUTE sp_xml_removedocument @idoc
IF @@ERROR=0 SET @idoc=0
BEGIN TRAN
...
COMMIT TRAN
RETURN 0
EXIT_ROLLBACK:
ROLLBACK TRAN
RETURN -1
EXIT_FAILED:
IF @idoc>0
BEGIN
EXECUTE sp_xml_removedocument @idoc
IF @@ERROR=0 set @idoc=0
END
RETURN -2
GO
CREATE usp_ReleaseXML @idoc int
AS
SET NOCOUNT ON
IF @idoc>0
BEGIN
EXECUTE sp_xml_removedocument @idoc
set @idoc=0
END
RETURN 0
-
Thank you very much! Great reply!
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply