SQL Server 2005 'Insufficient Memory to run the query'

[radcaesar]

We are Running a .sql script which was created by tablediff.exe using SQLCMD

The script execution fails with the ' 701: There is insufficient system memory to run this query. ' Exception.

The following was the SQL Server Configuration


* Operating System: Windows Server 2003
* Database : SQL Server 2005 Standard Edition SP2
* Main Memory: 4GB
* Virtual Memory: 6GB
* Processors: 2 nos (Intel XEON 1.6 GHz)

Since, we are replicating a Database from a staging database, we are using the tablediff.exe.

The Script file which was created by the tablediff will be at the maximum size of 30MB.

The following are the SQL Server Settings which we are using now,


* Min Server Memory: 0
* Max Server Memory: 2147483647
* Min Memory Per Query: 2048
* AWE Enabled: No


Any ideas on where is the issue ?

[iburyak]

This error usually is not about a size.
It is usually when server is trying to do something in infinite loop.
If you have a script that you run try commenting out parts of this script and find out which part is not working.

Good Luck.

[radcaesar]

The sync script was created by the TableDiff utility. If the record count in that script is below 20,000, then no problems there. But it fails on huge counts like 60,000. I don't think that there will be a way for infinite loop run.

[iburyak]

http://support.microsoft.com/kb/912439

[radcaesar]

Hi,
This is the first fix i had tried and my production server's SP are up to date (Currently SP2).

I was confused that why it had thrown this exception after the fix was installed ? Any Suggestions ?

[iburyak]

Without seeing your script it is hard to guess what you are doing and where the problem is.

As I said it looks like something is taking too much memory and not releasing it on time.

[radcaesar]

Yes Exactly,

I found this cause some 2 weeks back itself. But there is no way i have to execute that script with 7 lakhs insert and updates using SQLCMD.

The sp which i used is below,

USE [ODS]
GO
/****** Object:  StoredProcedure [dbo].[sp_TableDiff4]    Script Date: 11/12/2007 05:58:32 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[sp_TableDiff4]
    -- Add the parameters for the stored procedure here
    (@sourceTable varchar(100), 
    @targetTable varchar(100), 
    @filepath varchar(100))
AS
BEGIN
        DECLARE @sTableDiff nvarchar(1000)
        DECLARE @sPath nvarchar(100)
        DECLARE @sTableName nvarchar(40)
        DECLARE @a nvarchar(50)
        DECLARE @sSQLCMD nvarchar(100)
        SET @sTableDiff= ' "C:\Program Files\Microsoft SQL Server\90\COM\tablediff" -sourceserver GRSRV4 -sourceuser username -sourcepassword pwd -sourcedatabase ODS-STG -sourcetable ' + @sourceTable + ' -destinationserver GRSRV4 -destinationuser username -destinationpassword password -destinationdatabase ODS -destinationtable ' + @targetTable + ' -f '+@filepath 
        PRINT @sTableDiff
        EXEC XP_CMDSHELL @sTableDiff
        SET @sTableName = @sourceTable
        PRINT @sTableName
        SET @sPath = 'd:\' + @sTableName
        SET @sPath = @filepath
        SET @sSQLCMD = 'sqlcmd -s GRSRV4 \SQL -U username -P pwd -d ODS -i ' + @sPath
        PRINT @sSQLCMD
        EXEC XP_CMDSHELL  @sSQLCMD
        SET @a = 'del ' + @sPath
        PRINT @a
        EXEC XP_CMDSHELL @a
END
 

In the above code, the sql cmd acquires memory to execute the 7L+ records.

Is there any other ways to release the acquired memory manually in our code i.e in the above sp ?

[iburyak]

You know what, I had similar problem a long time ago.
I had a DLL and because I had to call it multiple times it made memory overflow. I had to reboot my Server to proceed.
What happened was DLL stayed in server memory and with each call it loaded a new instance of the same DLL. I put this DLL under COM object and it started controlling environment, if I called the same DLL again it provided the same already loaded into memory instance instead of loading a new one. I never had this problem since. I think you should put both executables (tablediff and sqlcmd) under COM control which could solve your problem.

Hope it helps.
Good Luck

[radcaesar]

I called it through a stored procedure and a T-SQL script. I Was not using any languages like c# to make a dll and use it. Now app is in production and its hard to make those changes.

Any other ways to release the memory ?

[iburyak]

I don’t think you understood what I was talking about.

Go to your server, click Start – Run – type mmc, press enter.
Go to File – Add/Remove Snap-in
Press Add button and choose Component Service.
Press Add, Close, OK.
Expend everything to vew COM+ Applications
.
This is what I was talking about. You should put programs you are calling from your procedure under control of this object which will control memory usage and protect memory overflow.

Good Luck.

[radcaesar]

Thanks a lot, Let me try this....

I don’t think you understood what I was talking about.

Go to your server, click Start – Run – type mmc, press enter.
Go to File – Add/Remove Snap-in
Press Add button and choose Component Service.
Press Add, Close, OK.
Expend everything to vew COM+ Applications
.
This is what I was talking about. You should put programs you are calling from your procedure under control of this object which will control memory usage and protect memory overflow.

Good Luck.

[radcaesar]

Here i have a problem.

When i try to install, it requests for .MSI or .PAK file. But mine are all T-SQL and Stored Procedure.

The problem where i got is the SQLCMD.EXE (This is the one which was responsible for executing the scripts).

How can i add this in COM+ ?

I don’t think you understood what I was talking about.

Go to your server, click Start – Run – type mmc, press enter.
Go to File – Add/Remove Snap-in
Press Add button and choose Component Service.
Press Add, Close, OK.
Expend everything to vew COM+ Applications
.
This is what I was talking about. You should put programs you are calling from your procedure under control of this object which will control memory usage and protect memory overflow.

Good Luck.

[iburyak]

First program should be in this path according to your script.

C:\Program Files\Microsoft SQL Server\90\COM\tablediff.exe

Second program you should search for using Windows Search utility or better you should go to Registry and search for this keyword and it should show you where it is registered from.


sqlcmd.exe

Good Luck.
Irina.

[radcaesar]

Hey,
I Was unable to add that executable (sqlcmd.exe) into the COM+ service since it doesn't accept exe files. It only accepts .msi and .pak files (i.e To install pre-build applications it ask for .msi and .pak files but mine was executable).

Any workarounds ?