SQL Server Error 19728, Severity 17, is typically encountered when issues related to memory allocation occur during the execution of a query or operation. This might occur due to insufficient system resources (e.g., RAM) available to perform the requested task in SQL Server. Errors with severity level 17 generally indicate problems that occur outside user control but influence SQL Server's ability to execute commands. For this specific issue, the message typically indicates either hardware resource shortages or incorrect configuration settings within SQL Server, leading to resource contention.
Common causes:
- Memory Pressure: The server lacks sufficient memory for current workloads due to inadequate physical resources.
- Inappropriate Max Memory Configuration: `max server memory` settings for your instance may be too restrictive.
- Resource Governor Settings (if enabled): configuring inappropriate limits on workload groups.
- Non-optimized Queries: causing high consumption of tempdb or excessive use of resources like worktables and hash joins.
- Competing processes: consuming high system memory, leaving little for SQL Server operations.
Check Free System Resources Ensure that your OS has enough free memory available for stacking demands from both internal SQL components and external applications/services. Verify buffer pool utilization and total allocated/free pages.
SELECT * FROM sys.dm_os_sys_memory;
Certain tools such as AI-DBA for SQL Server can assist you with a proactive administration and mitigate such errors.