0X000009D5: SQL Server memory allocation failure fix

You're running a query that's been fine for months. Suddenly, SQL Server throws error 0X000009D5. The exact trigger? It happens when SQL Server asks Windows for a memory grant that exceeds what's available. I've seen this most often during heavy ETL processes or large index rebuilds on servers with 16 GB RAM or less. Sometimes it pops up right after a memory-intensive patch, like a cumulative update for SQL Server 2019.

Why this error shows up

The root cause is simple: SQL Server's memory manager tries to allocate a buffer or lock page, and the OS says 'nope, not enough commit charge left.' This isn't a corrupt database or bad query. It's a resource starvation issue. The OS has run out of virtual memory (page file + physical RAM). Or SQL Server's max server memory setting is too high, leaving nothing for the OS and other apps.

I've also seen this when someone enabled Lock Pages in Memory (LPIM) without adjusting the max memory setting. LPIM prevents Windows from paging SQL Server's memory out, which sounds great—until it backfires and the OS can't reclaim anything during a crunch.

Step-by-step fix

Here's the order I'd follow. Skip Nonesense—do these as written.

Step 1: Check current memory usage

Open SQL Server Management Studio (SSMS). Run this query to see what SQL Server thinks it's using:

SELECT 
  physical_memory_in_use_kb / 1024 AS mem_in_use_mb,
  locked_page_allocations_kb / 1024 AS locked_pages_mb,
  virtual_address_space_reserved_kb / 1024 AS vas_reserved_mb,
  virtual_address_space_committed_kb / 1024 AS vas_committed_mb
FROM sys.dm_os_process_memory;

If virtual_address_space_committed_kb is close to your max server memory setting (in MB), you're right at the edge.

Step 2: Check OS memory pressure

Open Task Manager on the Windows Server. Go to the Performance tab. Look at the Memory section. If Available memory is under 512 MB, or the Commit Charge (Total) is larger than your physical RAM + page file, you've hit the wall. Also check the Processes tab for other memory hogs—SQL Server's not always the only culprit.

Step 3: Lower max server memory

This is the real fix 90% of the time. I always leave at least 2 GB for the OS plus 1 GB per 8 GB of RAM for other services. For a server with 16 GB total, set max server memory to 12 GB. Here's how:

1. In SSMS, right-click the server name and choose Properties.
2. Go to the Memory page.
3. Set 'Maximum server memory (in MB)' to 12288 (for 12 GB).
4. Set 'Minimum server memory (in MB)' to 0 unless you've got a reason to lock it.
5. Click OK.

Alternatively, use T-SQL:

EXEC sp_configure 'max server memory (MB)', 12288;
RECONFIGURE;

Step 4: Check Lock Pages in Memory

If SQL Server has the LPIM privilege (common on enterprise editions), it can bypass OS paging. That can cause issues. Run this to check:

SELECT 
  locked_page_allocations_kb / 1024 AS locked_mb
FROM sys.dm_os_process_memory;

If locked_mb is non-zero, consider revoking LPIM from the SQL Server service account (via Local Security Policy > User Rights Assignment > Lock pages in memory). Then restart SQL Server. I've seen this single change fix the error on SQL Server 2016 Standard.

Step 5: Increase page file size

As a last resort, increase the Windows page file. I'm not a fan of this, because it masks the problem and can kill disk performance on SSDs. But if you can't lower memory settings right now, it works. Set the initial and maximum size to 1.5x your physical RAM. Reboot the server.

If it still fails

You've done steps 1-5, but the error's back. Here's what to check:

• Resource Governor: If you've got Resource Governor configured, check that the default pool's memory is set too low. The min memory per query can starve the system. Disable Resource Governor temporarily to test.
• Memory leaks in other apps: Use RAMMap from Sysinternals to see if a non-SQL process is leaking nonpaged pool memory. I've traced this to bad third-party antivirus drivers.
• Hardware issues: Faulty RAM can cause memory allocation failures. Run Windows Memory Diagnostic (mdsched.exe) to test. Yes, that's rare, but I've seen it on Dell PowerEdge servers with bad DIMMs.

If you're still stuck, check the SQL Server Error Log (in SSMS, under Management) for messages before the 0X000009D5. They'll often point to a specific query or stored procedure. You might need to optimize that query to use less memory (like adding indexes or breaking it into batches).

That's it. This error is infuriating because it looks like a database problem when it's really an OS memory problem. One setting change and you're back to normal.