SQL Server has protections to prevent a user from setting Max Server Memory below Min Server Memory. However, users can override that protection by using the
WITH OVERRIDE clause when issuing the
RECONFIGURE command. You should never use
WITH OVERRIDE on a
Once you override protections & misconfigure your server, you will need to start the SQL Server Service with minimal configuration via the
-f flag, as you mention.
From the docs, minimal configuration includes a number of limitations:
When you start an instance of SQL Server in minimal configuration mode, note the following:
- Only a single user can connect, and the CHECKPOINT process is not executed.
- Remote access and read-ahead are disabled.
- Startup stored procedures do not run.
- tempdb is configured at the smallest possible size.
Since you do not include the full error you are seeing, it's difficult to say for sure what problem you are having when you run the service with the
-f flag. However, I suspect it is caused by that last condition in the above list: "tempdb is configured at the smallest possible size."
Permissions issues on reconfiguring tempdb files on startup can sometimes lead to an error like:
Could not open file D:\MSSQL\Data\tempdb.mdf for file number 1.
OS error: 3(The system cannot find the path specified.).
The instructions for setting the startup options for the service are not included in the above article, but are listed separately.
If you did not configure the startup parameter properly, or are trying to run the SQL Server Service in a different way than normal (ex, from a command prompt), or if you accidentally changed the service account while configuring the
-f flag, it's possible that NTFS permissions on the tempdb files/directory are incorrect. In that case, you'd need to backtrack on what you did, and fix your original configuration error. If you are still having tempdb creation/permission issues after that, you may need to fix permissions on the directory containing your tempdb files.