I am sure –m is startup parameter that most number of DBAs have used at one point of time or the other during a disaster recovery scenario involving an issue with system databases or even while moving system database files.

There is a hidden gem about the –m startup parameter which most of us are not aware of or do not use often when required. That is the Client Application Name filter along with the –m parameter. I have gotten frustrated multiple times when I started the SQL instance in single user mode and a sysadmin connection latched onto the only connection (read: SQL Performance monitoring app, CRM apps, or any other 24X7 LOB apps).

If you want to start SQL Server in Single User mode using –m startup parameter, then you can now prevent other applications from connecting to the instance by specifying the restricted Client Application Name which can only connect to the instance. Note that this is a case sensitive parameter. Before specifying the Client Application Name filter, check what the program_name is showing your application name in the sys.dm_exec_sessions DMV output.

If you are doing this for SQL instance maintenance operations, then specify the filter as “SQLCMD” or “Microsoft SQL Server Management Studio – Query” as you should be able to run your scripts from either of the two applications SQLCMD or SSMS.

No more tearing your hair out to figure out who took up the sole connection to the SQL instance when you don’t want that lone wolf to be in demand by other applications.