October 7, 2015 at 12:34 am
good morning
I have a script which stops the sql server agent,do some coding,then start the sql server agent again.
I works perfectly when I run it from a query.
Now I want to create a job to do the script.
But the sql server agent stops and then the rest of the code do not happen.And the agent is not started also.
I want to put it in a job,so that it can be scheduled.
Any ideas how to get it to run.
here is my code:
-- STOP SQL Server Agent
EXEC xp_servicecontrol N'STOP',N'SQLServerAGENT'
GO
begin try
-- kill all delete statements
DECLARE @dbName SYSNAME
DECLARE @sqlCmd VARCHAR(MAX)
DECLARE @MyTempSessionsTBL Table (Session_ID int)
SET @sqlCmd = ''
create table #MyTempSessionsTBL
(session_id bigint,scommand varchar(500))
INSERT INTO #MyTempSessionsTBL
(Session_ID,scommand)
Select DISTINCT a.Session_ID,a.command
FROM sys.dm_exec_requests a with (nolock)
Order by Session_ID
SELECT @sqlCmd = @sqlCmd + 'KILL ' + CAST(Session_ID AS VARCHAR) +
CHAR(13)
FROM #MyTempSessionsTBL
where scommand='delete' or scommand='update'
ORDER BY Session_ID
select * from #MyTempSessionsTBL
order by scommand
select @sqlCmd
drop table #MyTempSessionsTBL
--Uncomment below line to kill
Begin Try
EXEC (@sqlCmd)
End Try
Begin Catch
End Catch
end try
begin catch
end catch
begin try
alter database msdb set recovery simple
end try
begin catch
end catch
go
select '1'
begin try
use tempdb
dbcc shrinkfile(N'templog',14000)
dbcc shrinkfile(N'templog',1400)
select '2'
use tempdb
dbcc shrinkfile(N'templog',0, TruncateONLy)
select '3'
dbcc shrinkfile(N'templog',140000)
select '3'
dbcc shrinkfile(N'templog',14000)
select '1'
use tempdb
dbcc shrinkfile(N'tempdev',140000)
dbcc shrinkfile(N'tempdev',14000)
select '2'
use tempdb
dbcc shrinkfile(N'tempdev',0, TruncateONLy)
select '3'
dbcc shrinkfile(N'tempdev',140000)
dbcc shrinkfile(N'tempdev',14000)
end try
begin catch
end catch
go
begin try
select '1'
use msdb
dbcc shrinkfile(N'msdblog',140000)
dbcc shrinkfile(N'msdblog',14000)
select '2'
use msdb
dbcc shrinkfile(N'msdblog',0, TruncateONLy)
select '3'
dbcc shrinkfile(N'msdblog',140000)
select '3'
dbcc shrinkfile(N'msdblog',14000)
select '1'
use msdb
dbcc shrinkfile(N'msdbdata',140000)
dbcc shrinkfile(N'msdbdata',14000)
select '2'
use msdb
dbcc shrinkfile(N'msdbdata',0, TruncateONLy)
select '3'
dbcc shrinkfile(N'msdbdata',140000)
dbcc shrinkfile(N'msdbdata',14000)
end try
begin catch
end catch
go
begin try
USE [database]
select '1.1'
begin try
DBCC SHRINKFILE (N'database' , 1202452)
end try
begin catch
end catch
begin try
DBCC SHRINKFILE (N'database' , 4801932)
end try
begin catch
end catch
begin try
USE [database]
DBCC SHRINKFILE (N'database_log' , 0)
end try
begin catch
end catch
begin try
USE [database]
select '1.1'
DBCC SHRINKFILE (N'database' , 3801932)
end try
begin catch
end catch
begin try
select '1.2'
DBCC SHRINKFILE (N'database' , 3301932)
select '1.3'
end try
begin catch
end catch
begin try
DBCC SHRINKFILE (N'database' , 28301932)
select '1.4'
end try
begin catch
end catch
begin try
DBCC SHRINKFILE (N'database' , 2301932)
select '1'
end try
begin catch
end catch
begin try
DBCC SHRINKFILE (N'database' , 1801932)
select '2'
end try
begin catch
end catch
begin try
DBCC SHRINKFILE (N'database' , 1301932)
select '3'
end try
begin catch
end catch
begin try
DBCC SHRINKFILE (N'database' , 801932)
select '4'
end try
begin catch
end catch
begin try
DBCC SHRINKFILE (N'database' , 301932)
select '5'
end try
begin catch
end catch
begin try
DBCC SHRINKFILE (N'database' , 0)
end try
begin catch
end catch
end try
begin catch
end catch
GO
-- START SQL Server Agent
EXEC xp_servicecontrol N'START',N'SQLServerAGENT'
GO
October 7, 2015 at 12:46 am
Clearly not using SQL Agent!
You'll need to use some other task scheduler. Or tell us why SQL Agent needs to be stopped while running these commands – there may be another way.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply