May 29, 2008 at 7:32 am
I would like to use SQLCMD to do the following tasks:
(1) Connect to the SQL Instance
(2) Check whether any jobs are currently running
(3) If no jobs running, stop the local SQL Server Agent Service
(4) If any jobs are running, keep checking every few min
(5) Once it is determined no jobs are running, stop the SQL Agent.
Does anyone have a script that does this sort of thing (or something similar)?
Is it possible to implement loop logic in a SQLCMD script?
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
May 29, 2008 at 8:30 am
I think you'd implement this as T-SQL logic, a stored proc perhaps, and then call that from SQLCMD.
May 29, 2008 at 11:18 am
Steve Jones - Editor (5/29/2008)
I think you'd implement this as T-SQL logic, a stored proc perhaps, and then call that from SQLCMD.
I'm almost there, but I'm stuck trying to pass a parameter from the sql script that checks whether any jobs are running back to the batch file that determines whether or not the agent should be stopped.
My SQL script is this:
DECLARE @stopAgent BINARY
CREATE TABLE #JobStatus
(
job_id uniqueidentifier,
LastRunDate int,
LastRunTime int,
NextRunDate int,
NextRunTime int,
NextRunSchedule int,
RequestedToRunint,
Request_Source int,
Request_Source_ID varchar(100),
Running int,
CurrentStep int,
CurrentRetryAttempt int,
[State] int
)
INSERT INTO #JobStatus
EXEC master.dbo.xp_sqlagent_enum_jobs 1, '';
WHILE ( 1 = 1 )
BEGIN
IF EXISTS(SELECT * FROM #JobStatus S JOIN msdb.dbo.SYSJOBS J (nolock)
ON S.job_id = J.job_id
WHERE S.State = 1 )
BEGIN --JOBS STILL RUNNING
SET @stopAgent = 0
END
ELSE
BEGIN --No Jobs running!!!
SET @stopAgent = 1
BREAK; --exit loop
END
WAITFOR DELAY '00:00:10';-- wait for 10 SEC
END
DROP TABLE #JobStatus;
My batch file is this:
!! IF (%stopAgent%)==(1) net stop SQLAgent$instanceName
How can I make the 2 talk to each other?
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
May 29, 2008 at 2:41 pm
I managed to resolve this the following way.
main.bat:
sqlcmd -S srvr\instance -i D:\checkJobsRunning.sql
sqlcmd -S srvr\instance -i D:\stopAgent.bat
checkJobsRunning.sql:
SET NOCOUNT ON
CREATE TABLE #JobStatus
(
job_id uniqueidentifier,
LastRunDate int,
LastRunTime int,
NextRunDate int,
NextRunTime int,
NextRunSchedule int,
RequestedToRunint,
Request_Source int,
Request_Source_ID varchar(100),
Running int,
CurrentStep int,
CurrentRetryAttempt int,
[State] int
)
INSERT INTO #JobStatus
EXEC master.dbo.xp_sqlagent_enum_jobs 1, '';
WHILE ( 1 = 1 )
BEGIN
IF EXISTS(SELECT * FROM #JobStatus S JOIN msdb.dbo.SYSJOBS J (nolock)
ON S.job_id = J.job_id
WHERE S.State = 1 )
BEGIN -- jobs still running
TRUNCATE TABLE #JobStatus;
INSERT INTO #JobStatus
EXEC master.dbo.xp_sqlagent_enum_jobs 1, '';
END
ELSE
BEGIN -- no jobs running
BREAK; --exit loop
END
WAITFOR DELAY '00:00:10';-- wait for 10 SEC
END
DROP TABLE #JobStatus;
stopAgent.bat:
!! net stop SQLAgent$instance
on the command prompt I ran the following:
main.bat
checkJobsRunning.sql keeps on checking every 10 sec whether any jobs are running. If there are jobs running, it continues checking every 10 sec, otherwise it stops. Then the second batch kicks in, stopAgent.bat, and kills the agent.
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply