Find out is SQL Server services are running by TSQL Query
-
Since these are Windows services, you would need to query Windows to determine if these services are running.
From command line:
sc query mssqlserver
sc query mssqlagent
sc query msdtc
If you are looking to find out in a SQL query (like in a stored procedure), you could use xp_cmdshell to send these commands, but you are going to get a multiple line response that you will need to parse out.
Hope this helps
Mark -
Well,
How do you submit an SQL Server query that queries "if SQL is running " if SQL server is not running for the first place and you can not run queries!
I do agree with Mark that Windows sc commans are appropriate as well as WMI queries that query service state similar to
Set colServiceList = objWMIService.ExecQuery _
("Select * from Win32_Service where StartMode = 'Auto'")
For Each objService in colServiceList
RecordString = "Name = " & objService.Name & " Display Name = " & objService.DisplayName & " State = " & objService.State
FileObject.WriteLine (RecordString)
Next
This is not a whole script, just part of it, see examples in Microsoft Script Repository.
Regards,Yelena Varsha
-
In my environment I have around 100 boxes, so what I want is for every 10 minutes i need to check whether all the SQL Server services are running.
If not I want to get alert by email or page.
But I think the "sc" command does not support checking the services on remote server.
Would appreciate any ideas supporting this.
Mark,
A correction, for SQL Agent the command should be of " sc query sqlserveragent"
-
In your situation, I would agree with Yelena, and use WMI queries. You can use those to check service status on remote servers from vbscript, then email your desired response/report. Schedule it either with task scheduler, or with SQL job calling activex script.
Mark -
Scripts like these can often be found in the Microsoft TechNet Script Repository. This should work for you:
This gets back a list of all services and their current status. You should be able to pare down to the services you are interested in by adding a WHERE clause to the WMI query.
K. Brian Kelley
@kbriankelley -
you might want to try
master.dbo.xp_servicecontrol 'MSSQLServer'
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/ -
The undocumented stored procedure xp_servicecontrol only runs local to the SQL Server. By executing it against the remote procedure you could determine if SQL Server was up, but only if you get a connection error, which I believe would be a Sev 16. Unfortunately, that drops you out of any follow on statements.
K. Brian Kelley
@kbriankelley -
it will return a status of running or stopped ( or paused ), you can also query other non sql services
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/ -
It only runs local to the SQL Server. Which means if the SQL Server service is in stopped or paused mode, you can't execute a T-SQL query against it remotely. This will work for services other than MSSQLServer (or more specifically, the service name of the instance) but it won't work if the instance itself is down. Hence the reason I said you'd get a connection failure error.
K. Brian Kelley
@kbriankelley -
You write WMI scripts in a notepad then name it a file with extention .vbs and you may schedule them. See Microsoft Script Repository, processes examples are at:
http://www.microsoft.com/technet/scriptcenter/scripts/os/process/default.mspx
As a joke: if one of your SQL Servers is down, you will get a call from your users much faster 🙂
Regards,Yelena Varsha
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply