August 23, 2013 at 8:44 am
Hi All
I'm having an issue with the tempdb, after running an intensive insert script (with simple insert statements)
The total number of inserts to be done is 2800. For each record a several select statements are performed to obtain values.
The error message is:
"
An error occurred while executing batch. Error message is: Exception of type 'System.OutOfMemoryException' was thrown.
"
Then I try to see the tempdb properties and it is not possible.
Anyway the tempdb is configured with 12 data files each with 256MB and an autogrowth of 60MB.
The tempdb log file is 2GB with 1GB of autogrowth.
I must restart the instance in order to see the tempdb properties.
The system is 24 core with 32 GB RAM, of which 28GB are dedicated to sql server.
There are no missing indexes in the script.
Can anyone suggest something...
Thank you
IgorMi
Igor Micev,My blog: www.igormicev.com
August 23, 2013 at 9:06 am
That's a client error, not server. It's a .Net memory error (sql server is not written in .net)
You're running Management Studio, on your client machine out of memory. Nothing to do with TempDB.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 23, 2013 at 9:07 am
...and additional
The insert operations execute under an explicit transaction.
I wanted to try to create a snapshot of the database and was not able because of the
SQL Server version:
Microsoft SQL Server 2008 R2 (RTM) - 10.50.1617.0 (X64) Apr 22 2011 19:23:43 Copyright (c) Microsoft Corporation Standard Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1)
Regards
IgorMi
Igor Micev,My blog: www.igormicev.com
August 23, 2013 at 10:51 am
GilaMonster (8/23/2013)
That's a client error, not server. It's a .Net memory error (sql server is not written in .net)You're running Management Studio, on your client machine out of memory. Nothing to do with TempDB.
Hi Gail
Yes, it's a bug of the Managment studio of SQL.
I tried manually for 1000 transactions and it works.
I have to re-write the script so that it does the inserts in smaller batches.
Best regards,
IgorMi
Igor Micev,My blog: www.igormicev.com
August 23, 2013 at 10:54 am
No, it's not a bug. The client app ran out of memory probably due to the 'several selects for each insert'.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 23, 2013 at 11:15 am
GilaMonster (8/23/2013)
No, it's not a bug. The client app ran out of memory probably due to the 'several selects for each insert'.
Correct!
Thank you
IgorMi
Igor Micev,My blog: www.igormicev.com
August 23, 2013 at 12:48 pm
IgorMi (8/23/2013)
GilaMonster (8/23/2013)
No, it's not a bug. The client app ran out of memory probably due to the 'several selects for each insert'.Correct!
Thank you
IgorMi
Issue was surpassed after I changed the output to text results.
Thanks,
IgorMi
Igor Micev,My blog: www.igormicev.com
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply