February 20, 2011 at 5:01 pm
I want to find out if my sql services including agent,full text,sql engine are running under automatic or manual mode. Is there any sql query i can use to do that? Thanks
February 20, 2011 at 6:46 pm
I would suggest PowerShell if you have it running on your servers (prior to Window Server 2008). Look at the Get-Service cmdlet.
Shawn Melton
Twitter: @wsmelton
Blog: wsmelton.github.com
Github: wsmelton
February 21, 2011 at 9:13 am
Unfortunately we do not have power shell running.Thanks
February 21, 2011 at 6:36 pm
VBScript to call WMI objects would be your only other option. I know you can get scripts with Google search to check status and certain properties. I have done this before long time ago, but I don't recall one to check the startup mode, but there is bound to be one.
Shawn Melton
Twitter: @wsmelton
Blog: wsmelton.github.com
Github: wsmelton
February 21, 2011 at 7:46 pm
In sql server you have XP_Servicecontrol which will tell you whether your service is status (running,stopped,etc) and you can use this to start and stop the sql services.
However you need to get the Service info , you can use the WMI script and call them from sql server using xp_cmdshell and store them in sql server
Regards,
Subbu
Click here to Get Speedy answer or solution
March 2, 2011 at 8:36 am
In-case you ever do get PowerShell in use PowerShell.com posted a tip today for the one-liner command to use:
Determining Service Start Mode
Shows you how to query WMI through PowerShell to pull out the service name and startup mode.
If you can't get to the site this is what it has:
SOURCE: PowerShell.com Tip of the Day:
By using WMI, you can enumerate the start mode that you want your services to use. To get a list of all services, try this:
Get-WMIObject Win32_Service | Select-Object Name, StartMode
If you want to find out the start mode of one specific service, try this instead:
([wmi]'Win32_Service.Name="Spooler"').StartMode
Shawn Melton
Twitter: @wsmelton
Blog: wsmelton.github.com
Github: wsmelton
March 2, 2011 at 8:46 am
This statement in CMD will give you the state of all services:
sc query state= all
You will just need to strip the info you need out... I have a procedure that strips out the data I need from an XP_CMDSHELL script:
CREATE PROCEDURE [dbo].[usp_ins_InfoSQLService]
AS
SET NOCOUNT ON
DECLARE @iIDINT
DECLARE @SQLVARCHAR(MAX)
DECLARE @tServiceNameVARCHAR(100)
DECLARE @tmpOutputTABLE
(
iIDINT IDENTITY(1,1)
,tOutputVARCHAR(MAX)
)
DECLARE @tmpDetailTABLE
(
iIDINT IDENTITY(1,1)
,tDetailVARCHAR(MAX)
)
DELETE FROM dbo.[tbl_InfoSQLService]
INSERT INTO @tmpOutput
(
tOutput
)
EXEC xp_cmdshell 'sc query state= all | findstr Sql'
INSERT INTO @tmpOutput
(
tOutput
)
EXEC xp_cmdshell 'sc query state= all | findstr sql'
INSERT INTO @tmpOutput
(
tOutput
)
EXEC xp_cmdshell 'sc query state= all | findstr SQL'
INSERT INTO @tmpOutput
(
tOutput
)
EXEC xp_cmdshell 'sc query state= all | findstr DTS'
INSERT INTO @tmpOutput
(
tOutput
)
EXEC xp_cmdshell 'sc query state= all | findstr dts'
INSERT INTO @tmpOutput
(
tOutput
)
EXEC xp_cmdshell 'sc query state= all | findstr Dts'
DELETE FROM @tmpOutput
WHERE tOutput IS NULL
OR LEFT(tOutput, 7) = 'DISPLAY'
UPDATE @tmpOutput
SET tOutput = LTRIM(RTRIM(REPLACE(tOutput, 'SERVICE_NAME:', '')))
WHILE (SELECT COUNT(*) FROM @tmpOutput) > 0
BEGIN
SELECT
@iID = iID
,@SQL = 'EXEC xp_cmdshell ''sc qc ' + tOutput + ''';'
FROM @tmpOutput
ORDER BY iID
INSERT INTO @tmpDetail
(
tDetail
)
EXEC(@SQL)
DELETE FROM @tmpDetail
WHERE tDetail IS NULL
SELECT
@tServiceName = LTRIM(RTRIM(REPLACE(tDetail, 'SERVICE_NAME: ', '')))
FROM @tmpDetail
WHERE LTRIM(RTRIM(tDetail)) LIKE 'SERVICE_NAME%'
INSERT INTO dbo.[tbl_InfoSQLService]
(
HostName
,ServiceName
)
SELECT
@@SERVERNAME
,LTRIM(RTRIM(REPLACE(tDetail, 'SERVICE_NAME: ', '')))
FROM @tmpDetail
WHERE LTRIM(RTRIM(tDetail)) LIKE 'SERVICE_NAME%'
UPDATE dbo.[tbl_InfoSQLService]
SET StartType =
(
SELECT LTRIM(RTRIM(REPLACE(tDetail, 'START_TYPE :', '')))
FROM @tmpDetail
WHERE LTRIM(RTRIM(tDetail)) LIKE 'START_TYPE%'
)
WHERE ServiceName = @tServiceName
UPDATE dbo.[tbl_InfoSQLService]
SET ServiceStartName =
(
SELECT LTRIM(RTRIM(REPLACE(tDetail, 'SERVICE_START_NAME : ', '')))
FROM @tmpDetail
WHERE LTRIM(RTRIM(tDetail)) LIKE 'SERVICE_START_NAME%'
)
WHERE ServiceName = @tServiceName
DELETE FROM @tmpOutput
WHERE iID = @iID
DELETE FROM @tmpDetail
END
SET NOCOUNT OFF
Although, looking at it, I think the PowerShell script might be a little "easier" :hehe:
Hope this helps
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply