SQL Server 2019 Long Async API Call

What this check looks for

The instance being SQL Server 2019 at a build known to log the message WARNING Long asynchronous API Call excessively. CU27 is the build associated with this behavior. The check is skipped on Amazon RDS.

Why it matters

The message itself is usually harmless. The volume of it is the problem.

SQL Server writes this warning when an asynchronous call to the operating system takes longer than an internal threshold. On the affected build it is emitted far more often than the condition warrants, and on a busy instance that means thousands of entries a day.

What that costs you:

• The error log becomes unusable. The error log is where you look when something has gone wrong: a failed login, an I/O error, a corruption report, a stack dump. When it is 95 percent one repeated warning, those entries are effectively invisible, and the first time that matters is during an incident.
• Log files roll constantly. SQL Server keeps a limited number of error log files, six by default. When one fills in hours rather than weeks, the retained history shrinks from months to days, so the evidence of what happened last Tuesday is gone.
• Disk space, on an instance where the log directory is not large.
• Any tool that reads the error log slows down, including the monitoring that reads it to find real problems. sp_readerrorlog on a very large log file is slow and takes locks on the log while it reads.

The distinction to hold onto: a small number of these messages can be a genuine I/O latency signal worth investigating. A flood of them on this specific build is a logging defect. Deciding which you have is the first step, and the I/O stall query below is how you tell.

How to confirm it yourself

Your build, which determines whether this applies:

SELECT SERVERPROPERTY('ProductVersion')     AS [build],
       SERVERPROPERTY('ProductUpdateLevel') AS [cu_level],
       SERVERPROPERTY('ProductMajorVersion') AS [major];

A major version of 15 is SQL Server 2019.

How many of these messages you actually have:

CREATE TABLE #errorLog (
    [LogDate]     DATETIME,
    [ProcessInfo] NVARCHAR(100),
    [Text]        NVARCHAR(MAX)
);

INSERT INTO #errorLog
EXEC sp_readerrorlog 0, 1, N'Long asynchronous';

SELECT COUNT(*)      AS [message_count],
       MIN([LogDate]) AS [first_seen],
       MAX([LogDate]) AS [last_seen]
  FROM #errorLog;

SELECT CAST([LogDate] AS DATE) AS [day], COUNT(*) AS [messages]
  FROM #errorLog
 GROUP BY CAST([LogDate] AS DATE)
 ORDER BY [day] DESC;

DROP TABLE #errorLog;

The per day count is what decides whether this is noise or a signal. A handful a day is worth investigating as real latency. Thousands a day on CU27 is the defect.

The log files themselves, with their sizes, so you can see how fast they are rolling:

EXEC sys.sp_enumerrorlogs;

That returns each retained log file with its size and the date range it covers. The path, if you want to look at the directory:

SELECT SERVERPROPERTY('ErrorLogFileName') AS [error_log_path];

And whether the underlying I/O is genuinely slow, which is the question the message claims to be answering:

SELECT DB_NAME(vfs.[database_id])                              AS [database_name],
       mf.[physical_name],
       vfs.[num_of_reads],
       vfs.[io_stall_read_ms] / NULLIF(vfs.[num_of_reads], 0)  AS [avg_read_ms],
       vfs.[num_of_writes],
       vfs.[io_stall_write_ms] / NULLIF(vfs.[num_of_writes], 0) AS [avg_write_ms]
  FROM sys.dm_io_virtual_file_stats(NULL, NULL) AS vfs
 INNER JOIN sys.master_files AS mf WITH (NOLOCK)
         ON mf.[database_id] = vfs.[database_id] AND mf.[file_id] = vfs.[file_id]
 ORDER BY [avg_read_ms] DESC;

Average read or write latency in single digit milliseconds alongside thousands of warnings means the warnings are not about your storage. Latency in the hundreds means they are, and then the storage is the finding.

How to fix it

Move off the affected build. There is no supported setting that suppresses the message.

1. Apply a later cumulative update for SQL Server 2019, or move to a later major version. This is the actual fix and it is the same maintenance window you would use for any CU.
2. Confirm after patching by cycling the log and checking whether the messages resume:

EXEC sp_cycle_errorlog;

Then look again a day later.

In the meantime, make the log survivable:

Increase the number of retained error log files so cycling does not throw away your history. The default of 6 is far too few when the log rolls daily:

EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE',
     N'Software\Microsoft\MSSQLServer\MSSQLServer',
     N'NumErrorLogs', REG_DWORD, 30;

Cycle the log on a schedule so no single file becomes enormous. A nightly Agent job running sp_cycle_errorlog keeps each file to one day, which also makes them readable:

EXEC msdb.dbo.sp_add_job @job_name = N'Cycle Error Log';
EXEC msdb.dbo.sp_add_jobstep
     @job_name = N'Cycle Error Log',
     @step_name = N'Cycle',
     @subsystem = N'TSQL',
     @database_name = N'master',
     @command = N'EXEC sp_cycle_errorlog;';

Read the log with a filter rather than wholesale, so the noise does not obscure the search:

-- errors only, skipping the known noise
EXEC sp_readerrorlog 0, 1, N'Error';
EXEC sp_readerrorlog 0, 1, N'failed';
EXEC sp_readerrorlog 0, 1, N'corrupt';

Do not filter this message out at the monitoring layer and forget about it. The reason to care is that a genuine I/O problem produces the same message, and a suppression rule hides both. Patching removes the noise and leaves the signal.

And check the storage anyway, once. If the I/O stall query shows real latency, you have two findings rather than one, and the slow I/O checks cover the second.

How long it takes

About half an hour to confirm the build and put the log retention and cycling in place. The patch itself is a normal cumulative update window.

Related reports

Related checks

Frequently asked questions

Can I turn the message off? Not through a supported setting. The fix is a later build.

Is the warning telling me something real? It can. A small number of these alongside high I/O latency is a genuine storage signal. A flood of them on CU27 with single digit latency is the logging defect.

How many error log files should I keep? The default of 6 is too few on any instance. 30 is a reasonable setting, and combined with a nightly cycle it gives you a month of readable history.

We are on Amazon RDS. The check is skipped there, because the error log handling and the patching path are both managed by the platform.