"Too many sessions open"

Hi. I have a program that performs a lot of queries (it's not exactly optimized yet) and I'm getting a "too many sessions" exception. I'm not clear on what a "session" is exactly because for one thing I only have one thread doing any real work, and I thought I only had one connection open for the entire application.

Am I forgetting to dispose of some resource or close a "session"?

I'm not using anything to do with transactions as all of my operations are atomic.

Could you please explain to me exactly what a session is, relative to connection and transaction. Thanks.P.S.
I'm doing most of my work through ExecuteResultSet() methods and iterating the result sets via Read() and Get...().|||

Did you find a solution for this? I'm running into the same problem. Any help is appreciated.

|||Use Data access block library, it is realy usefull

"Too many sessions open"

Hi. I have a program that performs a lot of queries (it's not exactly optimized yet) and I'm getting a "too many sessions" exception. I'm not clear on what a "session" is exactly because for one thing I only have one thread doing any real work, and I thought I only had one connection open for the entire application.

Am I forgetting to dispose of some resource or close a "session"?

I'm not using anything to do with transactions as all of my operations are atomic.

Could you please explain to me exactly what a session is, relative to connection and transaction. Thanks.
P.S.
I'm doing most of my work through ExecuteResultSet() methods and iterating the result sets via Read() and Get...().
|||

Did you find a solution for this? I'm running into the same problem. Any help is appreciated.

|||Use Data access block library, it is realy usefull

"too many fields defined" error when previewing xls

Anyone know why I am getting this?

===================================

There was an error displaying the preview. (Microsoft Visual Studio)

===================================

Too many fields defined. (Microsoft JET Database Engine)

Program Location:

at Microsoft.SqlServer.Dts.Tasks.ExecuteSQLTask.Connections.SQLTaskConnectionOleDbClass.ExecuteStatement(Int32 resultType, Boolean isStoredProc, UInt32 dwTimeOut)
at Microsoft.DataTransformationServices.Design.PipelineUtils.ShowDataPreview(String sqlStatement, ConnectionManager connectionManager, Control parentWindow, IServiceProvider serviceProvider, IDTSExternalMetadataColumnCollection90 externalColumns)
at Microsoft.DataTransformationServices.DataFlowUI.DataFlowConnectionPage.previewButton_Click(Object sender, EventArgs e)

A wild stab in the dark: You have to many fields? How many do you have? A clue would help determine if it is a problem of indeed a limitation..|||31 columns on the sheet that I am importing from.|||Obviously 31 is not to many. You have 31 columns with data in. Do you perhaps have any columns with a space or some other text in it right at the end of the document. Like at column ZZ?

I can create a sheet with 32 columns in it and it is fine. Adding spaces to columns after that causes the same error.

I suggest copying the columns you want out, pasting them in a new sheet and try that.|||the sheet has autofilter set on, could that be the cause?|||I don't know. Remove it and see.

"Too low memory" and XML in cache

Hi,

I've read that the parser can use 1/8th of the available memory for the xml-cache, but I've run into this limit much sooner than I'd expect.

I'm not using "sp_xml_preparedocument", but rather operating on columns with XML as datatype. This means I don't have the handle to the document and can't use "sp_xml_removedocument" to free the memory.

It's a simple application written in C#, so should it be sufficient to "reset" the sql-connection or how should I do it?

*I've tried to keep the post as simple as I could, but I might have simplified it too much?*

What is you application doing with XML, what level of concurrency?

When I investigated the 1/8 question I couldn't get any forumla on any machine to get to this number. THe one thing I did find was that it appeared to be related to the amount of memory available at startup of sql server. If running on a user machine you are likely to have lots of other apps running, VS, Managment Studio, help etc. These just take chunks out of the available memory for SQL.

I ahev no experience of the XML data type memory requirements in 2005 sorry.

|||

Hi,

My application does nothing more than look-ups and it does it all on one connection. As far as I can see, closing the connection should free the cache though.

My "problem" is that Im not using "sp_xml_preparedocument" and thus not getting the handle for the document.

Perhaps there is a way to see the "cache-status"?

I got the 1/8 from the reference regarding the stored procedure "sp_xml_preparedocument". http://msdn2.microsoft.com/en-us/library/ms187367(SQL.90).aspx

"A parsed document is stored in the internal cache of SQL Server 2005. The MSXML parser uses one-eighth the total memory available for SQL Server. To avoid running out of memory, run sp_xml_removedocument to free up the memory"

|||Can you post the error you are getting, when you are getting it and as much of the code as possible.|||

Basically I either get one of the following messages:

Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.

or

XML document could not be created because server memory is low. Use sp_xml_removedocument to release XML documents. (when doing SqlDataReader reader = cmd.ExecuteReader(); )

I'll do a few more tests to see if I can narrow it down a little, because I noticed that the application and sql-process didn't increase their memory usage, but the pagefile-usage kept increasing.

The program is printing some invoices, so the printing-part might influence it.

*heads back to his experiement*

|||

What SQL are you executing? Without this we can't really work out what might be the problem.

When you get the timeout, what processes are using the cpu?

|||

I'll clean/write it up, as it is rather embedded right now. I hope to make it clearer and without as much "clutter"-code.

In reality it is many select-statements on a table with a column with the xml-type. I'll eloborate later when I get it "readable"

Im running the application in debug-mode from Visual C# 2005.

|||

*slaps his forehead*

I think I found my mistake. The sp_xml_preparedocument did indeed get called (was hidden away in a stored procedure)

I'll run a test and see if it works.

-- *some variable names are in danish*

set ANSI_NULLS ON

set QUOTED_IDENTIFIER ON

go

ALTER PROCEDURE [dbo].[FakturaSP] AS

CREATE TABLE #philTemp

(prim int identity,

connectionID int)

--Fill #philTemp with the ConnectionIDs

INSERT INTO #philTemp SELECT ConnectionID FROM Connections

WHERE XmlData.exist('/Bat2005.Connection[ConnectionType = "FakturaHoved_OrdreHoved"]') = 1

--SELECT * FROM #philTemp

declare @.counter int

declare @.maxCount int

DECLARE @.doc xml

DECLARE @.idoc int

set @.counter = 0

SELECT @.maxCount = COUNT(*) FROM #philTemp

--Create table with the ordrehoveder that has been invoiced

CREATE TABLE #faktureredeOrdrehoveder

(prim int identity,

ordrehovedID int)

while @.counter < @.maxCount begin

set @.counter = @.counter + 1

SELECT @.doc = XmlData FROM Connections

WHERE ConnectionID = (SELECT ConnectionID FROM #philTemp WHERE prim = @.counter)

EXEC sp_xml_preparedocument @.idoc OUTPUT, @.doc

INSERT INTO #faktureredeOrdrehoveder SELECT *

FROM OPENXML (@.idoc, '/Bat2005.Connection',2)

WITH (IDofChild int)

EXEC sp_xml_removedocument @.idoc

end

--SELECT * FROM #faktureredeOrdrehoveder

--

CREATE TABLE #alleOrdrehoveder

(prim int identity,

ordrehovedID int)

--Fill #philTemp with the ConnectionIDs

INSERT INTO #alleOrdrehoveder SELECT ComponentID FROM Components

WHERE XmlData.exist('/BAT.Templates.DanNET.Ordrehoved') = 1

--SELECT * FROM #alleOrdrehoveder ORDER BY ordrehovedID asc;

SELECT #alleOrdrehoveder.ordrehovedID

FROM #alleOrdrehoveder LEFT JOIN #faktureredeOrdrehoveder ON #alleOrdrehoveder.ordrehovedID = #faktureredeOrdrehoveder.ordrehovedID

WHERE (((#faktureredeOrdrehoveder.ordrehovedID) Is Null)) ORDER BY ordrehovedID asc;

--Clean up

DROP TABLE #alleOrdrehoveder

DROP TABLE #faktureredeOrdrehoveder

DROP TABLE #philTemp

|||

A couple of comments.

Always create temporary tables at the very start, avoids the possibility of recompiles ( i know 2005 is better, but this is good practice)

Have you tried using the nodes method, have a look at the following link in BOL

ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/7267fe1b-2e34-4213-8bbf-1c953822446c.htm

Avoids the use of openXML.

|||

Thanks for the pointers, I'll have a look at the right away.

As for the stored procedure, it's a procedure I made to find the orders that haven't been invoiced yet. I didn't really plan to keep it (but it's doing its job) and I didn't expect to find the error here. (as you could see from the code I'm not really used to making stored procedures)

I just ran the test and it completed, although the pagefile is still increasing, but Im guessing that's due to another mistake somewhere else :-)

/Philip

|||

"Basically I either get one of the following messages:Timeout expired. The timeout period elapsed .."

Your second error "low memory" most probably is caused by the time out error. The reason is when time out happened, you SP has no chance to run the procedure sp_xml_removedocument.

One trick you can do when this happends is to call sp_xml_removedocument in a loop.

DECLARE @.Loop int

SET @.Loop=1

WHILE @.Loop<1000000

BEGIN

exec sp_xml_removedocument @.Loop

SET @.Loop=@.Loop+1

END

It may print lots of errors if @.Loop is not a valid hanlde of xml. But if it's, it will be removed.

For how to avoid memory leaking in this situation, please reference OPENXML and memory leak

|||A good point about xml in a transaction, thankssql

"Too low memory" and XML in cache

Hi,

I've read that the parser can use 1/8th of the available memory for the xml-cache, but I've run into this limit much sooner than I'd expect.

I'm not using "sp_xml_preparedocument", but rather operating on columns with XML as datatype. This means I don't have the handle to the document and can't use "sp_xml_removedocument" to free the memory.

It's a simple application written in C#, so should it be sufficient to "reset" the sql-connection or how should I do it?

*I've tried to keep the post as simple as I could, but I might have simplified it too much?*

What is you application doing with XML, what level of concurrency?

When I investigated the 1/8 question I couldn't get any forumla on any machine to get to this number. THe one thing I did find was that it appeared to be related to the amount of memory available at startup of sql server. If running on a user machine you are likely to have lots of other apps running, VS, Managment Studio, help etc. These just take chunks out of the available memory for SQL.

I ahev no experience of the XML data type memory requirements in 2005 sorry.

|||

Hi,

My application does nothing more than look-ups and it does it all on one connection. As far as I can see, closing the connection should free the cache though.

My "problem" is that Im not using "sp_xml_preparedocument" and thus not getting the handle for the document.

Perhaps there is a way to see the "cache-status"?

I got the 1/8 from the reference regarding the stored procedure "sp_xml_preparedocument". http://msdn2.microsoft.com/en-us/library/ms187367(SQL.90).aspx

"A parsed document is stored in the internal cache of SQL Server 2005. The MSXML parser uses one-eighth the total memory available for SQL Server. To avoid running out of memory, run sp_xml_removedocument to free up the memory"

|||Can you post the error you are getting, when you are getting it and as much of the code as possible.|||

Basically I either get one of the following messages:

Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.

or

XML document could not be created because server memory is low. Use sp_xml_removedocument to release XML documents. (when doing SqlDataReader reader = cmd.ExecuteReader(); )

I'll do a few more tests to see if I can narrow it down a little, because I noticed that the application and sql-process didn't increase their memory usage, but the pagefile-usage kept increasing.

The program is printing some invoices, so the printing-part might influence it.

*heads back to his experiement*

|||

What SQL are you executing? Without this we can't really work out what might be the problem.

When you get the timeout, what processes are using the cpu?

|||

I'll clean/write it up, as it is rather embedded right now. I hope to make it clearer and without as much "clutter"-code.

In reality it is many select-statements on a table with a column with the xml-type. I'll eloborate later when I get it "readable"

Im running the application in debug-mode from Visual C# 2005.

|||

*slaps his forehead*

I think I found my mistake. The sp_xml_preparedocument did indeed get called (was hidden away in a stored procedure)

I'll run a test and see if it works.

-- *some variable names are in danish*

set ANSI_NULLS ON

set QUOTED_IDENTIFIER ON

go

ALTER PROCEDURE [dbo].[FakturaSP] AS

CREATE TABLE #philTemp

(prim int identity,

connectionID int)

--Fill #philTemp with the ConnectionIDs

INSERT INTO #philTemp SELECT ConnectionID FROM Connections

WHERE XmlData.exist('/Bat2005.Connection[ConnectionType = "FakturaHoved_OrdreHoved"]') = 1

--SELECT * FROM #philTemp

declare @.counter int

declare @.maxCount int

DECLARE @.doc xml

DECLARE @.idoc int

set @.counter = 0

SELECT @.maxCount = COUNT(*) FROM #philTemp

--Create table with the ordrehoveder that has been invoiced

CREATE TABLE #faktureredeOrdrehoveder

(prim int identity,

ordrehovedID int)

while @.counter < @.maxCount begin

set @.counter = @.counter + 1

SELECT @.doc = XmlData FROM Connections

WHERE ConnectionID = (SELECT ConnectionID FROM #philTemp WHERE prim = @.counter)

EXEC sp_xml_preparedocument @.idoc OUTPUT, @.doc

INSERT INTO #faktureredeOrdrehoveder SELECT *

FROM OPENXML (@.idoc, '/Bat2005.Connection',2)

WITH (IDofChild int)

EXEC sp_xml_removedocument @.idoc

end

--SELECT * FROM #faktureredeOrdrehoveder

--

CREATE TABLE #alleOrdrehoveder

(prim int identity,

ordrehovedID int)

--Fill #philTemp with the ConnectionIDs

INSERT INTO #alleOrdrehoveder SELECT ComponentID FROM Components

WHERE XmlData.exist('/BAT.Templates.DanNET.Ordrehoved') = 1

--SELECT * FROM #alleOrdrehoveder ORDER BY ordrehovedID asc;

SELECT #alleOrdrehoveder.ordrehovedID

FROM #alleOrdrehoveder LEFT JOIN #faktureredeOrdrehoveder ON #alleOrdrehoveder.ordrehovedID = #faktureredeOrdrehoveder.ordrehovedID

WHERE (((#faktureredeOrdrehoveder.ordrehovedID) Is Null)) ORDER BY ordrehovedID asc;

--Clean up

DROP TABLE #alleOrdrehoveder

DROP TABLE #faktureredeOrdrehoveder

DROP TABLE #philTemp

|||

A couple of comments.

Always create temporary tables at the very start, avoids the possibility of recompiles ( i know 2005 is better, but this is good practice)

Have you tried using the nodes method, have a look at the following link in BOL

ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/7267fe1b-2e34-4213-8bbf-1c953822446c.htm

Avoids the use of openXML.

|||

Thanks for the pointers, I'll have a look at the right away.

As for the stored procedure, it's a procedure I made to find the orders that haven't been invoiced yet. I didn't really plan to keep it (but it's doing its job) and I didn't expect to find the error here. (as you could see from the code I'm not really used to making stored procedures)

I just ran the test and it completed, although the pagefile is still increasing, but Im guessing that's due to another mistake somewhere else :-)

/Philip

|||

"Basically I either get one of the following messages:Timeout expired. The timeout period elapsed .."

Your second error "low memory" most probably is caused by the time out error. The reason is when time out happened, you SP has no chance to run the procedure sp_xml_removedocument.

One trick you can do when this happends is to call sp_xml_removedocument in a loop.

DECLARE @.Loop int

SET @.Loop=1

WHILE @.Loop<1000000

BEGIN

exec sp_xml_removedocument @.Loop

SET @.Loop=@.Loop+1

END

It may print lots of errors if @.Loop is not a valid hanlde of xml. But if it's, it will be removed.

For how to avoid memory leaking in this situation, please reference OPENXML and memory leak

|||A good point about xml in a transaction, thanks