SQLServerCentral / Discuss content posted by Rudy Panigas / Article Discussions / Article Discussions by Author / SQL Server Service Check Utility / Latest Posts

RE: SQL Server Service Check Utility

trea_johnson — Sun, 12 Dec 2010 14:19:09 GMT

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

RE: SQL Server Service Check Utility

MiguelSQL — Fri, 19 Nov 2010 09:59:17 GMT

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[code="sql"]DECLARE @KEY_VALUE varchar(100)DECLARE @ServiceAccountName varchar(100) EXECUTE master..xp_regread 'HKEY_LOCAL_MACHINE', @KEY_VALUE, 'ObjectName', @ServiceAccountName OUTPUTSELECT @ServiceAccountName as 'SQLAgent Account'[/code]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:[code="sql"]declare @regkey varchar (100)SET @REGKEY = 'System\CurrentControlSet\Services\SQLAgent$instance_name'EXEC master..xp_regenumvalues @rootkey='HKEY_LOCAL_MACHINE',@key=@regkey[/code]for the Object value I get the following:[code="other"]Value data value dataObjectName .\sqlservice NULL NULL[/code]It seems that for some cases the registry key has two sets of values, weird.Anyone has ideas of why this might be?Thank youMiguel

RE: SQL Server Service Check Utility

SQLCharger — Fri, 19 Nov 2010 08:15:42 GMT

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 bitIF CAST(SERVERPROPERTY('ServerName') AS varchar) LIKE '%\%' SET @NamedInstance = 1 ELSE SET @NamedInstance = 0DECLARE @ServiceName varchar(50)IF @NamedInstance = 0BEGINSET @ServiceName = 'MSSQLSERVER'ENDELSEBEGINSET @ServiceName = 'MSSQL$' + RIGHT(CAST(SERVERPROPERTY('ServerName') AS varchar),LEN(CAST(SERVERPROPERTY('ServerName') AS varchar)) - CHARINDEX('\',CAST(SERVERPROPERTY('ServerName') AS varchar),1))ENDDECLARE @KEY_VALUE varchar(100)DECLARE @ServiceAccountName varchar(100) SET @KEY_VALUE = 'SYSTEM\CurrentControlSet\Services\' + @ServiceNameEXECUTE master..xp_regread 'HKEY_LOCAL_MACHINE', @KEY_VALUE, 'ObjectName', @ServiceAccountName OUTPUTSELECT @ServiceAccountName as 'SQLService Account'IF CAST(SERVERPROPERTY('ServerName') AS varchar) LIKE '%\%' SET @NamedInstance = 1 ELSE SET @NamedInstance = 0IF @NamedInstance = 0BEGINSET @ServiceName = 'SQLSERVERAGENT'ENDELSEBEGINSET @ServiceName = 'SQLAgent$' + RIGHT(CAST(SERVERPROPERTY('ServerName') AS varchar),LEN(CAST(SERVERPROPERTY('ServerName') AS varchar)) - CHARINDEX('\',CAST(SERVERPROPERTY('ServerName') AS varchar),1))ENDSET @KEY_VALUE = 'SYSTEM\CurrentControlSet\Services\' + @ServiceNameEXECUTE master..xp_regread 'HKEY_LOCAL_MACHINE', @KEY_VALUE, 'ObjectName', @ServiceAccountName OUTPUTSELECT @ServiceAccountName as 'SQLAgent Account'

RE: SQL Server Service Check Utility

Rudy Panigas — Tue, 16 Nov 2010 06:37:17 GMT

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

RE: SQL Server Service Check Utility

SQLCharger — Tue, 16 Nov 2010 02:00:32 GMT

A useful addition might be the option to display the [b]service account[/b].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 ;-)

RE: SQL Server Service Check Utility

Rudy Panigas — Mon, 15 Nov 2010 11:08:29 GMT

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

RE: SQL Server Service Check Utility

MiguelSQL — Mon, 15 Nov 2010 10:57:53 GMT

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:[code="sql"]------------------------------------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 #instancesdrop table #instances.------------------------------------[/code]

RE: SQL Server Service Check Utility

brian_w — Mon, 15 Nov 2010 08:16:36 GMT

Also, the registry keys are different:Had to change:[code="sql"]SET @REGKEY = 'System\CurrentControlSet\Services\MsDtsServer'[/code]to [code="sql"]SET @REGKEY = 'System\CurrentControlSet\Services\MsDtsServer100'[/code]

RE: SQL Server Service Check Utility

brian_w — Mon, 15 Nov 2010 07:42:38 GMT

No, 2005 and 2008 are different. Here's a listing of the service names:[url=http://blogs.technet.com/b/fort_sql/archive/2010/05/31/list-of-sql-server-service-names.aspx]http://blogs.technet.com/b/fort_sql/archive/2010/05/31/list-of-sql-server-service-names.aspx[/url]Great script, by the way.

RE: SQL Server Service Check Utility

Rudy Panigas — Mon, 15 Nov 2010 07:27:19 GMT

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 '08Thanks for take the time to look into this.Rudy

RE: SQL Server Service Check Utility

brian_w — Mon, 15 Nov 2010 07:23:56 GMT

For SQL 2008, the following will return a "specified service does not exist as an installed service" error: [code="sql"]EXEC master.dbo.xp_servicecontrol N'QUERYSTATE',N'MsDtsServer'[/code]I changed the service name to MsDtsServer100 and it returned "Running.":[code="sql"]EXEC master.dbo.xp_servicecontrol N'QUERYSTATE',N'MsDtsServer100'[/code]

RE: SQL Server Service Check Utility

Rudy Panigas — Mon, 15 Nov 2010 06:31:21 GMT

I hope to bring more utilities soon.Thanks for your comments

RE: SQL Server Service Check Utility

SQLCharger — Mon, 15 Nov 2010 04:53:21 GMT

Useful utility, thanks Rudy:-)

SQL Server Service Check Utility

Rudy Panigas — Sat, 13 Nov 2010 12:01:34 GMT

Comments posted to this topic are about the item [B]SQL Server Service Check Utility [/B]