This document describes a continuing problem we have been experiencing using
a SQL Server linked server connection to an IBM UniVerse data source via
ODBC.
This issue manifests itself when querying the linked server, and appears to
be related to an overall gradual loss of memory. We have tested using both
our production server as well as a virtual test environment and see the same
results.
>> Universe environment
Universe version 10.2.10
Microsoft Windows Server 2003 R2
Service Pack 2
In our production environment we are using the following versions:
Microsoft SQL Server Management Studio
9.00.4035.00
Microsoft Analysis Services Client Tools
2005.090.4035.00
Microsoft Data Access Components (MDAC)
2000.086.3959.00 (srv03_sp2_rtm.070216-1710)
Microsoft MSXML
2.6 3.0 6.0
Microsoft Internet Explorer
7.0.5730.13
Microsoft .NET Framework
2.0.50727.3607
Operating System
5.2.3790 (Windows Server 2003 R2 with 32GB RAM)
We use the UniOLEDB Provider for the linked server and we have checked for
the latest UniOLEDB drivers. We have tried changing the MemToLeave area by
setting the –g option to 3072 - which delays the problem but does not stop
it from occurring, it just happens less frequently.
In our virtual test environment, we are using the following versions:
Microsoft SQL Server Management Studio
9.00.4035.00
Microsoft Analysis Services Client Tools
2005.090.4035.00
Microsoft Data Access Components (MDAC)
2000.086.3959.00 (srv03_sp2_rtm.070216-1710)
Microsoft MSXML
2.6 3.0 6.0
Microsoft Internet Explorer
8.0.6001.18702
Microsoft .NET Framework
2.0.50727.3607
Operating System
5.2.3790 (Windows Server 2003 R2 with 2GB RAM)
In test, we have utilized the following query to monitor memory usage while
performing large queries against the linked server:
;WITH VAS_Summary AS
(
SELECT
Size = VAS_Dump.Size,
Reserved = SUM(CASE(CONVERT(INT, VAS_Dump.Base)^0) WHEN 0 THEN 0 ELSE 1
END),
Free = SUM(CASE(CONVERT(INT, VAS_Dump.Base)^0) WHEN 0 THEN 1 ELSE 0 END)
FROM
(
SELECT CONVERT(VARBINARY, SUM(region_size_in_bytes)) [Size],
region_allocation_base_address [Base]
FROM sys.dm_os_virtual_address_dump
WHERE region_allocation_base_address <> 0x0
GROUP BY region_allocation_base_address
UNION
SELECT CONVERT(VARBINARY, region_size_in_bytes) [Size],
region_allocation_base_address [Base]
FROM sys.dm_os_virtual_address_dump
WHERE region_allocation_base_address = 0x0
)
AS VAS_Dump
GROUP BY Size
)
INSERT INTO tbl_chkMemToLeaveSpace
SELECT GETDATE(), SUM(CONVERT(BIGINT,Size)*Free)/1024,
CAST(MAX(Size) AS BIGINT)/1024
FROM VAS_Summary
WHERE Free <> 0
The results of this monitoring have always shown that we start with
approximately 836132KB TotalAvailMem and 122216KB MaxFreeSize. Then, as the
queries run, the amount of available and free memory have continually
dropped until there is only 2000-3000KB free, and then we see any one of the
following error message(s) during any attempted query execution: (In this
example – when we received this message, our monitoring showed 43248KB
TotalAvailMem and 2728KB MaxFreeSize)
OLE DB provider "MSDASQL" for linked server "pauvqa1" returned message
"[IBM][UVODBC][0000000]Unable to allocate sufficient memory!".
Msg 7330, Level 16, State 2, Line 1
Cannot fetch a row from OLE DB provider "MSDASQL" for linked server
"pauvqa1".
After trying to query a second time:
Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "MSDASQL" for linked server "pauvqa1" reported an error.
The provider reported an unexpected catastrophic failure.
Msg 7330, Level 16, State 2, Line 1
Cannot fetch a row from OLE DB provider "MSDASQL" for linked server
"pauvqa1".
Additionally, we have seen the following series of error messages when
performing the same test of using SQL queries, combined with utilizing a
custom Access ADP (access data project) that calls stored procedures relying
on the Uni OLEDB driver as part of the programming. In this example, we saw
the following error message in SQL Server:
Then we tried the Access ADP functionality, which calls a stored procedure
that relies on the UniVerse linked server (Uni OLEDB) and got the following
series of error messages:
We have tried the DBCC options for clearing the various caches but it did
not help. The only way we have been able to resolve the issue and restore
the memory is to restart the MSSQLSERVER service.
--
View this message in context:
http://old.nabble.com/SQL-server-to-Universe-via-ODBC-problems-tp28843937p28843937.html
Sent from the U2 - Users mailing list archive at Nabble.com.
_______________________________________________
U2-Users mailing list
U2-Users@listserver.u2ug.org
http://listserver.u2ug.org/mailman/listinfo/u2-users
