Get SQL Start Up Parameters and Other Info From sys.dm_server_registry

By Lori Brown | Intermediate

Nov 03

–By Lori Brown @SQLSupahStah

The dynamic management view sys.dm_server_registry has been around since SQL 2008 R2 SP1 so it is not new but it’s relatively new to me. Since I have a ton of SQL Servers to monitor, it is always good to have some way of easily knowing if there are any start up parameters configured for the instance. I found that this and more can be pulled from the registry using sys.dm_server_registry. (https://technet.microsoft.com/en-us/library/hh204561(v=sql.105).aspx )

If you look at all values returned by the dmv, you will see something like this:

blog_20161103_1

In my local instance there are 191 rows returned so this can get to be a little difficult to read through. I wanted to just report on certain things so I have narrowed what is returned. Most of the time I am not interested in the actual registry key, so here is what I did:

— Startup Parameters

SELECT value_name, value_data

FROM sys.dm_server_registry

WHERE registry_key LIKE N’%Parameters’

Which returns this as the results:

blog_20161103_2

Notice SQLArg3 which shows that I have added the T1118 trace flag to the startup parameters. Of course I can get some of the same info by opening up the configuration manager and checking the Startup Parameters tab of the SQL Server service but I wanted my results sent to me periodically in a report so that I can know if someone had added or changed anything. I’ll show my report later.

I also have found that it is good to know what protocols are enabled and what port SQL is listening on. Since I have a lot of servers to manage, with some on the default port and others not, I thought that it would be good to report on this. My clients also have different protocols enabled for different servers. So, since nothing is standardized, it’s good that this info be put into a report so that I can easily refer to it should I ever need it.

— List protocols enabled

SELECT sr.value_data AS EnabledProtocol

FROM sys.dm_server_registry sr

WHERE sr.registry_key IN (SELECT k.registry_key

FROM sys.dm_server_registry k

WHERE k.value_name = ‘Enabled’ AND k.value_data = 1)

AND sr.value_name = ‘DisplayName’

 

— List TCP port

SELECT value_name AS PortName, value_data AS PortValue

FROM sys.dm_server_registry

WHERE registry_key LIKE N’%SuperSocketNetLib\Tcp\IPAll’

AND value_name NOT LIKE N’DisplayName’

blog_20161103_3

And, here is what my report looks like. I like to make small HTML formatted reports that I know I can generate on pretty much any server.

blog_20161103_4

With a little bit of research and creativity, you can come up with ways to get at information in SQL easily.

For more information about blog posts, concepts and definitions, further explanations, or questions you may have…please contact us at SQLRx@sqlrx.com. We will be happy to help! Leave a comment and feel free to track back to us. Visit us at www.sqlrx.com!

 

Follow

About the Author

Lori is an avid runner, cross fitter and SQL enthusiast. She has been working for SQLRX for 15 years and has been working with SQL in general for 25 years. Yup...she is an old hand at this stuff.

>