6

We're running a 2014 SQL Server. For the past couple of years, the server generates memory dumps with no rhyme or reason, every 2-7 days, locking up most processes, and forcing us to restart the SQL service to get it going.

Here's snippet of the error file:

Exception Address = 00007FFAD7FCBAA2 Module(combase+000000000003BAA2)
Exception Code    = c0000005 EXCEPTION_ACCESS_VIOLATION
Access Violation occurred reading address 00000000D8063158
Input Buffer 74 bytes -

Anything else we should explore?
Or is our ONLY option to have Microsoft analyze the .MDMP file(s) ?

Points to mention:

  • We have it patched to the latest Service Package, with the latest Cumulative & GDR updates. Hasn't made a difference

    Microsoft SQL Server 2014 (SP3-CU4-GDR) (KB5014164) - 12.0.6439.10 (X64)   
    
  • There's no specific SQL statements/stored procedures that correlate to when these memory dumps occur.

  • The SQL database files run on a seperate Hard drive than the OS. It's a 1TB drive with 250GB free at any given time.

  • The Machine is a Windows Server 2012 R2, Virtual Machine

  • CHKDSK does not find any errors on the OS & Data drives.

  • DBCC checks go through without issues.

  • OPENQUERY & OPENROWSET functions are used quite frequently, using the latest ACE & ODBC drivers.

  • If I query sys.dm_server_memory_dumps, here's the record count by date:

       creation_time  QTY
       2022-09-13 1
       2022-09-09 38
       2022-09-07 11
       2022-09-06 21
       2022-09-04 1
       2022-08-25 2
       2022-08-24 11
       2022-08-23 7
       2022-08-22 1
       2022-08-21 1
       2022-08-17 9
       2022-08-09 7
       2022-07-29 1
       2022-07-27 1
       2022-07-22 1
    
2
  • 1
    Welcome to the DBA.SE community. What a well written question. Am I correct in assuming that the errors started occurring on the 22nd July? Are there (were there) any errors in the Windows Eventlog that would correlate with the Dumps occurring?
    – John K. N.
    Commented Sep 18, 2022 at 8:22
  • Hi @JohnK.N.- This has been happening for years. The snippet above is just the count for the past 60 days. the memory dumps do correlate with the Windows Event log, however it's generic "The SQL Server (MSSQLSERVER) service terminated unexpectedly. It has done this 1 time(s). The following corrective action will be taken in 60000 milliseconds: Restart the service." Commented Sep 18, 2022 at 17:46

2 Answers 2

4

OPENQUERY & OPENROWSET functions are used quite frequently, using the latest ACE & ODBC drivers.

Sorry. That's not supported, and now you know why.

The Office System Drivers are only supported under certain scenarios, including:

  1. Desktop applications which read from and write to various files formats including Microsoft Office Access, Microsoft Office Excel and text files.
  2. To transfer data between supported file formats and a database repository, such as SQL Server. For example, to transfer data from an Excel workbook into SQL Server using the SQL Server Import and Export Wizard or SQL Server Integration Services (provided the SSIS jobs run in the context of a logged-on user with a valid HKEY_CURRENT_USER registry hive).

https://www.microsoft.com/en-us/download/details.aspx?id=54920

Remove them in favor of SSIS packages or similar that run outside SQL Server in short-lived processes.

You can use OPENQURERY etc against the SSIS Data Streaming Destination.

Alternatively you can try getting the OleDb drivers running in out-of-process mode. See eg Setting up linked servers with an out-of-process OLEDB provider

3

Just a follow-up on this - We had our Memory dump files analyzed by Microsoft, and it turns out the OPENQUERY and OPENROWSET SQL statements have been the culprit.

  1. We updated the Microsoft Access Database Engine drivers (both 2010 and 2016) to the latest versions
  2. Many of the comments within OPENQUERY statements used a double hyphen --, which for some odd reason, sporadically causes a crash/memory dump with the Oracle OLEDB providers. We replaced all double-hyphen synatx, with the /* */ comment

We've seen incredible stabile since.

1
  • 3
    --Double-hyphen comments are the devil's work
    – Hannah Vernon
    Commented Nov 1, 2022 at 20:33

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.