SQL Server Inaccessible After Maximum Memory Set Too Low — DAC Recovery

Indicators

• SQL Server service is running but all normal client connections fail or time out
• Unable to log in via SQL Server Management Studio after a recent Maximum Memory configuration change
• Maximum Server Memory recently set to an extremely low value (e.g., 15 MB)
• SSMS connection attempts hang or return resource-related errors
• No error in SQL Server error log indicating service crash — service appears healthy but unresponsive to logins

Likely causes

• Maximum Server Memory (max server memory MB) set to an unrealistically low value, starving SQL Server of memory required to authenticate and serve normal connections
• Accidental misconfiguration via SQL Server Management Studio Server Properties dialog or direct T-SQL sp_configure without verifying the unit (MB vs GB)
• Operator error when intending to reduce memory pressure — e.g., entering 15 instead of 15360 (15 GB in MB)

Diagnostic steps

1. Confirm the SQL Server service is still running via Services.msc or 'Get-Service MSSQLSERVER' in PowerShell. If running but unresponsive to logins, proceed with DAC recovery.
2. Open SQL Server Management Studio (SSMS). Click 'New Query' (not 'Connect' via Object Explorer) to open the connection dialog directly for a query window.
3. In the Server Name field, enter 'ADMIN:servername' — replacing 'servername' with your actual SQL Server instance name or hostname (e.g., 'ADMIN:SQLPROD01' or 'ADMIN:SQLPROD01\INSTANCENAME'). Set authentication as appropriate and click Connect.
4. Dismiss any non-critical errors about Object Explorer, IntelliSense, or the database dropdown failing to populate. These occur because DAC permits only a single connection, and these SSMS subsystems each attempt their own connection — they are expected and benign in this context.
5. Alternatively, connect via sqlcmd from the command line on the SQL Server host itself: sqlcmd -S ADMIN:servername -E (using Windows Authentication) or sqlcmd -S ADMIN:servername -U sa -P <password>. Local sqlcmd DAC does not require remote DAC to be enabled.
6. Once connected via DAC, verify the current Maximum Memory setting by running: EXEC sp_configure 'max server memory (MB)'; — confirm the run_value shows the problematic low figure.
7. Restore Maximum Memory to an appropriate value for the server. As a baseline, set it to at least 256 MB for minimal instances or a calculated value leaving 2–4 GB for the OS. Example for 2048 MB: EXEC sp_configure 'max server memory (MB)', 2048; RECONFIGURE WITH OVERRIDE;
8. Verify the new setting is active: EXEC sp_configure 'max server memory (MB)'; — confirm run_value now reflects the updated figure.
9. Test that normal SSMS or application connections are now accepted. Close the DAC session — only one DAC connection is permitted at a time, so leaving it open may block future emergency access.

Resolution path

• Confirm SQL Server service is running but rejecting normal connections following a Maximum Memory configuration change
• Open SSMS and use the 'New Query' connection dialog — enter 'ADMIN:servername' as the server name to initiate a Dedicated Administrator Connection (DAC)
• Alternatively, connect via sqlcmd on the host using: sqlcmd -S ADMIN:servername -E
• Dismiss expected benign errors from SSMS Object Explorer and IntelliSense attempting secondary connections — these are normal DAC behaviour
• Run: EXEC sp_configure 'max server memory (MB)', 2048; RECONFIGURE WITH OVERRIDE; — substituting 2048 with an appropriate value for the server's RAM
• Verify the setting took effect with: EXEC sp_configure 'max server memory (MB)';
• Confirm normal client connections are now accepted and close the DAC session to free it for future emergency use
• Review and document the correct Maximum Memory baseline for the instance to prevent recurrence

Prevention

• Always verify the unit when entering Maximum Server Memory — the field is in megabytes (MB); entering 15 when intending 15 GB requires 15360
• Set Maximum Server Memory to no lower than 256 MB for minimal test instances; production instances should follow the standard formula: Total RAM minus 2–4 GB OS reservation
• Use SQL Server Policy-Based Management to enforce an acceptable minimum and maximum range for the 'max server memory (MB)' configuration facet
• Test all server-level configuration changes on a non-production instance before applying to production SQL Servers
• Ensure DAC is enabled for remote connections (sp_configure 'remote admin connections', 1) on production instances before performing risky changes, so recovery is possible if local access is unavailable
• Capture and document baseline sp_configure output after initial server build so deviations are immediately identifiable
• Require change-control approval and a peer review for any modifications to SQL Server memory configuration options in production environments

Tools

• SQL Server Management Studio (SSMS) — DAC connection via ADMIN: server name prefix
• sqlcmd — command-line DAC: sqlcmd -S ADMIN:servername -E
• sp_configure system stored procedure
• RECONFIGURE WITH OVERRIDE T-SQL command
• PowerShell (Get-Service) — to confirm SQL Server service state
• SQL Server Error Log — to review memory-related startup or connection errors

References

• DBA StackExchange: Accidentally turned down SQL memory too low to log in — how do I fix?
• Microsoft Docs: Diagnostic Connection for Database Administrators (DAC)
• Microsoft Docs: Server Memory Server Configuration Options