We have been experiencing both “Out of memory” and “fatal catastrophic
failure” messages when querying a linked server setup to an IBM UniVerse
ODBC connection. We have tested using both our production server as well as
a virtual test environment and see the same results.
>> Universe environment
Windows Server 2003 R2 Service Pack 2
Universe 10.2.10
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 also use the UniOLEDB Provider for the linked server. We have checked for
the latest UniOLEDB drivers. We have tried changing the MemToLeave area by
setting the –g option to –g2048 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 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".
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.
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
--
View this message in context:
http://old.nabble.com/SQL-server-to-Universe-via-ODBC-problems-tp28844105p28844105.html
Sent from the U2 - Users mailing list archive at Nabble.com.
_______________________________________________
U2-Users mailing list
[email protected]
http://listserver.u2ug.org/mailman/listinfo/u2-users
