Today my colleague had issue that SQL server instance is not starting on.
It is a SQL 2016 and had seven instances with 24 GB of total OS RAM. Same again, one of the SQL instance memory configured incorrectly. It is a alwaysON secondary replica.
We know where we have to look, when we cannot start SQL service.
Run — Eventvwr — Windows logs –Application and system.
One more: Open SQL server errorlog file in notepad and review as well.
You can get the error log location in the configuration manger — startup parameters.
2019-01-30 04:43:14.73 spid6s Error: 701, Severity: 17, State: 123.
2019-01-30 04:43:14.73 spid6s There is insufficient system memory in resource pool ‘internal’ to run this query.
2019-01-30 04:43:14.73 Server Error: 17300, Severity: 16, State: 1. (Params:). The error is printed in terse mode because there was error during formatting. Tracing, ETW, notifications etc are skipped.
2019-01-30 04:43:14.73 Server Error: 17300, Severity: 16, State: 1. (Params:). The error is printed in terse mode because there was error during formatting. Tracing, ETW, notifications etc are skipped.
2019-01-30 04:43:14.74 Server Error: 17300, Severity: 16, State: 1. (Params:). The error is printed in terse mode because there was error during formatting. Tracing, ETW, notifications etc are skipped.
2019-01-30 04:43:14.74 Server Error: 17300, Severity: 16, State: 1. (Params:). The error is printed in terse mode because there was error during formatting. Tracing, ETW, notifications etc are skipped.
2019-01-30 04:43:14.74 Server Error: 17300, Severity: 16, State: 1. (Params:). The error is printed in terse mode because there was error during formatting. Tracing, ETW, notifications etc are skipped.
2019-01-30 04:43:14.74 Server Error: 17300, Severity: 16, State: 1. (Params:). The error is printed in terse mode because there was error during formatting. Tracing, ETW, notifications etc are skipped.
2019-01-30 04:43:14.74 spid6s SQL Server shutdown has been initiated
2019-01-30 04:43:14.74 spid6s Error: 19032, Severity: 10, State: 1. (Params:). The error is printed in terse mode because there was error during formatting. Tracing, ETW, notifications etc are skipped.
To fix:
We need to start SQL service in minimal configuration -f or single user mode -m and have to change the memory setting by GUI or T-SQL.
By GUI you have connect normal and go to property and change max and min memory and remove that startup flag and recycle the SQL service.
By T-SQL cancel the connection and click new query window and use following T-SQL
sp_configure ‘show advan’,1;reconfigure
Go
Sp_configure ‘max server memory (MB)’,’1024′;reconfigure
