Insufficient system memory in resource pool to execute SQL Scripts in SQL Server 2008

Question

Greetings Friends

I am facing one problem, i have a tool which has been developed in C#.Net 2005 and it will create Database by executing SQL scripts; the scripts are running in batch.

When run in XP it works fine; but problem starts when it runs in Windows Vista 32 bit O.S. with SQR Server 2008 Express;i am logging the error in log file; below are the log with Size of the script files.

I don't understand why there less memory available to excute the scripts, earlier i faced this and broke the Master_Meata_Data into two files (Master_Data_Metadata1 and Master_Data_Metadata2); still i am facing the same issue in Vista...

08/28/2009 In DB progress start: Local\SQLEXPRESS:WINDOWS::1031:NEWDB bgWorkerCreateDB_DoWork:C:\Program Files\CreateNewDB\

ReadAndExecuteScript: C:\Program Files\CreateNewDB\Drop_Create.sql (Size 3KB)

ReadAndExecuteScript: C:\Program Files\CreateNewDB\Tables.sql (Size 999KB )

ReadAndExecuteScript: C:\Program Files\CreateNewDB\Master_Data.sql (Sizr 649KB)

ReadAndExecuteScript: C:\Program Files\CreateNewDB\Master_Data_Metadata1.sql (Size 2.72MB) Friday, August 28, 2009 SQL Error in ReadAndExecuteScript: There is insufficient system memory in resource pool 'internal' to run this query.

Friday, August 28, 2009 5:10 PM ReadAndExecuteScript: C:\Program Files\CreateNewDB\Data_Metadata2.sql (Size 1.55MB ) Friday, August 28, 2009 SQL Error in ReadAndExecuteScript: There is insufficient system memory in resource pool 'internal' to run this query.

ReadAndExecuteScript: C:\Program Files\CreateNewDB\Initialize_Data.sql (Size 14.3MB)

ReadAndExecuteScript: C:\Program Files\CreateNewDB\Views1.sql (Size 2KB)

ReadAndExecuteScript: C:\Program Files\CreateNewDB\Views2.sql (Size 4KB)

ReadAndExecuteScript: C:\Program Files\CreateNewDB\Views3.sql (Size 2KB)

ReadAndExecuteScript: C:\Program Files\CreateNewDB\Fn_Duration.sql (Size 2.27KB)

ReadAndExecuteScript: C:\Program Files\CreateNewDB_Fn_GetDate.sql (Size 1KB)

regards

Answer 1

Most likely you are just running out of physical memory, but if I you aren't and have the resource governor enabled try using

ALTER RESOURCE GOVERNOR 
    { DISABLE | RECONFIGURE }
|
        WITH ( CLASSIFIER_FUNCTION = { schema_name.function_name | NULL } )
|
    RESET STATISTICS
[ ; ]

and

ALTER WORKLOAD GROUP { group_name | "default" }
[ WITH
    ([ IMPORTANCE = { LOW | MEDIUM | HIGH } ]
           [ [ , ] REQUEST_MAX_MEMORY_GRANT_PERCENT = value ]
           [ [ , ] REQUEST_MAX_CPU_TIME_SEC = value ]
           [ [ , ] REQUEST_MEMORY_GRANT_TIMEOUT_SEC = value ]
           [ [ , ] MAX_DOP = value ]
           [ [ , ] GROUP_MAX_REQUESTS = value ] )
 ]
[ USING { pool_name | "default" } ]
[ ; ]

to alter the amount of memory available for executing queries.

MSDN Page for ALTER RESOURCE GOVERNOR

MSDN page for ALTER WORKLOAD GROUP

Answer 2

I can't answer the question of why it works in XP and not Vista, but when you see that error message associated with a very large script, it usually means that the query batch is too big for the parser to handle.

Are there any GO statements in those scripts? If not, that's probably what's causing the failure. More than 1 MB is pretty big for a SQL script that hasn't been split into batches. Either put some GO statements in there or (better yet) split it up into smaller scripts.