This article is about very interesting facts about SQL Server startup parameter “-f”. First let’s warm you up with the well known facts about the parameter.
The parameter -f starts an instance of SQL Server with minimal configuration. This is useful if the setting of a configuration value (for example, over-committing memory) has prevented the server from starting. Starting SQL Server in minimal configuration mode places SQL Server in single-user mode.
Make sense? This is exactly what is written in Microsoft Documentation. And if you are not so experienced in SQL Server I could imagine your facial expression.
With this in mind I shall explain with example what it actually means. At the same time the text “Starting SQL Server in minimal configuration mode places SQL Server in single-user mode” is self explanatory. That means you don’t have to specify -m ( Single user Mode) explicitly when you are using -f.
Let me show you by starting SQL Server with -f parameter:
Not to mention the command is for a default SQL Instance. If you have a named instance then you need to use either of the below:
net start “SQL Server ( instancename )”
net start MSSQL$ instancename
As you can see I haven’t mentioned /m (Single User Mode) parameter but the error still says the SQL Instance is in Single User Mode.
Error message: Server is in single user mode. Only one administrator can connect at this time.
Now let me explain the very first sentence which says “minimal configuration”. What exactly is minimal configuration.
When SQL Server starts with -f parameter:
- It remains in Single User mode as mentioned above
- SQL Server does not execute the Checkpoint process which it normally does while starting SQL Services
- It disables any startup Stored Procedures and Triggers
Now that you understood what is minimal configuration in this context, let me tell you when we need to use it.
When to use -f startup parameter to start SQL Sever:
You may get the following error message while connecting to SQL Instance:
Likewise you may get the below insufficient memory error while executing a query:
Interesting fact about -f Startup Parameter
Without any further delay let me show you how the parameter helps SQL Instance to start though it has insufficient memory.
For this demo I have configured the Max Server Memory to 128 MB:
Now let me stop and then start SQL Server service with -f startup parameter again:
Here I want you to pay attention to the “run_value” for ‘Max Server Memory’ which is the maximum default value though the config value is 128. This is how -f parameter allows a user to connect even though SQL has insufficient memory.
Now does the SQL Instance has that much memory? of course not, it is just configuring to the max possible value and utilizes the amount of memory it needs to login.
Hope you have learned something interesting today. Let me know if you have any questions.
