22,209
社区成员
发帖
与我相关
我的任务
分享
USE master
go
select
request_session_id as spid,
resource_type,
db_name(resource_database_id) as dbName,
resource_description,
resource_associated_entity_id,
OBJECT_NAME(resource_associated_entity_id,DB_ID('PayCenter')) AS TabName,
request_mode as mode,
request_status as Status
from
sys.dm_tran_locks
DECLARE @is_sysadmin INT
, @job_owner sysname
, @job_state INT
, @job_id_as_char VARCHAR(36)
,@Sql NVARCHAR(max)=''
SET NOCOUNT ON
IF OBJECT_ID('Tempdb..#xp_results') IS NOT NULL
DROP TABLE #xp_results
CREATE TABLE #xp_results (job_id UNIQUEIDENTIFIER NOT NULL,
last_run_date INT NOT NULL,
last_run_time INT NOT NULL,
next_run_date INT NOT NULL,
next_run_time INT NOT NULL,
next_run_schedule_id INT NOT NULL,
requested_to_run INT NOT NULL, -- BOOL
request_source INT NOT NULL,
request_source_id sysname collate database_default null,
running INT NOT NULL, -- BOOL
current_step INT NOT NULL,
current_retry_attempt INT NOT NULL,
job_state INT NOT NULL)
SELECT @is_sysadmin = ISNULL(IS_SRVROLEMEMBER(N'sysadmin'), 0)
SELECT @job_owner = suser_sname(suser_sid())
INSERT INTO #xp_results
EXECUTE master.dbo.xp_sqlagent_enum_jobs @is_sysadmin, @job_owner
SELECT @Sql=@Sql+'EXEC msdb.dbo.sp_stop_job @job_id='+QUOTENAME(a.job_id,'''')+';' FROM #xp_results AS a WHERE a.job_state=1 AND EXISTS(SELECT 1 FROM msdb.dbo.sysjobs WHERE job_id=a.job_id AND name IN('A',N'B','C'))
EXEC(@Sql)