TempDB issue

  • 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

  • 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

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • ...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

  • 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

  • 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

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • 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

  • 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