Hello Some thoughts and some code for you.
1. Are you running SQL Server 64bit on 64bit Windows? With running SQL at -g2048 you would almost have to be. 2048 is a very high value for the virtual address space, but I guess you had to do it to keep the application running. It's not a good long term solution as it just masks the underlying problem. 2. You are on the right track trying to analyze the VAS. The code you posted helps you do this. VAS is the area of SQL Server memory reserved for "extra stuff" ie. linked server queries, extended stored procedures, CLR .net runtime etc. 3. One possibility is that the uniVerse driver is 32bit (check this out). It may be some strange interaction with running 32bit WOW (windows on windows) is causing this. Again, I am assuming you are running 64bit windows. 4. I stongly suspect the underlying problem is that the UniVerse driver is comsuming SQL Server VAS and not releasing it and/or fragmenting it. We looked at UniOledb driver a few years ago and couldn't get it to work reliably so we gave up. One solution we were able to get to work is to bypass the linked server and use SQL 2000 DTS to move the data out of UV and into SQL. Given the demise of SQL 2000 in our environment, we now run the old DTS packages from the command line. It's not pretty but it works. We were never able to get SQL 2005 SSIS to work with the uniVerse ODBC driver. let me know if you want some examples. Here's some code that may help you further analyze your VAS and what is consuming it (I cannot take credit for writing this code): --- Find out how dlls map to our VAS select base_address, name from sys.dm_os_loaded_modules ORDER BY NAME -- analysis of the memory clerks select type, sum(virtual_memory_reserved_kb) as [VM Reserved], sum(virtual_memory_committed_kb) as [VM Committed], sum(awe_allocated_kb) as [AWE Allocated], sum(shared_memory_reserved_kb) as [SM Reserved], sum(shared_memory_committed_kb) as [SM Committed], sum(multi_pages_kb) as [MultiPage Allocator], sum(single_pages_kb) as [SinlgePage Allocator] from sys.dm_os_memory_clerks group by type --raw vas dump SELECT * FROM sys.dm_os_virtual_address_dump -- try and Map loaded modules into the VAS -- this may help you tell what's consuming the VAS SELECT lm.base_address, lm.file_version, lm.[description], lm.NAME [region_base_address], ([region_size_in_bytes]/1024) AS 'Region_KB' FROM sys.[dm_os_loaded_modules] lm INNER JOIN sys.[dm_os_virtual_address_dump] vad ON lm.base_address = vad.region_base_address ORDER BY region_kb For more info on these SQL dmv's google them or look em up on books online. Hope this helps. Post back as to how it goes. Again, I'm pretty sure it's the driver that's causing the memory leak. Maybe this will give you ammo to take to Rocket software support. Regards, Craig Purnell Baker Hostetler LLP Cleveland , OH On Mon, Jun 14, 2010 at 7:30 AM, ptnaman <[email protected]> wrote: > > > > +++++++++ > We have used the –G option in the SQL Server Service startup to increase to > 2048 (2 gig) . the protected memory pool. > > The server is a 16 processor Windows 2003 server with 32 gig of memory - > 22 > gig is allocated to SQL server. > > Increasing the –g option from the default 348 to 2048 allows us to run a > day’s worth of transactions (most usually) and we restart the sql server > service nightly as a scheduled job to flush out this memtoleave area. > > +++++++++ > > We use the following code to interrogate the MemtoLeave region of memory - > 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 > ++++++ > > This has given us insight into the 2 gig protected memory pool… it seems to > us that there is a memory leak. Only restarting the SQL Server Service > remediates this collapse. The collapse doesn’t tear down sql server - but > no calls using the linked servers to UV will work without a restart. > > --- > > > _______________________________________________ U2-Users mailing list [email protected] http://listserver.u2ug.org/mailman/listinfo/u2-users
