Sometimes DBA receives SQL Alert System -17.
I have given below the detailed description and solution for this issue. Hope
it will be helpful for DBAs.
Alert
SQL Server Alert System: '17 - Insufficient
Resources' occurred on \\Server1
DESCRIPTION: There is insufficient system
memory in resource pool 'internal' to run this query.
Issue Description
SQL Server was unable to run a new system
task, either because there is insufficient memory or the number of configured
sessions exceeds the maximum allowed in the server. Verify that the server has
adequate memory. Use sp_configure with option 'user connections' to check the
maximum number of user connections allowed. Use sys.dm_exec_sessions to check
the current number of sessions, including user processes.
There
is insufficient system memory in resource pool 'internal' to run this query.
There
was a memory allocation failure during connection establishment. Reduce
nonessential memory load, or increase system memory. The connection has been
closed.
This may also due to Virtual Address Space
pressure, specifically in a region of VAS that is reserved at initial startup
of SQL Server known as MemToLeave.
Recommended Solution
1.
Most of the timings, while this issue occurs,
we would not able to access the Database engine through SSMS. In this case,
Connect Database Engine through command line prompts and identifies the
blocking process and kills it with appropriate approvals.
2.
Try increasing the memory capacity if it is
lower than threshold value.
3.
We will try upgrading the SQL Server Version
to SQL Server 2008 R2 Service Pack 1
if it is SQL Server 2008 R2 RTM.
It is a Microsoft recommendation to avoid memory pressure. Reference
links are given below:
4.
SQL Server Linked Server, process conflict,
Power Shell to connect to SQL, executing SQL query using OSQL in command prompt
and ad-hoc INSERTs cause a rapid memory leak which eventually causes the server
to become non-responsive.
5.
Plan to schedule the database engine refresh
once in a month for better server performance which may avoid memory pressure
issue.
6.
High availability solution [ex. Clustering]
can be implemented to keep database to be available online at all the scenarios
irrespective of any issue occurs.
No comments:
Post a Comment