SQL Server Configuration Manager Error The Parameter is Invalid


By:   |   Updated: 2021-06-11   |   Comments   |   Related: More > SQL Server Configurations


Problem

In a previous article, we learned how to migrate the master database files to a new location. This process assumes changing the SQL Server startup parameters in SQL Server Configuration Manager and setting the new paths of the data and log files. This is quite a simple process, and everything should go smoothly. While testing this process, I ran into an issue with changing the startup parameters in SQL Server Configuration Manager and getting the error "The parameter is invalid". I followed the same steps that worked for other SQL Server instances, but was now getting this error.

In this article, we will describe what caused this issue and how this can be solved.

Solution

To make the solution more informative and clearer, we will define a sample task to reproduce the issue and solve this problem.

I was following the steps in the Move SQL Server Files for Master Database article. I moved the master database files to the location shown below C:\MSSQL\Master-Databases\.

master database files

When I got to the point of updating the startup parameters, I recieved the following error message.

sql server configuration manager error

After a bunch of trial and error, I figured out the reason for this is that the new path contains a hyphen which is a special character and is rejected by SQL Server Configuration Manager. I could have just changed the folder and moved the master database files to another folder, but I wanted to see if there was a way to solve this problem.

Realizing that it is impossible to change this parameter via SQL Server Configuration Manager we need to switch to alternative options. Specifically, we will set the values using the Windows registry. First, we need to find out which registry keys and which arguments we should edit to change these values. To do that, we will run the code below:

SELECT * FROM sys.dm_server_registry
WHERE registry_key LIKE '%MSSQLSERVER\Parameters%'

The result clearly shows the registry paths of these startup parameters:

registry entries

Now, we will go to the Registry editor and will locate and edit these values. Just type "regedit" in the Windows search and the Registry Editor will appear which we can click on to open. We can also open the Registry Editor by issuing the regedit command in the Windows Command Prompt:

registry editor

When we are in the registry editor, we can locate the path which the query above shows. In our case: HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQLServer\Parameters

Here we can see that SQLArg0 and SQLArg2 values contain the master database data and log files correspondingly:

registry editor

We will change these values to the new path for both parameters, which works without any issues.

registry editor

Now we can try to start SQL Server via SQL Server Configuration Manager and can see that it starts successfully:

sql server configuration manager

By running the query below, we can find the new paths of the master database files:

SELECT name AS FileLogicalName, physical_name AS FileLocation
FROM sys.master_files  
WHERE database_id = 1

Perfect, the new path with a hyphen is set:

master database files location

The same path we can see in the SQL Server Configuration Manager:

startup parameters

A bit tricky situation: while there is no way to directly set a parameter with a special symbol (hyphen in our case) in SQL Server Configuration Manager, the new path with the symbol appears in Configuration Manager after setting it through the Registry Editor. Anyway, our task is solved and the new path containing a special symbol is set.

Before concluding, let’s do one more test. It will be interesting to see what happens if we try to edit this path in SQL Server Configuration Manager. Will it allow us to keep the current path? Let's see. We will just click on the startup parameter with a hyphen and without changing anything click "Update". Hmm, the same error appears:

sql server configuration manager error

Even though the path with a hyphen appears here in Configuration Manager, the error occurs in saving the same value. We will just click "OK" and close the window. Our path remains with a hyphen as we haven’t changed anything.

Conclusion

In conclusion, although it is not possible to specify the SQL Server startup parameters containing special characters in the SQL Server Configuration Manager, it is still possible to overcome this obstacle. Startup parameters with special characters can be set directly in the Windows registry editor by editing the values of the corresponding arguments. A better option is to try to avoid this situation and now use characters that will cause an issue.

Next Steps

Please find more topic-related information by following the links below:



Last Updated: 2021-06-11


get scripts

next tip button



About the author
MSSQLTips author Sergey Gigoyan Sergey Gigoyan is a database professional with more than 10 years of experience, with a focus on database design, development, performance tuning, optimization, high availability, BI and DW design.

View all my tips
Related Resources



Comments For This Article





download





Recommended Reading

How to tell what SQL Server versions you are running

Resolving could not open a connection to SQL Server errors

Identify SQL Server TCP IP port being used

Changing SQL Server Collation After Installation

Managing Maximum Number of Concurrent Connections in SQL Server














get free sql tips
agree to terms