Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

SQL Server Service Check Utility Expand / Collapse
Author
Message
Posted Saturday, November 13, 2010 12:01 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, October 7, 2014 11:08 AM
Points: 316, Visits: 1,117
Comments posted to this topic are about the item SQL Server Service Check Utility


Post #1020357
Posted Monday, November 15, 2010 4:53 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, April 17, 2014 1:41 AM
Points: 170, Visits: 1,400
Useful utility, thanks Rudy

Cheers,

JohnA

MCM: SQL2008
Post #1020664
Posted Monday, November 15, 2010 6:31 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, October 7, 2014 11:08 AM
Points: 316, Visits: 1,117
I hope to bring more utilities soon.

Thanks for your comments



Post #1020712
Posted Monday, November 15, 2010 7:23 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, September 22, 2014 9:41 AM
Points: 12, Visits: 315
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'

Post #1020770
Posted Monday, November 15, 2010 7:27 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, October 7, 2014 11:08 AM
Points: 316, Visits: 1,117
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



Post #1020772
Posted Monday, November 15, 2010 7:42 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, September 22, 2014 9:41 AM
Points: 12, Visits: 315
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.
Post #1020783
Posted Monday, November 15, 2010 8:16 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, September 22, 2014 9:41 AM
Points: 12, Visits: 315
Also, the registry keys are different:

Had to change:

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

to

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

Post #1020803
Posted Monday, November 15, 2010 10:57 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Yesterday @ 1:53 PM
Points: 97, Visits: 765
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.
------------------------------------

Post #1020936
Posted Monday, November 15, 2010 11:08 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, October 7, 2014 11:08 AM
Points: 316, Visits: 1,117
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



Post #1020940
Posted Tuesday, November 16, 2010 2:00 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, April 17, 2014 1:41 AM
Points: 170, Visits: 1,400
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
Post #1021293
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse