-2

On SQL Server 2019, it is possible to configure the server for a smaller than minimum amount of memory (in my case, 100mb). After doing this, the server crashes on startup. Some other forums pointed to the -f flag, but in my case I am seeing some "unable to read file" error even when doing this.

Have any of you troubleshooted this issue before?

New contributor
Seth Ficke is a new contributor to this site. Take care in asking for clarification, commenting, and answering. Check out our Code of Conduct.
4
  • 4
    Do you think it would be useful to post the full text of the other error message? Oct 13 at 14:31
  • Does this answer your question? Accidentally turned down SQL memory too low to log in, how do I fix?
    – bbaird
    Oct 13 at 14:45
  • I took a wild guess at what error you might be seeing when you start SQL Server with the -f flag. But if the error you are getting is not related to tempdb startup, please edit your question to include the full error text. Without the full error, all we can do is guess.
    – AMtwo
    Oct 13 at 16:18
  • Please add the exact error message to your question. Without that detail, we are likely shooting in the dark, and making guesses about the cause.
    – Hannah Vernon
    Oct 13 at 17:38
1

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 RECONFIGURE.

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.

Not the answer you're looking for? Browse other questions tagged or ask your own question.