SQL Server Service Check Utility

Question

Post reply

SQL Server Service Check Utility

Rudy Panigas

SSChampion

Points: 10702
More actions
November 13, 2010 at 12:01 pm

#241091

Comments posted to this topic are about the item SQL Server Service Check Utility
Rudy

Viewing 14 posts - 1 through 13 (of 13 total)

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

SQLCharger SSCommitted Points: 1928 More actions · Answer 1

Useful utility, thanks Rudy:-)

Cheers,

JohnA

MCM: SQL2008

Rudy Panigas SSChampion Points: 10702 More actions · Answer 2

I hope to bring more utilities soon.

Thanks for your comments

Rudy

brian_w SSC Enthusiast Points: 192 More actions · Answer 3

For SQL 2008, the following will return a "specified service does not exist as an installed service" error:

EXEC master.dbo.xp_servicecontrol N'QUERYSTATE',N'MsDtsServer'

I changed the service name to MsDtsServer100 and it returned "Running.":

EXEC master.dbo.xp_servicecontrol N'QUERYSTATE',N'MsDtsServer100'

Rudy Panigas SSChampion Points: 10702 More actions · Answer 4

Thanks for the update.

Do you know if this change will work on SQL 2005 installation? i don't think this will. Guess there may need to be a version of SQL '05 and '08

Thanks for take the time to look into this.

Rudy

brian_w SSC Enthusiast Points: 192 More actions · Answer 5

No, 2005 and 2008 are different.

Here's a listing of the service names:http://blogs.technet.com/b/fort_sql/archive/2010/05/31/list-of-sql-server-service-names.aspx

Great script, by the way.

brian_w SSC Enthusiast Points: 192 More actions · Answer 6

Also, the registry keys are different:

Had to change:

SET @REGKEY = 'System\CurrentControlSet\Services\MsDtsServer'

to

SET @REGKEY = 'System\CurrentControlSet\Services\MsDtsServer100'

MiguelSQL Hall of Fame Points: 3112 More actions · Answer 7

WOW, excellent script.

However, this only give you the information for a single instance.

These script could be modified to loop though all the instances.

My addition:

To get a list of the instances on that machine, use the following:

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

create table #instances

(c1 varchar (100),

c2 varchar (100))

insert into #instances

EXEC master..xp_regenumvalues 'HKEY_LOCAL_MACHINE','SOFTWARE\Microsoft\Microsoft SQL Server\Instance Names\SQL'

select c1 from #instances

drop table #instances.

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

Rudy Panigas SSChampion Points: 10702 More actions · Answer 8

Good idea about looking at all the instances. When I wrote this code, I was using it as a subset for another code that would allow me to collect all SQL server information on a per instance bases. This code (still working on it) will create information that will allow you to rebuild a server exactly like it was originally.

Glad to see that others are finding this useful.

Thanks for all the comments!

Rudy

SQLCharger SSCommitted Points: 1928 More actions · Answer 9

A useful addition might be the option to display the service account.

Rudy, I've got a script that returns the SQLService and SQLAgent accounts for an instance, but it would be really nice if it could be incorporated in your code returning all service accounts.

Lots of people have SQL instances running under the wrong accounts without realising it. If a test instance (for example) runs under the same service account as a live instance, then test jobs may have (accidentally) too much potential access to Live 😉

Cheers,

JohnA

MCM: SQL2008

Rudy Panigas SSChampion Points: 10702 More actions · Answer 10

John,

Good idea! Do you think we can all see this code of your? Originally, I did not include service accounts before we have a separate network for our production and development server so I did not think about adding that feature.

Glad to see everyone's ideas on how to enhance this code.

Thanks,

Rudy

SQLCharger SSCommitted Points: 1928 More actions · Answer 11

Rudy,

The code below returns the names of SQLService and SQLAgent accounts.

Works on 2000, 2005 and 2008 as far as I can tell:

DECLARE @NamedInstance bit

IF CAST(SERVERPROPERTY('ServerName') AS varchar) LIKE '%\%' SET @NamedInstance = 1 ELSE SET @NamedInstance = 0

DECLARE @ServiceName varchar(50)

IF @NamedInstance = 0

BEGIN

SET @ServiceName = 'MSSQLSERVER'

END

ELSE

BEGIN

SET @ServiceName = 'MSSQL$' + RIGHT(CAST(SERVERPROPERTY('ServerName') AS varchar),LEN(CAST(SERVERPROPERTY('ServerName') AS varchar)) - CHARINDEX('\',CAST(SERVERPROPERTY('ServerName') AS varchar),1))

END

DECLARE @KEY_VALUE varchar(100)

DECLARE @ServiceAccountName varchar(100)

SET @KEY_VALUE = 'SYSTEM\CurrentControlSet\Services\' + @ServiceName

EXECUTE master..xp_regread 'HKEY_LOCAL_MACHINE', @KEY_VALUE, 'ObjectName', @ServiceAccountName OUTPUT

SELECT @ServiceAccountName as 'SQLService Account'

IF CAST(SERVERPROPERTY('ServerName') AS varchar) LIKE '%\%' SET @NamedInstance = 1 ELSE SET @NamedInstance = 0

IF @NamedInstance = 0

BEGIN

SET @ServiceName = 'SQLSERVERAGENT'

END

ELSE

BEGIN

SET @ServiceName = 'SQLAgent$' + RIGHT(CAST(SERVERPROPERTY('ServerName') AS varchar),LEN(CAST(SERVERPROPERTY('ServerName') AS varchar)) - CHARINDEX('\',CAST(SERVERPROPERTY('ServerName') AS varchar),1))

END

SET @KEY_VALUE = 'SYSTEM\CurrentControlSet\Services\' + @ServiceName

EXECUTE master..xp_regread 'HKEY_LOCAL_MACHINE', @KEY_VALUE, 'ObjectName', @ServiceAccountName OUTPUT

SELECT @ServiceAccountName as 'SQLAgent Account'

Cheers,

JohnA

MCM: SQL2008

MiguelSQL Hall of Fame Points: 3112 More actions · Answer 12

Hi,

I actually worked on this issue for almost half a day and I couldn't resolve a snack with John's code a few days ago, and I just gave up. 🙁

He uses the following

DECLARE @KEY_VALUE varchar(100)

DECLARE @ServiceAccountName varchar(100)

EXECUTE master..xp_regread 'HKEY_LOCAL_MACHINE', @KEY_VALUE, 'ObjectName', @ServiceAccountName OUTPUT

SELECT @ServiceAccountName as 'SQLAgent Account'

however, I've run that code on several servers and in some of them the result is 'NULL'.

(it doesn't matter the service, it happens with several services).

The only thing I could figure out is that when I run the following code:

declare @regkey varchar (100)

SET @REGKEY = 'System\CurrentControlSet\Services\SQLAgent$instance_name'

EXEC master..xp_regenumvalues @rootkey='HKEY_LOCAL_MACHINE',@key=@regkey

for the Object value I get the following:

Value data value data

ObjectName.\sqlserviceNULLNULL

It seems that for some cases the registry key has two sets of values, weird.

Anyone has ideas of why this might be?

Thank you

Miguel

trea_johnson SSC-Addicted Points: 423 More actions · Answer 13

Excellent script, Rudy. Thanks a mint for sharing it.