This is a SQL Server 2008 error message, probably SQL Server's fault,
but I'm asking the question here because it involves SAOLEDB.11 to
copy data from SQL Server to SQL Anywhere 11.0.1.2276.

So, here we go, in case anyone else has a suggestion, workaround,
or... be still, my pounding heart... an actual solution!

=====

SQL Server 2008 Linked Server and ad-hoc INSERTs cause a rapid memory
leak which eventually causes the server to become non-responsive and
ends with the following error:

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

The server remains non-responsive until SQL Server is restarted.

Software in use:

- Windows Vista Ultimate 64 bit build 6001 SP1

- Microsoft SQL Server 2008 (SP1) - 10.0.2734.0 (X64) Sep 11 2009
14:30:58 Copyright (c) 1988-2008 Microsoft Corporation Express
Edition with Advanced Services (64-bit) on Windows NT 6.0 <X64> (Build
6001: Service Pack 1)

- SAOLEDB.11 driver from SQL Anywhere 11.0.1.2276

Setting max server memory (MB) to 2048 did not help.

Adding various -g values (e.g., -g256 to the server Startup
Parameters did not help.

Using DBCC FREESYSTEMCACHE ( 'ALL' ), DBCC FREESESSIONCACHE and DBCC
FREEPROCCACHE did not help.

Installing the Cumnulative update package 4 to SQL Server 2008 Service
Pack 1 did not help, even though it contained a fix to a memory leak
symptom involving Linked Server usage.

Separating the SELECT ... ROW_NUMBER() OVER ... query from the INSERT
did not help. Experimentation showed that the complex SELECT did not
cause the memory leak, the INSERT did.

Changing the code to use the ad-hoc "INSERT INTO OPENROWSET" syntax
instead of a linked server did not help; the code below shows the
linked server usage.

The sysinternals.com Process Explore utility shows that the memory
usage was associated with sqlserver.exe, not the DLLs used by the SQL
Anywhere OLEDB driver SAOLEDB.11.

Note that the SQL Anywhere version of linked server (proxy tables)
works OK, to "pull" 1.9 million rows from a SQL Server 2008 table to a
SQL Anywhere 11 database in a single transaction. The logic shown here
is an attempt to use the linked server feature to "push" the rows;
same direction, different syntax.

The code follows; 4G of RAM is exhausted after three or four
executions of the EXECUTE copy_mss_t2:

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

--
Breck Carter http://sqlanywhere.blogspot.com/

RisingRoad SQL Anywhere and MobiLink Professional Services
breck.carter (AT) risingroad (DOT) com