大网数据广告活动
数据库与应用安全

SQL Server dump files are generated when a linked server query encounters exceptions

· 52CE 我爱测

This article discusses how to fix an assert dump-related SQL Server issue in which a linked server query references an LOB data type and raises an exception.

This article helps you resolve an issue that causes Microsoft SQL Server to generate an asset dump file and the SQL Server service to become unresponsive.

Symptoms

Consider the following scenario:

  • SQL Server generates an assert dump file. This causes the SQL Server service to become unresponsive.

  • In the SQL Server error log, you observe the following assert expression: pilb->m_cRef == 0. For more information about asserts and how to troubleshoot them, see MSSQLSERVER_3624 - SQL Server.

    2023-03-14 06:12:44.83 spid54 * Location: memilb.cpp:1836 2023-03-14 06:12:44.83 spid54 * Expression: pilb->m_cRef == 0

  • SQL Server becomes unresponsive during the dump file process.

Cause

The following are some conditions that can affect when the SQL Server dump file is generated:

  • A remote query is run by using the EXEC AT command. For example:

    DECLARE @test NVARCHAR(MAX) = N'ABC'
    EXEC ('SELECT * FROM Customers.dbo.CustomerInformation WHERE CustomerId = ?', @test) AT [YourRemoteServer];
    
  • A large object (LOB) argument is passed to the query. For more information about LOB data types, see Data types (Transact-SQL) - SQL Server.

In the previous example, the NVARCHAR(MAX) variable is passed as a query parameter. One of the underlying requests that are submitted to the linked server fails.

You can also reproduce the issue by running the following query:

DECLARE @test NVARCHAR(MAX) = N'msdb' 
EXEC ('---SELECT * FROM sys.databases where name = ?', @test) AT [<server>\<instance>]

To avoid this issue, use nonblob (BLOB) data type in your database design or code:

DECLARE @test NVARCHAR(5) = N'msdb' 
EXEC ('---SELECT * FROM sys.databases where name = ?', @test) AT  [<server>\<instance>]

Warning: If you run this code in your environment, you are likely to encounter a dump file that will cause an outage. Run at your own risk.

Status

Microsoft is still investigating this issue to identify a long-term fix. Until a fix is available, you can try the workaround that's provided in the next section.

Workaround

To work around this issue, change all LOB (Large Object) data types to fixed length data types that the input data length supports. For example, convert NVARCHAR(MAX) to NVARCHAR(200)).

Source: Microsoft Learn / IT Pro troubleshooting by Microsoft and contributors (upstream author metadata: JamesFerebee). Documentation licensed CC BY 4.0. Code samples are covered by the MIT License. Converted from Markdown, with links normalized and embedded media omitted.

资料来源与适用范围

资料来源
Microsoft Learn / IT Pro troubleshooting
适用范围
See source applicability · Source date 02/16/2024
内容语言
中文;命令、路径和错误标识保留技术原文
本地收录日期
2026-11-15
授权说明
CC BY 4.0 (documentation), MIT (code samples)

正文与所需资源由本站本地存储。查看来源及更新记录 ↗

请按适用版本和实际日志确认原因;本站没有对每一种系统或设备逐项执行文中的操作。