New System.OutOfMemoryException on RTM release

Last week I removed the CTP version of SQL Server 2005 and installed the RTM
SQL Server 2005 Developer from the MSDN downloads. In doing some baseline
testing, I discovered that a trivial query that had been working correctly is
now generating:
"An error occurred while executing batch. Error message is: Exception of
type 'System.OutOfMemoryException' was thrown."
I installed fresh Microsoft Windows Server 2003 Standard + SP + critical
updates.
I installed fresh SQL Server 2005 Developer RTM.
The error still occurs. The query is "select <column> from <table>", where
column is a date_time field. Number of rows in <table> is about 42 million
... a mediums-sized table for our customers.
The machine hardware has not changed. It is a development box with a 3.0GHz
hyperthreaded P4 and 2GB of RAM. Something like 200GB of disk available.
Current space allocated to the database is 2X data size and 2x log size with
autogrowth at 100MB on each.
Only "unusual" thing about the box is that it has an Infineon Trusted
Platform Module installed. Device manager says that its active and working
correctly. In the My Computer -> Properties dialog box on the General tab, in
the Computer: section, below the lines that give the processor and RAM
information, appears the text "Physical Address Extension". I've never seen
this before and I don't know if it's related to the TPM or to the problem for
that matter.
This is looking like a client-side issue. It occurs when going against a
duplicate database an another server (SQL Server 2000) as well. When I
switched output from grid to text it still errored out, but the the error is
"Could not replace text."
The same query in the SQL Server 2000 QA executes will no problem, returned
the expected 42 million+ rows.
"ScottL" wrote:

> Last week I removed the CTP version of SQL Server 2005 and installed the RTM
> SQL Server 2005 Developer from the MSDN downloads. In doing some baseline
> testing, I discovered that a trivial query that had been working correctly is
> now generating:
> "An error occurred while executing batch. Error message is: Exception of
> type 'System.OutOfMemoryException' was thrown."
> I installed fresh Microsoft Windows Server 2003 Standard + SP + critical
> updates.
> I installed fresh SQL Server 2005 Developer RTM.
> The error still occurs. The query is "select <column> from <table>", where
> column is a date_time field. Number of rows in <table> is about 42 million
> ... a mediums-sized table for our customers.
> The machine hardware has not changed. It is a development box with a 3.0GHz
> hyperthreaded P4 and 2GB of RAM. Something like 200GB of disk available.
> Current space allocated to the database is 2X data size and 2x log size with
> autogrowth at 100MB on each.
> Only "unusual" thing about the box is that it has an Infineon Trusted
> Platform Module installed. Device manager says that its active and working
> correctly. In the My Computer -> Properties dialog box on the General tab, in
> the Computer: section, below the lines that give the processor and RAM
> information, appears the text "Physical Address Extension". I've never seen
> this before and I don't know if it's related to the TPM or to the problem for
> that matter.
>
>
|||Hi Scott,
Welcome to use MSDN Managed Newsgroup!
Thanks so much for your detailed information. From your descriptions, I
understood your query via SQL Server 2005 RTM will report the error message
"An error occurred while executing batch. Error message is: Exception of
type 'System.OutOfMemoryException' was thrown". If I have misunderstood
your concern, please feel free to point it out.
Based on my knowledge, since SQL Server Management Studio was built via
.NET technology. The limitation of System.Data.DataRows might be the root
cause for this error message. When total memory footprint was used up by 42
million rows, Management Studio will report System.OutOfMemoryException.
For now, answer/perform the steps below and let me know the result.
1. If you use SELECT statement to select other tables in AdventureWorks,
will it reproduce this behavior? For example
use AdventureWorks
go
SELECT * FROM HumanResources.Employee
go
2. If you change the SELECT statement to be SELECT TOP 100 <column> from
<table>, will you get the result as expected?
3. If you connect SQL Server via SQLCMD instead of SQL Server Management
Studio, will this SELECT statement report the error message?
4. In the SQL Server Management Studio, click menu Query -> Query
Options...
Make sure everything was set to Default and then have a test again.
Thank you for your patience and cooperation. If you have any questions or
concerns, don't hesitate to let me know. We are always here to be of
assistance!
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
================================================== ===
This posting is provided "AS IS" with no warranties, and confers no rights.
|||Hi Michael, thanks for the response. Here are the additional results you
requested:
1. Sorry, AdventureWorks is not loaded, so I instead executed the same query
against a summary table with 2.1 million rows. Result: completed OK. Then I
executed the same query against a detail table with 6.3 million rows. Result:
completed OK.
2. Experimenting with TOP shows that 100 records = OK, 40 million = error,
20 million = OK, 30 million = OK, 35 million = error. So on this system with
it's current processes, it begins to error out between 30 and 35 million rows.
3. I am assuming that you mean to use SQLCMD from the command line so that
it uses OleDB, not SQLCMD Mode in Management Studio. I ran the following
query from a command shell window:
sqlcmd -S <server name> -U sa -P <password> -d <db name> -q "select <column>
from <table>"
Result: Completed OK - 42.x million rows returned.
Actually, I went ahead and ran the query using SQLCMD Mode in Management
Studio as well (so as to try SQLCMD using SqlClient as a provider).
Result: same error, System.OutOfMemoryException
4. I selected Query --> Query Options and on the Query Options dialog
selected the button Reset to Default for each of the property pages. Clicked
OK to close dialog and reran the original query.
Result: same error, System.OutOfMemoryException

> .NET technology. The limitation of System.Data.DataRows might be the root
> cause for this error message. When total memory footprint was used up by 42
Note that it also errors when output is in Text mode. The error message is
"An error occurred while executing batch. Error message is: Couldn't replace
text" I repeated the TOP experiments (#2) in text mode. The results were: 100
rows = OK, 40 million = error, 20 million = error, 10 million = OK, 15
million = error.
Please advise. I know that this is kind of a far-out case, but this is
exactly the kind of thing that some of our customers will do and when it no
longer works with SQL Server 2005, as MS knows, we ISVs will be the
customers' first support call. :-)
Thanks, Scott
"Michael Cheng [MSFT]" wrote:

> Hi Scott,
> Welcome to use MSDN Managed Newsgroup!
> Thanks so much for your detailed information. From your descriptions, I
> understood your query via SQL Server 2005 RTM will report the error message
> "An error occurred while executing batch. Error message is: Exception of
> type 'System.OutOfMemoryException' was thrown". If I have misunderstood
> your concern, please feel free to point it out.
> Based on my knowledge, since SQL Server Management Studio was built via
> .NET technology. The limitation of System.Data.DataRows might be the root
> cause for this error message. When total memory footprint was used up by 42
> million rows, Management Studio will report System.OutOfMemoryException.
> For now, answer/perform the steps below and let me know the result.
> 1. If you use SELECT statement to select other tables in AdventureWorks,
> will it reproduce this behavior? For example
> use AdventureWorks
> go
> SELECT * FROM HumanResources.Employee
> go
> 2. If you change the SELECT statement to be SELECT TOP 100 <column> from
> <table>, will you get the result as expected?
> 3. If you connect SQL Server via SQLCMD instead of SQL Server Management
> Studio, will this SELECT statement report the error message?
> 4. In the SQL Server Management Studio, click menu Query -> Query
> Options...
> Make sure everything was set to Default and then have a test again.
> Thank you for your patience and cooperation. If you have any questions or
> concerns, don't hesitate to let me know. We are always here to be of
> assistance!
>
> Sincerely yours,
> Michael Cheng
> Microsoft Online Partner Support
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> ================================================== ===
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
>
|||Hi Scott,
Thanks for your testing and prompt update.
I believe too much results bring up the error message:
System.OutOfMemoryException. I am afriad this is a by design "feature" for
SQL Server Management Studio 2005 and we do not have a better workaround
except not using SSMS 2005.
You may also submit your feedback to the site below
http://lab.msdn.microsoft.com/produc...k/default.aspx
If this is urgent, you may open a Support incident with Microsoft Customer
Service and Support (CSS) so that a dedicated Support Professional can work
with you in a more timely and efficient manner. If you need any help in
this regard, please let me know.
Please be advised that contacting phone support will be a charged call.
However, if you are simply requesting a hotfix be sent to you and no other
support then charges are usually refunded or waived.
For a complete list of Microsoft Customer Service and Support phone
numbers, please go to the following address on the World Wide Web:
<http://support.microsoft.com/directory/overview.asp>
If you are outside the US please see http://support.microsoft.com for
regional support phone numbers.
Thank you for your patience and cooperation. If you have any questions or
concerns, don't hesitate to let me know. We are always here to be of
assistance!
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
================================================== ===
This posting is provided "AS IS" with no warranties, and confers no rights.