Did you know about the –m startup parameter


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.

Advertisements

How to find startup parameters for SQL Server 2005 using WMI


SQL Server startup parameters -d, -l, -e which store the information about the master database data file, log file and ERRORLOG locations respectively are stored under the registry key: 
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.X\MSSQLServer\Parameters

However, if you need to find out the startup parameters without using the regedit snap-in (default and any extra ones added), you can use the following script:

 <<SCRIPT>>

strComputer = "."
 

Set objWMIService = GetObject("winmgmts:\\" & strComputer & "\root\Microsoft\SqlServer\ComputerManagement")

 

Set colItems = objWMIService.ExecQuery( _

 

"SELECT * FROM SqlServiceAdvancedProperty WHERE SqlServiceType = 1 and PropertyName = 'STARTUPPARAMETERS'",,48)

 

For Each objItem in colItems

 

Wscript.Echo "ServiceName: " & objItem.ServiceName

 

Wscript.Echo "PropertyName: " & objItem.PropertyName

 

Wscript.Echo "PropertyStrValue: " & objItem.PropertyStrValue

 

Next

 

 <</SCRIPT>