Need Query to know the SQl server service status

Question

Post reply

Need Query to know the SQl server service status

New persopn

SSCarpal Tunnel

Points: 4019
More actions
January 26, 2016 at 3:18 am

#323876

Hi All,
Please suggest the process to know the Status of all SQL services through CMD.

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

krzysztof.ostrowski SSCrazy Points: 2909 More actions · Answer 1

Maybe like that:

sc query MSSQLSERVER

or

Wmic service where (PathName like '%Binn\\sqlservr%') get caption, name, startmode, state, PathName, ProcessId

New persopn SSCarpal Tunnel Points: 4019 More actions · Answer 2

it will give only Sql server information. I want to know the details including SSRS SSIS SSAS and agent services too.

krzysztof.ostrowski SSCrazy Points: 2909 More actions · Answer 3

You must first get list of services name

sc query type= service state= all |find "SQL" |find /V "DISPLAY_NAME" |find /V "AD" | find /V "Writer"

here is result from my uat

SERVICE_NAME: MSSQLFDLauncher

SERVICE_NAME: MSSQLSERVER

SERVICE_NAME: MSSQLServerOLAPService

SERVICE_NAME: SQLBrowser

SERVICE_NAME: SQLSERVERAGENT

now you can check:

sc query SQLBrowser

New persopn SSCarpal Tunnel Points: 4019 More actions · Answer 4

We will get the list of services but we wont get the state of services. I want to get the services with the status like start/stop

Grant Fritchey SSC Guru Points: 398690 More actions · Answer 5

If I wanted to get service status, you're going to be much better off going through the windows OS rather than attempting this through T-SQL. T-SQL doesn't even control SSAS, let alone the services and their status. Instead, look to WMI queries, probably through PowerShell. Here's an example.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 6

It's a real shame that so many people believe that xp_CmdShell is a security risk but then will let anyone run PowerShell and still not get the data into SQL Server. This task is pretty easy to do if you can use xp_CmdShell.

First, download the DelimitedSplit8K function from the RESOURCE section at the bottom of the following article. It's something that most people need for one reason or another, anyway.

http://www.sqlservercentral.com/articles/Tally+Table/72993/

Once that's in place then run the following code. Since you're smart enough to NOT grant privs to individuals to run xp_CmdShell directly or give them CONTROL SERVER privs, then only DBAs that actually have sysadmin privs will be able to run this (provided xp_CmdShell is enabled).

As usual, details are in the comments in the code.

--===== Conditionally drop any Temp Tables to make reruns in SSMS easier.

IF OBJECT_ID('tempdb..#CmdResult','U') IS NOT NULL DROP TABLE #CmdResult;

IF OBJECT_ID('tempdb..#Service' ,'U') IS NOT NULL DROP TABLE #Service;

--===== Create the table that will hold the unparsed service lines.

CREATE TABLE #CmdResult

(

RowNum INT IDENTITY(1,1)

,CmdResult VARCHAR(8000)

)

;

--===== Pull the service lines into the temp table so we can parse the rows.

-- Note that the order of the columns in the WMIC command doesn't matter.

-- The (missing) NODE column will always come first and the rest of the

-- columns will be in alphabetical order. I listed them in order just to make

-- the returned order obvious. CSV rows are returned thanks to /FORMAT:CSV.

INSERT INTO #CmdResult

(CmdResult)

EXEC xp_CmdShell 'wmic service get Caption,Name,ServiceType,StartMode,State,Status /FORMAT:CSV'

;

--SELECT * FROM #CmdResult;

--===== Parse, pivot (using a fast CROSSTAB), and save the service data in a table that we can query later.

-- Note that "NODE" is the name of the computer we just ran on.

SELECT RowNum = ROW_NUMBER() OVER (ORDER BY cr.RowNum)

,Node = MAX(CASE WHEN split.ItemNumber = 1 THEN split.Item ELSE '' END)

,Caption = REPLACE(MAX(CASE WHEN split.ItemNumber = 2 THEN split.Item ELSE '' END),'&','&')

,Name = REPLACE(MAX(CASE WHEN split.ItemNumber = 3 THEN split.Item ELSE '' END),'&','&')

,ServiceType = MAX(CASE WHEN split.ItemNumber = 4 THEN split.Item ELSE '' END)

,StartMode = MAX(CASE WHEN split.ItemNumber = 5 THEN split.Item ELSE '' END)

,State = MAX(CASE WHEN split.ItemNumber = 6 THEN split.Item ELSE '' END)

,Status = MAX(CASE WHEN split.ItemNumber = 7 THEN split.Item ELSE '' END)

INTO #Service

FROM #CmdResult cr

CROSS APPLY dbo.DelimitedSplit8K(cr.CmdResult,',') split

WHERE cr.RowNum > 2 --First row is blank, second row is column headers

AND cr.CmdResult > '' --Last row is NULL but this takes care of BLANKS and NULLs

GROUP BY cr.RowNum --And now you know why the IDENTITY column is important.

;

--===== Show what we've got

SELECT *

FROM #Service

ORDER BY Name

;

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

krzysztof.ostrowski SSCrazy Points: 2909 More actions · Answer 7

Jeff Moden (1/26/2016)
It's a real shame that so many people believe that xp_CmdShell is a security risk but then will let anyone run PowerShell and still not get the data into SQL Server.

Its very nice solution, but will not show it, that sqlserver service is stopped, because it will not be able to execute

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 8

krzysztof.ostrowski (1/26/2016)
Jeff Moden (1/26/2016)
It's a real shame that so many people believe that xp_CmdShell is a security risk but then will let anyone run PowerShell and still not get the data into SQL Server.
Its very nice solution, but will not show it, that sqlserver service is stopped, because it will not be able to execute

Like I said, that's a real shame. You could, however, run the WMIC command via CmdExec step in a job. Or, turn xp_cmdshell on, execute the code, and then turn it back off if you want nice warm fuzzies.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

johnwalker10 SSCrazy Eights Points: 9124 More actions · Answer 9

Take a look at this article, hope this will help you out:

https://www.mssqltips.com/sqlservertip/2609/checking-sql-services-status--an-evolution-part-1/[/url]

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 10

johnwalker10 (1/27/2016)
Take a look at this article, hope this will help you out:
https://www.mssqltips.com/sqlservertip/2609/checking-sql-services-status--an-evolution-part-1/[/url]

Thanks for posting that article. Interesting thing in the discussions that occurred on that article... some people are complaining about the use of xp_CmdShell there but no one's complaining about the clear text login and password in the code that a fellow by the name of "Ed" wrote to supposedly get away from the so called security risk. Go figure. 🙂

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Sgar... SSCarpal Tunnel Points: 4409 More actions · Answer 11

using powershell script you can get that data.

Sagar Sonawane
** Every DBA has his day!!:cool: