sys.dm_server_registry – New Dynamic Management View (DMV) in SQL Server 2012

sys.dm_server_registry – New Dynamic Management View (DMV) in SQL Server 2012

As I said earlier either that SQL Server giving more & more power to the administrator of SQL Server by making SQL Server related information available in SQL Server Management Studio directly. Information is two side bladed swords, if you don’t use it wisely than you will be in trouble.

Note: Registry is very sensitive part and change without actually know what exactly you are doing, you will be in trouble.

So far, if you wanted to get some registry level information for SQL Server, you had, mostly, two options.

1.)    Go to registry from Windows

2.)    Use “xp_RegRead” extended stored procedure

SQL Server 2012 is giving you better way to handle this by providing sys.dm_server_registry. You can simply query this new dynamic management view and find all SQL Server related registry with its name and value right from your SQL Server Management Studio. Isn’t it fantastic?

You can set alert email by querying some important value in SQL Server JOB. For example you would like to get an email whenever port of SQL Server changes.

Let us now query this DMV and see its result:

[sourcecode language=”sql”] SELECT * FROM sys.dm_server_registry [/sourcecode]

Here is the output of above query :

You can read my earlier article I written for “XP_RegRead” from here.

If you like this article, do like “Extreme-Advice” page in Facebook.

Reference: Ritesh Shah

http://Extreme-Advice.com

http://www.sqlhub.com

Note: Microsoft Books online is a default reference of all articles.

Sys.dm_server_services – new DMV in SQL Server 2012

Sys.dm_server_services – new DMV in SQL Server 2012

Microsoft is giving more power to the user of Microsoft SQL Server with each release of SQL Server. SQL Server 2012 is no exception and came up with lots of new features, tools, DMVs, system stored procedure, functions and much more. Today we are going to see one of the new DMV in SQL Server 2012, named “Sys.dm_server_services” which returns information about the SQL Server, Full-Text, and SQL Server Agent services in the current instance of SQL Server. You can use this dynamic management view to report status information about these services.

Generally we used to use WMI script or Services.msc under control panel -> Administrative tools to see the installed services of SQL Server and the status of those services. In SQL Server 2012, you can login to SQL Server Management Studio (SSMS), if SQL Server Services is running, and execute simple DMV query to see all SQL Server related services with its status. Isn’t it useful?

Currently I am using WMI script to keep a watch on status of services which used to send me an email if any of the services found stopped in my production server and SQL Server 2012 made my life easy by providing “Sys.dm_server_services”.

Let us execute simple SELECT query on “Sys.dm_server_services”.

[sourcecode language=”sql”]SELECT * FROM Sys.dm_server_services [/sourcecode]

Here is the screen capture of above query:

You can get important information about SQL Server related services like:

  1. Service Name
  2. Service Description whether it is configured to run Automatic or manual etc.
  3. Current status like 1(stopped), 4(running), 7 (Paused) etc.
  4. Last_Startup_time
  5. Service_Account

If you like this article, do like “Extreme-Advice” page in Facebook.

Reference: Ritesh Shah

http://Extreme-Advice.com

http://www.sqlhub.com

Note: Microsoft Books online is a default reference of all articles.