SQL SERVER 2019 Linked Server ODBC MSDASQL ran out of memory

Question

Hi,

I am running a VM with Windows Server 2019 + MS SQL SERVER 2019 - 64bit, 8Core 32GbRAM, 500Gb Disk Space

I am facing some issues with SQL Server 2019, using ODBC Driver (provider MSDASQL) running out of memory.

I am running OPENQUERY like SELECT * FROM OPENQUERY('remote__server', 'select column1, column2, column3 from schema.target_table where created__at>''2023-03-14 00:00:00'' and ''2023-03-15 23:59:59''');

This query worked a few times and then I started to get the "provider ran out of memory" error message

Msg 7399, Level 16, State 1, Line 2

The OLE DB provider "MSDASQL" for linked server "remote__server_" reported an error. The provider ran out of memory.

My intention is to collect some data from the remote_server and store it in a table in SQL Server. I was able to get about 100k records from the remote table and the provider ran out of memory started.

I am able to run the query using the interactive SQL from the ODBC driver outside the SQL Server which makes me believe there is some buffer or cache full in the SQL Server provider because I noticed each time doing the queries tried to set 6 months period then, 3 months...1month and now barely a 24hours period work.

I have tried to clean the SQL server cache and buffer but did not work.

Also, the max memory is set to 20GbRAM and is using only 4Gb so the physical memory should not be a problem here.

Does someone has some suggestion or went through the same issue before?

PS: I have tried to restart the SQL Server and the VM multiple times.

Answer 1

I found out the provider was running out of memory due to the source table column length.

The provider was not able to fetch some of the rows which have around 8000 characters (source limit) so I have applied the LEFT(column, 8000) as column statement in the OPENQUERY SELECT and the ran out of memory is gone.

This is an example of a query that is working perfectly now

OPENQUERY like SELECT * FROM OPENQUERY('remote_server', 'select column1, LEFT(column2, 8000) AS column2, column3 from schema.target_table where created_at>=''2023-03-14 00:00:00'' and created_at<=''2023-03-15 23:59:59''');

Answer 2

First, how have you configured the provider? Allow to run in-process or not? Both settings come with its own set of issues.

Next, these situations are very difficult to troubleshoot, because there are so many components. There is SQL Server, there is the remote data source, and in between that there is the ODBC provider and the OLE DB provider. And, no, MSDASQL is not part of SQL Server, but it ships with the OS.

In this case, MSDASQL says that it is running out of memory, but that could be because the ODBC driver is misbehaving.

Answer 3

Hi @Bruno Matiucci

I haven't had such a problem, and based on your error, I found the similar thread: https://stackoverflow.com/questions/943240/sql-server-linked-server-query-running-out-of-memory.

But these threads are relatively old, and I don't know if these apply to your events, maybe you can use them as a reference.

As Erland said, these situations are difficult to troubleshoot, and you can check out this official documentation to troubleshoot out-of-memory issues in SQL Server: https://learn.microsoft.com/en-us/troubleshoot/sql/database-engine/performance/troubleshoot-memory-issues#internal-memory-pressure-not-coming-from-sql-server.

Best regards,

Aniya