Msg 701,Level 17,State 123,Server BRECK-PC\sqlEXPRESS,Line 2
There is insufficient system memory in resource pool 'internal' to run this 
query.

Location:        qxcntxt.cpp:1052
Expression:      cref == 0
SPID:            51
Process ID:      1880

服务器保持不响应,直到重新启动sql Server.

正在使用的软件：

> Windows Vista Ultimate 64位版本6001 SP1
> Microsoft sql Server 2008(SP1) – 10.0.2734.0(X64)2009年9月11日14:30:58版权所有(c)1988-2008 Windows NT 6.0上的高级服务(64位)的Microsoft Corporation Express Edition(Build 6001： Service Pack 1)
> sql Anywhere 11.0.1.2276中的SAOLEDB.11驱动程序

将最大服务器内存(MB)设置为2048没有帮助.

将各种-g值(例如-g256;)添加到服务器启动参数没有帮助.

使用DBCC FREESYSTEMCACHE(‘ALL’),DBCC FREESESSIONCACHE和DBCC FREEPROCCACHE没有帮助.

即使它包含对涉及链接服务器使用的内存泄漏症状的修复,将Cumnulative更新包4安装到sql Server 2008 Service Pack 1也没有帮助.

从INSERT分离SELECT … ROW_NUMBER()OVER …查询没有帮助.实验表明,复杂的SELECT没有引起内存泄漏,INSERT没有.

更改代码以使用ad-hoc“INSERT INTO OPENROWSET”语法而不是链接的服务器没有帮助;下面的代码显示了链接的服务器使用情况.

sysinternals.com Process Explore实用程序显示内存使用情况与sqlserver.exe相关联,而不是sql Anywhere OLEDB驱动程序SAOLEDB.11使用的DLL.

请注意,链接服务器(代理表)的sql Anywhere版本可以正常工作,以便在单个事务中将“sql”2008表中的190万行“拉”为sql Anywhere 11数据库.这里所示的逻辑是尝试使用链接的服务器功能来“推”行;方向相同,语法不同.

代码如下执行EXECUTE copy_mss_t2三或四次之后,4G的RAM已耗尽：

EXEC sys.sp_configure 
   N'show advanced options',N'1'
GO

RECONFIGURE WITH OVERRIDE
GO

EXEC sys.sp_configure
   N'max server memory (MB)',N'2048'
GO

RECONFIGURE WITH OVERRIDE
GO

EXEC sys.sp_configure
   N'show advanced options',N'0'
GO

RECONFIGURE WITH OVERRIDE
GO
EXEC master.dbo.sp_MSset_oledb_prop
   N'SAOLEDB.11',N'AllowInProcess',1
GO

sp_addlinkedserver
   @server = 'mem',@srvproduct = 'sql Anywhere OLE DB Provider',@provider = 'SAOLEDB.11',@datasrc = 'mem_PAVILION2'
GO

EXEC master.dbo.sp_serveroption
   @server=N'mem',@optname=N'rpc',@optvalue=N'true'
GO

EXEC master.dbo.sp_serveroption
   @server=N'mem',@optname=N'rpc out',@optvalue=N'true'
GO

sp_addlinkedsrvlogin
   @rmtsrvname = 'mem',@useself = 'false',@locallogin = NULL,@rmtuser = 'dba',@rmtpassword = 'sql'
GO

CREATE PROCEDURE copy_mss_t2
   @from_row            BIGINT,@to_row              BIGINT,@rows_copied_count   BIGINT OUTPUT
AS

   SELECT *
     INTO #t
     FROM ( SELECT *,ROW_NUMBER()
                      OVER ( ORDER BY sample_set_number,connection_number )
                   AS t2_row_number
             FROM mss_t2 ) AS ordered_mss_t2
    WHERE ordered_mss_t2.t2_row_number BETWEEN @from_row AND @to_row;

   SELECT @rows_copied_count = COUNT(*)
     FROM #t;

INSERT INTO mem..dba.sa_t2
SELECT sampling_id,sample_set_number,connection_number,blocker_owner_table_name,blocker_lock_type,blocker_owner_name,blocker_table_name,blocker_reason,blocker_row_identifier,current_engine_version,page_size,ApproximatecpuTime,BlockedOn,BytesReceived,BytesSent,CacheHits,CacheRead,"Commit",DiskRead,DiskWrite,FullCompare,IndAdd,IndLookup,Isolation_level,LastReqTime,LastStatement,LockCount,LockName,LockTableOID,LoginTime,LogWrite,Name,NodeAddress,Prepares,PrepStmt,QueryLowMemoryStrategy,QueryOptimized,QueryReused,ReqCountActive,ReqCountBlockContention,ReqCountBlockIO,ReqCountBlockLock,ReqCountUnscheduled,ReqStatus,ReqTimeActive,ReqTimeBlockContention,ReqTimeBlockIO,ReqTimeBlockLock,ReqTimeUnscheduled,ReqType,RequestsReceived,Rlbk,RollbackLogPages,TempFilePages,TransactionStartTime,UncommitOp,Userid,prevIoUs_ApproximatecpuTime,interval_ApproximatecpuTime,prevIoUs_Commit,interval_Commit,prevIoUs_Rlbk,interval_Rlbk
  FROM #t;

GO

DECLARE @rows_copied_count BIGINT
EXECUTE copy_mss_t2 1110001,1120000,@rows_copied_count OUTPUT
SELECT @rows_copied_count
GO

EXECUTE create_linked_server
GO

DECLARE @rows_copied_count BIGINT
EXECUTE copy_mss_t2 1120001,1130000,@rows_copied_count OUTPUT
SELECT @rows_copied_count
GO

EXECUTE create_linked_server
GO

这里是sql Server源表,包含大约1G的数据在190万行中：

CREATE TABLE mss_t2 (
   sampling_id                       BIGINT NOT NULL,sample_set_number                 BIGINT NOT NULL,connection_number                 BIGINT NOT NULL,blocker_owner_table_name          VARCHAR ( 257 ) NULL,blocker_lock_type                 VARCHAR ( 32 ) NULL,blocker_owner_name                VARCHAR ( 128 ) NULL,blocker_table_name                VARCHAR ( 128 ) NULL,blocker_reason                    TEXT NULL,blocker_row_identifier            VARCHAR ( 32 ) NULL,current_engine_version            TEXT NOT NULL,page_size                         INTEGER NOT NULL,ApproximatecpuTime                DECIMAL ( 30,6 ) NULL,BlockedOn                         BIGINT NULL,BytesReceived                     BIGINT NULL,BytesSent                         BIGINT NULL,CacheHits                         BIGINT NULL,CacheRead                         BIGINT NULL,"Commit"                          BIGINT NULL,DiskRead                          BIGINT NULL,DiskWrite                         BIGINT NULL,FullCompare                       BIGINT NULL,IndAdd                            BIGINT NULL,IndLookup                         BIGINT NULL,Isolation_level                   BIGINT NULL,LastReqTime                       TEXT NOT NULL DEFAULT '1900-01-01',LastStatement                     TEXT NULL,LockCount                         BIGINT NULL,LockName                          BIGINT NULL,LockTableOID                      BIGINT NULL,LoginTime                         TEXT NOT NULL DEFAULT '1900-01-01',LogWrite                          BIGINT NULL,Name                              VARCHAR ( 128 ) NULL,NodeAddress                       TEXT NULL,Prepares                          BIGINT NULL,PrepStmt                          BIGINT NULL,QueryLowMemoryStrategy            BIGINT NULL,QueryOptimized                    BIGINT NULL,QueryReused                       BIGINT NULL,ReqCountActive                    BIGINT NULL,ReqCountBlockContention           BIGINT NULL,ReqCountBlockIO                   BIGINT NULL,ReqCountBlockLock                 BIGINT NULL,ReqCountUnscheduled               BIGINT NULL,ReqStatus                         TEXT NULL,ReqTimeActive                     DECIMAL ( 30,ReqTimeBlockContention            DECIMAL ( 30,ReqTimeBlockIO                    DECIMAL ( 30,ReqTimeBlockLock                  DECIMAL ( 30,ReqTimeUnscheduled                DECIMAL ( 30,ReqType                           TEXT NULL,RequestsReceived                  BIGINT NULL,Rlbk                              BIGINT NULL,RollbackLogPages                  BIGINT NULL,TempFilePages                     BIGINT NULL,TransactionStartTime              TEXT NOT NULL DEFAULT '1900-01-01',UncommitOp                        BIGINT NULL,Userid                            VARCHAR ( 128 ) NULL,prevIoUs_ApproximatecpuTime       DECIMAL ( 30,6 ) NOT NULL DEFAULT 0.0,interval_ApproximatecpuTime       AS ( COALESCE ( "ApproximatecpuTime",0 ) - prevIoUs_ApproximatecpuTime ),prevIoUs_Commit                   BIGINT NOT NULL DEFAULT 0,interval_Commit                   AS  ( COALESCE ( "Commit",0 ) - prevIoUs_Commit ),prevIoUs_Rlbk                     BIGINT NOT NULL DEFAULT 0,interval_Rlbk                     AS  ( COALESCE ( Rlbk,0 ) - prevIoUs_Rlbk ) )

以下是sql Anywhere 11中的目标表：

CREATE TABLE sa_t2 (
   sampling_id                       BIGINT NOT NULL,interval_ApproximatecpuTime       DECIMAL ( 30,6 ) NOT NULL COMPUTE ( COALESCE ( "ApproximatecpuTime",interval_Commit                   BIGINT NOT NULL COMPUTE ( COALESCE ( "Commit",interval_Rlbk                     BIGINT NOT NULL COMPUTE ( COALESCE ( Rlbk,0 ) - prevIoUs_Rlbk ),PRIMARY KEY ( sample_set_number,connection_number ) );