sql - 查找要删除的引用不存在的数据库的 SQL 作业
问题描述
我试图找到我们所有不再需要的 SQL 作业,因为它们使用的数据库不再存在。
逻辑很简单,选择带有命令的作业列表,然后在命令中检查数据库是否列在 sysdatabase 表中。到目前为止,这就是我所拥有的,几乎就在那里:
SELECT
Srv.srvname AS ServerName,
Job.name AS JobName,
JStep.step_id,
--JStep.step_name AS StepName,
JStep.command
--,Job.enabled
into #JobListTable
FROM msdb.dbo.sysjobs Job
JOIN msdb.dbo.sysjobsteps JStep
ON JStep.job_id = Job.job_id
JOIN MASTER.dbo.sysservers Srv
ON Srv.srvid = Job.originating_server_id
WHERE JStep.subsystem='SSIS'
--SELECT * FROM #JobListTable
DECLARE @JobName SYSNAME, @StepId INT, @Command NVARCHAR(MAX),@PosStart BIGINT, @PosEnd BIGINT, @Server NVARCHAR(128),@JobList VARCHAR(MAX), @DBNAMETEMP SYSNAME, @SQLStatement VARCHAR(MAX)
DECLARE @MAXID INT, @Counter INT
SET @COUNTER = 1
SELECT @MAXID = COUNT(*) FROM #JobListTable
WHILE (@COUNTER <= @MAXID)
--WHILE (@COUNTER <= 3)
BEGIN
SET @JobName=(SELECT top 1 JobName FROM #JobListTable)-- WHERE command=@Command)
SET @command=(SELECT top 1 command FROM #JobListTable)-- WHERE JobName=@JobName)
SET @StepId=(SELECT top 1 step_id FROM #JobListTable)
SELECT @Command = [s].[command]
FROM msdb.dbo.[sysjobs] [j]
INNER JOIN msdb.dbo.[sysjobsteps] [s] ON [s].[job_id] = [j].[job_id]
WHERE [j].[name] = @JobName
AND [s].[step_id] = @StepId
SET @PosStart = PATINDEX('%/SERVER %', @Command)+8
SET @PosEnd = CHARINDEX(' ', @Command, @PosStart)
SET @Server = SUBSTRING(@Command, @PosStart, @PosEnd - @PosStart)
SET @PosStart = PATINDEX('%"\"%', @Command)+3
SET @PosEnd = PATINDEX('%\""%', @Command)
SET @Command = SUBSTRING(@Command, @PosStart, @PosEnd - @PosStart)
--select
--right(@Command, len(@Command) - charindex('Initial Catalog=',@Command ,1)+1) DBIndexPos,
--charindex(';',right(@Command, len(@Command) - charindex('Initial Catalog=',@Command ,1)+1),1) SemiColonPos,
SET @DBNAMETEMP =
replace(left(right(@Command, len(@Command) - charindex('Initial Catalog=',@Command ,1)+1), charindex(';',right(@Command, len(@Command) - charindex('Initial Catalog=',@Command ,1)+1),1)-1),'Initial Catalog=','') --DatabaseName
--PRINT @DBNAMETEMP
select 'exec msdb..sp_delete_jobstep @job_name = ''' + j.name + ''', @step_id = ' + convert(varchar(100), js.step_id)
from msdb..sysjobs j
inner join msdb..syscategories c
on c.category_id = j.category_id
inner join msdb..sysjobsteps js
on js.job_id = j.job_id
where @DBNAMETEMP not in (select name from sysdatabases)
SET @SQLStatement= 'EXEC msdb.dbo.sp_delete_job @job_name =''' + @jobName + ''', @delete_unused_schedule=1'
PRINT(@SQLStatement)
--select * from #dblist
SET @COUNTER = @COUNTER + 1
DELETE FROM #JobListTable WHERE JobName=@JobName
END
DROP TABLE #JobListTable
--DROP TABLE #dblist
解决方案
我只需要从 msdb.dbo.sysjobs 和 msdb.dbo.sysjobsteps 获得的所有作业和作业步骤。然后我们可以简单地在 sys.databases 中检查作业数据库当前是否可用。请检查以下查询:
SELECT
job.job_id,
notify_level_email,
name,
enabled,
description,
step_name,
command,
server,
database_name
FROM
msdb.dbo.sysjobs job
INNER JOIN
msdb.dbo.sysjobsteps steps
ON
job.job_id = steps.job_id
WHERE database_name not in ( select name from sys.databases)
推荐阅读
- .htaccess - .htaccess 中的 AuthName 未显示在 Chrome 中
- symfony4 - Knp_paginator 模板问题
- react-native - React Native Appium 找不到样式位置视图的自动化 ID:iOS 中的绝对
- angular - Angular8 TypeError:无法读取未定义的属性“forEach”
- clojure - 如何引用它存在的映射中的键?
- android - 无法删除 vcs android studio 中的代理设置
- json - Ansible-使用键匹配将两个嵌套的 json 文件合并为单个文件
- javascript - 温斯顿登录生产
- css - 在 jqgrid 的表格中启用水平滚动条
- ios - 配置文件“iOS 团队配置文件:...不支持关联域功能