Overview
Problem:
Windows update has occurred and now SQL instance won't start with the following error
Navigating to the following errorlog
C:\\Program Files\\Microsoft SQL Server\\MSSQL15.SQLDEV2019\\MSSQL\\Log will possible result in the following error
2022-06-15 12:46:41.43 Server 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.
2022-06-15 12:46:41.43 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.
2022-06-15 12:46:41.43 spid10s Error: 912, Severity: 21, State: 2.
2022-06-15 12:46:41.43 spid10s Script level upgrade for database 'master' failed because upgrade step 'msdb110_upgrade.sql' encountered error 701, state 123, severity 25. This is a serious error condition which might interfere with regular operation and the database will be taken offline. If the error happened during upgrade of the 'master' database, it will prevent the entire SQL Server instance from starting. Examine the previous errorlog entries for errors, take the appropriate corrective actions and re-start the database so that the script upgrade steps run to completion.
2022-06-15 12:46:41.43 spid10s Error: 3417, Severity: 21, State: 3.
2022-06-15 12:46:41.43 spid10s Cannot recover the master database. SQL Server is unable to run. Restore master from a full backup, repair it, or rebuild it. For more information about how to rebuild the master database, see SQL Server Books Online.
2022-06-15 12:46:41.43 spid10s SQL Trace was stopped due to server shutdown. Trace ID = '1'. This is an informational message only; no user action is required.
Solution:
There is a Bat file in the Screenconnect Toolbox, that will stop the SQL service, set the correct Min and max memory to 1024 and 4086 and restart the service again
This needs to be run as Elevated
BAT file Script:
ECHO OFF
FOR /F "tokens=2* delims= " %%A IN ('reg query "HKEY_LOCAL_MACHINE\\SOFTWARE\\Microsoft\\Microsoft SQL Server" /v InstalledInstances') DO SET SQL_INT=%%B
FOR /F "tokens=2* delims= " %%A IN ('reg query HKCU\\SOFTWARE\\Backoffice /v SQL_Server') DO SET SQL_SERVER=%%B
FOR /F "tokens=2* delims= " %%A IN ('reg query HKCU\\SOFTWARE\\Backoffice /v SQL_DSN') DO SET SQL_DSN=%%B
ECHO %SQL_INT%
ECHO %SQL_SERVER%
ECHO %SQL_DSN%
if not exist "C:\\Bepoz\\Scripts\\" mkdir "C:\\Bepoz\\Scripts\\"
@echo off
(
ECHO sp_configure 'show advanced options', 1;
ECHO GO
ECHO RECONFIGURE;
ECHO GO
ECHO sp_configure 'min server memory', 1024;
ECHO GO
ECHO RECONFIGURE;
ECHO GO
ECHO sp_configure 'max server memory', 4096;
ECHO GO
ECHO RECONFIGURE;
ECHO GO
)>C:\\Bepoz\\Scripts\\SQLmem.sql
NET STOP MSSQL$%SQL_INT%
NET START MSSQL$%SQL_INT% /T902
SQLCMD -S %SQL_SERVER% -U bepoz -P do4safet -d %SQL_DSN% -i C:\\Bepoz\\Scripts\\SQLmem.sql
NET STOP MSSQL$%SQL_INT%
NET START MSSQL$%SQL_INT%
EXIT
Related Articles:
TBC