Finding All Running Services on SQL Server

I was revisiting automation scripts that I had written back in the day and wanted to automate some steps to determine all the steps running on SQL Server. With all the resources on net, I didn’t want to write one from scratch so I went on a hunt and the best solution I found was by Robert Pearl @PearlKnows from an article he wrote on https://www.mssqltips.com/sqlservertip/2610/sql-services-status-check–an-evolution-part-2/ great script and it would do the job but I need to change a few things.

  1. Main change was determining the version of SQL Server without having to keep updating the script as new versions are released so I made the following change specifically for SSIS Service which changes the name on each release

--IF CHARINDEX('2008',@@Version) > 0 SET @DTS='MsDtsServer100'
--IF CHARINDEX('2005',@@Version) > 0 SET @DTS= 'MsDtsServer'


select @DTS = 'MsDtsServer'+substring(cast(value_data as varchar(10)), 1,2)+'0' from sys.dm_server_registry where value_name = 'CurrentVersion'
SET @REGKEY = 'System\CurrentControlSet\Services\'+@DTS

  1. Change the Temp tables cleanup – very minor

Below is my entire script, again thanks to Robert for such a great work

--Check SQL Server Services Status
SET NOCOUNT ON
IF EXISTS (SELECT * FROM TEMPDB.DBO.sysobjects
WHERE NAME LIKE 'ServicesServiceStatus')
DROP TABLE tempdb.dbo.ServicesServiceStatus
IF EXISTS (SELECT * FROM TEMPDB.DBO.sysobjects
WHERE NAME LIKE 'RegResult')
DROP TABLE tempdb.dbo.RegResult
CREATE TABLE tempdb.dbo.RegResult
(
ResultValue NVARCHAR(4)
)
CREATE TABLE tempdb.dbo.ServicesServiceStatus
(
RowID INT IDENTITY(1,1)
,ServerName NVARCHAR(128)
,ServiceName NVARCHAR(128)
,ServiceStatus VARCHAR(128)
,StatusDateTime DATETIME DEFAULT (GETDATE())
,PhysicalSrverName NVARCHAR(128)
)
DECLARE
@ChkInstanceName NVARCHAR(128) /*Stores SQL Instance Name*/
,@ChkSrvName NVARCHAR(128) /*Stores Server Name*/
,@TrueSrvName NVARCHAR(128) /*Stores where code name needed */
,@SQLSrv NVARCHAR(128) /*Stores server name*/
,@PhysicalSrvName NVARCHAR(128) /*Stores physical name*/
,@DTS NVARCHAR(128) /*Store SSIS Service Name */
,@FTS NVARCHAR(128) /*Stores Full Text Search Service name*/
,@RS NVARCHAR(128) /*Stores Reporting Service name*/
,@SQLAgent NVARCHAR(128) /*Stores SQL Agent Service name*/
,@OLAP NVARCHAR(128) /*Stores Analysis Service name*/
,@REGKEY NVARCHAR(128) /*Stores Registry Key information*/
SET @PhysicalSrvName = CAST(SERVERPROPERTY('MachineName') AS VARCHAR(128))
SET @ChkSrvName = CAST(SERVERPROPERTY('INSTANCENAME') AS VARCHAR(128))
SET @ChkInstanceName = @@serverName
IF @ChkSrvName IS NULL /*Detect default or named instance*/
BEGIN
SET @TrueSrvName = 'MSSQLSERVER'
SELECT @OLAP = 'MSSQLServerOLAPService' /*Setting up proper service name*/
SELECT @FTS = 'MSFTESQL'
SELECT @RS = 'ReportServer'
SELECT @SQLAgent = 'SQLSERVERAGENT'
SELECT @SQLSrv = 'MSSQLSERVER'
END
ELSE
BEGIN
SET @TrueSrvName = CAST(SERVERPROPERTY('INSTANCENAME') AS VARCHAR(128))
SET @SQLSrv = '$'+@ChkSrvName
SELECT @OLAP = 'MSOLAP' + @SQLSrv /*Setting up proper service name*/
SELECT @FTS = 'MSFTESQL' + @SQLSrv
SELECT @RS = 'ReportServer' + @SQLSrv
SELECT @SQLAgent = 'SQLAgent' + @SQLSrv
SELECT @SQLSrv = 'MSSQL' + @SQLSrv
END
/* ------------------------ SQL Server Service Section ----------------------*/
SET @REGKEY = 'System\CurrentControlSet\Services\'+@SQLSrv
INSERT tempdb.dbo.RegResult ( ResultValue ) EXEC MASTER.sys.xp_regread @rootkey='HKEY_LOCAL_MACHINE', @key= @REGKEY
IF (SELECT ResultValue FROM tempdb.dbo.RegResult) = 1
BEGIN
INSERT tempdb.dbo.ServicesServiceStatus (ServiceStatus) /*Detecting staus of SQL Sever service*/
EXEC xp_servicecontrol N'QUERYSTATE',@SQLSrv
UPDATE tempdb.dbo.ServicesServiceStatus SET ServiceName = 'MS SQL Server Service' WHERE RowID = @@identity
UPDATE tempdb.dbo.ServicesServiceStatus SET ServerName = @TrueSrvName WHERE RowID = @@identity
UPDATE tempdb.dbo.ServicesServiceStatus SET PhysicalSrverName = @PhysicalSrvName WHERE RowID = @@identity
TRUNCATE TABLE tempdb.dbo.RegResult
END
ELSE
BEGIN
INSERT INTO tempdb.dbo.ServicesServiceStatus (ServiceStatus) VALUES ('NOT INSTALLED')
UPDATE tempdb.dbo.ServicesServiceStatus SET ServiceName = 'MS SQL Server Service' WHERE RowID = @@identity
UPDATE tempdb.dbo.ServicesServiceStatus SET ServerName = @TrueSrvName WHERE RowID = @@identity
UPDATE tempdb.dbo.ServicesServiceStatus SET PhysicalSrverName = @PhysicalSrvName WHERE RowID = @@identity
TRUNCATE TABLE tempdb.dbo.RegResult
END
/* -------------------- SQL Server Agent Service Section ----------------*/
SET @REGKEY = 'System\CurrentControlSet\Services\'+@SQLAgent
INSERT tempdb.dbo.RegResult ( ResultValue ) EXEC MASTER.sys.xp_regread @rootkey='HKEY_LOCAL_MACHINE', @key= @REGKEY
IF (SELECT ResultValue FROM tempdb.dbo.RegResult) = 1
BEGIN
INSERT tempdb.dbo.ServicesServiceStatus (ServiceStatus) /*Detecting staus of SQL Agent service*/
EXEC xp_servicecontrol N'QUERYSTATE',@SQLAgent
UPDATE tempdb.dbo.ServicesServiceStatus SET ServiceName = 'SQL Server Agent Service' WHERE RowID = @@identity
UPDATE tempdb.dbo.ServicesServiceStatus SET ServerName = @TrueSrvName WHERE RowID = @@identity
UPDATE tempdb.dbo.ServicesServiceStatus SET PhysicalSrverName = @PhysicalSrvName WHERE RowID = @@identity
TRUNCATE TABLE tempdb.dbo.RegResult
END
ELSE
BEGIN
INSERT INTO tempdb.dbo.ServicesServiceStatus (ServiceStatus) VALUES ('NOT INSTALLED')
UPDATE tempdb.dbo.ServicesServiceStatus SET ServiceName = 'SQL Server Agent Service' WHERE RowID = @@identity
UPDATE tempdb.dbo.ServicesServiceStatus SET ServerName = @TrueSrvName WHERE RowID = @@identity
UPDATE tempdb.dbo.ServicesServiceStatus SET PhysicalSrverName = @PhysicalSrvName WHERE RowID = @@identity
TRUNCATE TABLE tempdb.dbo.RegResult
END
/* ---------------------------------- SQL Browser Service Section ------------*/
SET @REGKEY = 'System\CurrentControlSet\Services\SQLBrowser'
INSERT tempdb.dbo.RegResult ( ResultValue ) EXEC MASTER.sys.xp_regread @rootkey='HKEY_LOCAL_MACHINE', @key= @REGKEY
IF (SELECT ResultValue FROM tempdb.dbo.RegResult) = 1
BEGIN
INSERT tempdb.dbo.ServicesServiceStatus (ServiceStatus) /*Detecting staus of SQL Browser Service*/
EXEC MASTER.dbo.xp_servicecontrol N'QUERYSTATE',N'sqlbrowser'
UPDATE tempdb.dbo.ServicesServiceStatus SET ServiceName = 'SQL Browser Service - Instance Independent' WHERE RowID = @@identity
UPDATE tempdb.dbo.ServicesServiceStatus SET ServerName = @TrueSrvName WHERE RowID = @@identity
UPDATE tempdb.dbo.ServicesServiceStatus SET PhysicalSrverName = @PhysicalSrvName WHERE RowID = @@identity
TRUNCATE TABLE tempdb.dbo.RegResult
END
ELSE
BEGIN
INSERT INTO tempdb.dbo.ServicesServiceStatus (ServiceStatus) VALUES ('NOT INSTALLED')
UPDATE tempdb.dbo.ServicesServiceStatus SET ServiceName = 'SQL Browser Service - Instance Independent' WHERE RowID = @@identity
UPDATE tempdb.dbo.ServicesServiceStatus SET ServerName = @TrueSrvName WHERE RowID = @@identity
UPDATE tempdb.dbo.ServicesServiceStatus SET PhysicalSrverName = @PhysicalSrvName WHERE RowID = @@identity
TRUNCATE TABLE tempdb.dbo.RegResult
END
/* ---------------------------------- Integration Service Section -------------*/
--IF CHARINDEX('2008',@@Version) > 0 SET @DTS='MsDtsServer100'
--IF CHARINDEX('2005',@@Version) > 0 SET @DTS= 'MsDtsServer'
select @DTS = 'MsDtsServer'+substring(cast(value_data as varchar(10)), 1,2)+'0' from sys.dm_server_registry
where value_name = 'CurrentVersion'
SET @REGKEY = 'System\CurrentControlSet\Services\'+@DTS
INSERT tempdb.dbo.RegResult ( ResultValue ) EXEC MASTER.sys.xp_regread @rootkey='HKEY_LOCAL_MACHINE', @key= @REGKEY
IF (SELECT ResultValue FROM tempdb.dbo.RegResult) = 1
BEGIN
INSERT tempdb.dbo.ServicesServiceStatus (ServiceStatus) /*Detecting staus of Intergration Service*/
EXEC MASTER.dbo.xp_servicecontrol N'QUERYSTATE',@DTS
UPDATE tempdb.dbo.ServicesServiceStatus SET ServiceName = 'Integration Service - Instance Independent' WHERE RowID = @@identity
UPDATE tempdb.dbo.ServicesServiceStatus SET ServerName = @TrueSrvName WHERE RowID = @@identity
UPDATE tempdb.dbo.ServicesServiceStatus SET PhysicalSrverName = @PhysicalSrvName WHERE RowID = @@identity
TRUNCATE TABLE tempdb.dbo.RegResult
END
ELSE
BEGIN
INSERT INTO tempdb.dbo.ServicesServiceStatus (ServiceStatus) VALUES ('NOT INSTALLED')
UPDATE tempdb.dbo.ServicesServiceStatus SET ServiceName = 'Integration Service - Instance Independent' WHERE RowID = @@identity
UPDATE tempdb.dbo.ServicesServiceStatus SET ServerName = @TrueSrvName WHERE RowID = @@identity
UPDATE tempdb.dbo.ServicesServiceStatus SET PhysicalSrverName = @PhysicalSrvName WHERE RowID = @@identity
TRUNCATE TABLE tempdb.dbo.RegResult
END
/* ---------------------------------- Reporting Service Section ----------*/
SET @REGKEY = 'System\CurrentControlSet\Services\'+@RS
INSERT tempdb.dbo.RegResult ( ResultValue ) EXEC MASTER.sys.xp_regread @rootkey='HKEY_LOCAL_MACHINE', @key= @REGKEY
IF (SELECT ResultValue FROM tempdb.dbo.RegResult) = 1
BEGIN
INSERT tempdb.dbo.ServicesServiceStatus (ServiceStatus) /*Detecting staus of Reporting service*/
EXEC MASTER.dbo.xp_servicecontrol N'QUERYSTATE',@RS
UPDATE tempdb.dbo.ServicesServiceStatus SET ServiceName = 'Reporting Service' WHERE RowID = @@identity
UPDATE tempdb.dbo.ServicesServiceStatus SET ServerName = @TrueSrvName WHERE RowID = @@identity
UPDATE tempdb.dbo.ServicesServiceStatus SET PhysicalSrverName = @PhysicalSrvName WHERE RowID = @@identity
TRUNCATE TABLE tempdb.dbo.RegResult
END
ELSE
BEGIN
INSERT INTO tempdb.dbo.ServicesServiceStatus (ServiceStatus) VALUES ('NOT INSTALLED')
UPDATE tempdb.dbo.ServicesServiceStatus SET ServiceName = 'Reporting Service' WHERE RowID = @@identity
UPDATE tempdb.dbo.ServicesServiceStatus SET ServerName = @TrueSrvName WHERE RowID = @@identity
UPDATE tempdb.dbo.ServicesServiceStatus SET PhysicalSrverName = @PhysicalSrvName WHERE RowID = @@identity
TRUNCATE TABLE tempdb.dbo.RegResult
END
/* ---------------------------------- Analysis Service Section ---------------*/
IF @ChkSrvName IS NULL /*Detect default or named instance*/
BEGIN
SET @OLAP = 'MSSQLServerOLAPService'
END
ELSE
BEGIN
SET @OLAP = 'MSOLAP'+'$'+@ChkSrvName
SET @REGKEY = 'System\CurrentControlSet\Services\'+@OLAP
END
INSERT tempdb.dbo.RegResult ( ResultValue ) EXEC MASTER.sys.xp_regread @rootkey='HKEY_LOCAL_MACHINE', @key= @REGKEY
IF (SELECT ResultValue FROM tempdb.dbo.RegResult) = 1
BEGIN
INSERT tempdb.dbo.ServicesServiceStatus (ServiceStatus) /*Detecting staus of Analysis service*/
EXEC MASTER.dbo.xp_servicecontrol N'QUERYSTATE',@OLAP
UPDATE tempdb.dbo.ServicesServiceStatus SET ServiceName = 'Analysis Services' WHERE RowID = @@identity
UPDATE tempdb.dbo.ServicesServiceStatus SET ServerName = @TrueSrvName WHERE RowID = @@identity
UPDATE tempdb.dbo.ServicesServiceStatus SET PhysicalSrverName = @PhysicalSrvName WHERE RowID = @@identity
TRUNCATE TABLE tempdb.dbo.RegResult
END
ELSE
BEGIN
INSERT INTO tempdb.dbo.ServicesServiceStatus (ServiceStatus) VALUES ('NOT INSTALLED')
UPDATE tempdb.dbo.ServicesServiceStatus SET ServiceName = 'Analysis Services' WHERE RowID = @@identity
UPDATE tempdb.dbo.ServicesServiceStatus SET ServerName = @TrueSrvName WHERE RowID = @@identity
UPDATE tempdb.dbo.ServicesServiceStatus SET PhysicalSrverName = @PhysicalSrvName WHERE RowID = @@identity
TRUNCATE TABLE tempdb.dbo.RegResult
END
/* ---------------------------------- Full Text Search Service Section ---------*/
SET @REGKEY = 'System\CurrentControlSet\Services\'+@FTS
INSERT tempdb.dbo.RegResult ( ResultValue ) EXEC MASTER.sys.xp_regread @rootkey='HKEY_LOCAL_MACHINE', @key= @REGKEY
IF (SELECT ResultValue FROM tempdb.dbo.RegResult) = 1
BEGIN
INSERT tempdb.dbo.ServicesServiceStatus (ServiceStatus) /*Detecting staus of Full Text Search service*/
EXEC MASTER.dbo.xp_servicecontrol N'QUERYSTATE',@FTS
UPDATE tempdb.dbo.ServicesServiceStatus SET ServiceName = 'Full Text Search Service' WHERE RowID = @@identity
UPDATE tempdb.dbo.ServicesServiceStatus SET ServerName = @TrueSrvName WHERE RowID = @@identity
UPDATE tempdb.dbo.ServicesServiceStatus SET PhysicalSrverName = @PhysicalSrvName WHERE RowID = @@identity
TRUNCATE TABLE tempdb.dbo.RegResult
END
ELSE
BEGIN
INSERT INTO tempdb.dbo.ServicesServiceStatus (ServiceStatus) VALUES ('NOT INSTALLED')
UPDATE tempdb.dbo.ServicesServiceStatus SET ServiceName = 'Full Text Search Service' WHERE RowID = @@identity
UPDATE tempdb.dbo.ServicesServiceStatus SET ServerName = @TrueSrvName WHERE RowID = @@identity
UPDATE tempdb.dbo.ServicesServiceStatus SET PhysicalSrverName = @PhysicalSrvName WHERE RowID = @@identity
TRUNCATE TABLE tempdb.dbo.RegResult
END
/* ---------------------------------------------------------------*/
SELECT ServiceName AS 'SQL Server Service'
,ServiceStatus AS 'Current Service Status'
,StatusDateTime AS 'Date/Time Service Status Checked'
FROM tempdb.dbo.ServicesServiceStatus
/* ----------------------------------------------------------------------*/

Advertisements

Quick Clean-up for mysites

There are moments, when you need to remove all the mysites in a SharePoint environment

Quick PowerShell script

$sites = Get-SPSite | Where-Object {$_.Url -like “<host>/personal/*”}

foreach ($site in $sites)
{
$DisplaySite = $site.ToString()
write-host $DisplaySite.substring(11)
Remove-Spsite -identity $DisplaySite.substring(11) -confirm:$false
}

Power BI

Schematics behind Power BI (beautiful)PowerBI-Architecture

Power BI

Growth of Power BI, Apple watch can now provide insights at a glancePowerBI

Got over being not soo productive…

Final cert taken for Business Intelligence (MCSE)

MCSE

Installing and Configuring R

Now that we know what R is, the next steps is to get it configured for a SQL Server environment, at the writing of this post I was using SQL Server 2016 CPT 3.0 and I had combed through the Microsoft site for the post configuration process but the configuration kept failing due to some gaps in the process. I was finally able to get it working by following the MSSQLTips configuration process.

Make sure you open the Connector RGUi and execute an R command easy one being 1+1 to allow the RevoScaler to directory to be created in your installation directory.

R in SQL Server 2016

Amongst many, one of the new exciting feature in SQL Server 2016 is the inclusion of Revolution Analytic popularly known as R or Advanced Analytic. For the seasoned SQL professionals like me R was the 18th letter of the alphabet but not for long. The kid in me that likes new shiny things went digging to figure what this “R” was.

So what is R?

R is the sequel (pun intended) of a language called S developed by some AT&T dudes back in the 90’s which presumably “altered how people analyze, visualize and manipulated data.” R expands on this idea and it’s quite common in the data scientist field. It has very extensive and powerful graphic abilities tightly linked to analytical capabilities.

From SQL Server perspective, incorporation of R allows us to do advanced analysis without having to export the data out of SQL Server and it grows a new breed of data analysts to bring big data analytic to SQL Server.

Oohh and its open source too, so trend knowing attention may be limited.

Knowing that, combined with the love of data, we’ll try to become scientisty with the tools in our hands.