Post reply

Out of memory error

  • September 20, 2013 at 8:01 am

    #283439

    I have a procedure, in which returns many results in the grid(over 1 million) and when I run it I get the out of memory error. I tried running the script with the "discard results after execution" box checked and not only was I able to process many more records, but the execution time was cut down from about an hour to 5 minutes. The checkbox I was referring to is located under Tools > Options > Query Results > SQL Server > Results to Grid > Discard results after execution. Is there an equivalent T-SQL command that can perform the same configuration? Having this box checked still processes the script, but doesn't produce any results in the grid. The script now runs so much faster and memory isn't getting used up by the massive amounts of rows that were previously being returned. Since this is will be run in production, i would like to include this option in the procedure/ script instead of selecting it from GUI.

  • September 20, 2013 at 2:21 pm

    #1652048

    once the proc is in production would you not want the results?

    Bob
    -----------------------------------------------------------------------------
    How to post to get the best help[/url]

  • September 20, 2013 at 5:01 pm

    #1652083

    The error message (out of memory) is a client-side error. It's not SQL Server that's running out of memory, it's the client application (Management studio). There's no option in SQL to run a query and discard the results. SQL will send the results to the client app, what that client app does with the data is its business.

    If you don't want the stored proc to return results, then remove the select that's returning results from the procedure.

    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

  • October 8, 2013 at 6:41 am

    #1656707

    Thank you for your replies, the issue is that this script is from a third party software, there is no way to remove the select, are you aware of any issues of > Options > Query Results > SQL Server > Results to Grid > Discard results after execution could cause if selected to run the query?

  • October 8, 2013 at 7:18 am

    #1656723

    Other than the fact that you won't see any results, no.

    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

  • October 8, 2013 at 7:41 am

    #1656734

    Thank you Gila.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply