Script to see if SQLAgent is running

Question

Post reply

Script to see if SQLAgent is running

well0549

SSCrazy

Points: 2259
More actions
September 9, 2003 at 9:14 am

#81106

does anyone have a SQL script to see
if SQLServer Agent is running.
And if possible MSDTC as well...
I know you can do this with XP_regread. But sometimes this gives me an error....
( if anybody knows why i wouldn't need this query )

Viewing 15 posts - 1 through 15 (of 16 total)

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

Allen Cui-55137 SSC Guru Points: 51650 More actions · Answer 1

xp_cmdshell 'sc.exe \\yourservername query MSDTC'

xp_cmdshell 'sc.exe \\yourservername query SQLSERVERAGENT'

mybid2003 SSCertifiable Points: 6620 More actions · Answer 2

mybid2003

SSCertifiable

Points: 6620

September 9, 2003 at 9:40 am

#473243

Allen,

Is it scm.exe or sc.exe??

.

Allen Cui-55137 SSC Guru Points: 51650 More actions · Answer 3

sc.exe but scm.exe can also give similar information.

mybid2003 SSCertifiable Points: 6620 More actions · Answer 4

I couldn't find sc.exe on the server. I have executed the following command from QA:

xp_cmdshell 'scm.exe \\NCCHASQL01 query SQLSERVERAGENT'

It never completed. after 10 minutes, I stopped the query from QA. after few minutes the connection timedout. But I could still see the connection on the server (using sp_who2 & DBArtisan). It went into "KILLED/ROLLBACK" state when I killed the connection with KILL. It still running in that state.

.

Allen Cui-55137 SSC Guru Points: 51650 More actions · Answer 5

Run scm.exe ? to see the complete syntax.

http://support.microsoft.com/default.aspx?scid=kb;en-us;230236

sc.exe is from NT/2000 resource kit. I like it better than scm.exe.

http://support.microsoft.com/?kbid=251192

BrenBart SSCrazy Points: 2622 More actions · Answer 6

sc.exe comes with the Win32® Software Development Kit (SDK)

http://msdn.microsoft.com/archive/default.asp?url=/archive/en-us/dnarpic/html/msdn_scmslite.asp

"I met Larry Niven at ConClave 27...AND I fixed his computer. How cool is that?"
(Memoirs of a geek)

mybid2003 SSCertifiable Points: 6620 More actions · Answer 7

Allen:

My command xp_cmdshell 'scm.exe \\NCCHASQL01 query SQLSERVERAGENT' is still active on the server in RUNNABALE state with KILLED/ROLLBACK in the command.

I tried to kill the process... But nothing is happening...

PLEASE SUGGEST.... I tried this on the PRODUCTION System. My unpardonable mistake. 🙁

.

K. Brian Kelley SSC Guru Points: 114642 More actions · Answer 8

IF you have administrative rights to the system. Grab the pstools suite from sysinternals.

http://www.sysinternals.com/ntw2k/freeware/pstools.shtml

Use pslist to look to see if sc or scm is running. If it's running, use pskill to kill that process.

K. Brian Kelley

http://www.truthsolutions.com/

Author: Start to Finish Guide to SQL Server Performance Monitoring

http://www.netimpress.com/

K. Brian Kelley
@kbriankelley

mybid2003 SSCertifiable Points: 6620 More actions · Answer 9

Thank You Brian. I remote logged into Server. I saw the processes using Task Manager--> Processes & killed scm.exe.

I was not sure to kill this like this earlier.

Thanks Again.

.

well0549 SSCrazy Points: 2259 More actions · Answer 10

Thanks but this is not what i am looking for.

It would be a diagnostic query.

And installing all kind of stuff on a production server is out of the question.....

Any other suggestions ??????

Wayne G. Zablatzky Old Hand Points: 368 More actions · Answer 11

Try this:

SET NOCOUNT ON

DECLARE @ServiceExists VARCHAR(255)

CREATE TABLE #services

( service VARCHAR(255) )

-- Retrieve list of services that ARE running

INSERT INTO #services

EXECUTE master..xp_cmdshell 'net start'

-- Clean up some of the data....

DELETE FROM #services

WHERE service NOT LIKE ' %'

OR service IS NULL

UPDATE #services

SET service = LTRIM(service)

-- Query for service

SELECT @ServiceExists=service

FROM #services

WHERE service = 'SQLSERVERAGENT'

-- Test is service is running. Do what ever you need....

IF ISNULL(@ServiceExists,'') = ''

BEGIN

SELECT 'Service is NOT Running'

-- Restart service....

EXECUTE master..xp_cmdshell 'net start SQLSERVERAGENT'

END

ELSE

BEGIN

SELECT 'Service is Running'

END

-- Clean-up....

DROP TABLE #services

SET NOCOUNT OFF

Bob Snyder Right there with Babe Points: 727 More actions · Answer 12

This one is, very similar to wzablatzky;

CREATE TABLE #ProcText (LineText varchar(255) NULL)

INSERT #ProcText (LineText) EXEC ('exec master..xp_cmdshell ''NET START''')

SELECT RTRIM(linetext) FROM #ProcText WHERE linetext LIKE '%SQL%' OR linetext LIKE '%MSD%'

DROP TABLE #ProcText

stanislavpetkov SSC-Addicted Points: 474 More actions · Answer 13

Just to add some WMI flavor here:

The script could be executed from a workstation if the user has admin rights to the box and if WMI has been installed (it comes with Win2000 and up, but has to be installed on WinNT).

The script will give you the information if the services are running, but trough similar scripts could be done more than that.

The script could be executed from both Windows Explorer by just dbl clicking it, or in a command window with the command line:

CScript TheScript.vbs.

Dim oLocator

Dim oServices

Dim sQueryText

Dim OneService

Dim sResult

Set oLocator = CreateObject("WbemScripting.SWbemLocator")

Set oServices = oLocator.ConnectServer("ServerName") ' Put a real server name here

'sQueryText = "Select Name,State From Win32_Service"

sQueryText = "Select Name,State From Win32_Service Where " & _

"Name = 'MSSQLServer' Or " & _

"Name = 'SQLServerAgent' Or Name = 'MSDTC'"

Set oWMIObjSet = oServices.ExecQuery(sQueryText)

sResult = ""

For Each OneService In oWMIObjSet

sResult = sResult & OneService.Name & " - " & OneService.State & vbCrLf

Stanislav Petkov

philcart SSC-Forever Points: 47794 More actions · Answer 14

Useful script ...

Not being totally up with WMI, how would I do it if I was just querying one service? Presumably I wouldn't need the For...Next loop?

Hope this helps

Phill Carter

--------------------

Colt 45 - the original point and click interface

--------------------
Colt 45 - the original point and click interface