86 lines
2.6 KiB
Transact-SQL
86 lines
2.6 KiB
Transact-SQL
/*=============================================================================
|
|
|
|
check si une db hciTools existe, ainsi qu'un job avec "wait stats" dans son nom sur 1 serveur
|
|
|
|
-----------------------------------------------
|
|
us 336, pour faire le drop des jobs non standardisés
|
|
|
|
Contexte d'utilisation
|
|
----------------------
|
|
sur toute les instances avec une db hciTools et ayant un job dont la logique correspond à xxx
|
|
|
|
Création : 12.10.2022 / TSC
|
|
|
|
=============================================================================*/
|
|
USE [master];
|
|
GO
|
|
SET XACT_ABORT ON;
|
|
SET NOCOUNT ON;
|
|
|
|
DECLARE @db_hci BIT = 0;
|
|
DECLARE @tbl_job_name TABLE (job_id UNIQUEIDENTIFIER,
|
|
job_name VARCHAR(500),
|
|
matched_by VARCHAR(500),
|
|
treated BIT
|
|
DEFAULT 0);
|
|
|
|
SELECT @db_hci = 1
|
|
FROM sys.databases db
|
|
WHERE name = 'HCITools';
|
|
|
|
IF @db_hci = 1
|
|
BEGIN
|
|
--la db hci_tools existe, on check le job
|
|
INSERT INTO @tbl_job_name ([job_name],
|
|
[job_id],
|
|
[matched_by])
|
|
SELECT [name],
|
|
[sj].[job_id],
|
|
'keyword "wait stats" in name'
|
|
FROM msdb.dbo.sysjobs sj
|
|
WHERE LOWER(sj.[name]) LIKE '%wait stat%'
|
|
AND LOWER(sj.[name]) <> '_D92060 - Monitor Wait Stats';
|
|
|
|
INSERT INTO @tbl_job_name ([job_name],
|
|
[job_id],
|
|
[matched_by])
|
|
SELECT sj.[name],
|
|
sj.[job_id],
|
|
'dmv "dm_os_wait_stats" in code'
|
|
FROM msdb.dbo.sysjobs sj
|
|
INNER JOIN msdb.dbo.[sysjobsteps] sp
|
|
ON [sp].[job_id] = [sj].[job_id]
|
|
WHERE [sp].[command] LIKE '%sys.dm_os_wait_stats%'
|
|
AND LOWER(sj.[name]) <> '_D92060 - Monitor Wait Stats';
|
|
END;
|
|
|
|
IF EXISTS (SELECT 1 FROM @tbl_job_name)
|
|
AND @db_hci = 1
|
|
BEGIN
|
|
DECLARE @job_name VARCHAR(500);
|
|
DECLARE @job_id UNIQUEIDENTIFIER;
|
|
DECLARE @job_source VARCHAR(500);
|
|
|
|
WHILE EXISTS (SELECT 1 FROM @tbl_job_name WHERE [treated] = 0)
|
|
BEGIN
|
|
SELECT TOP (1) @job_name = [j].[job_name],
|
|
@job_id = [j].[job_id],
|
|
@job_source = j.[matched_by]
|
|
FROM @tbl_job_name j
|
|
WHERE [j].[treated] = 0;
|
|
|
|
RAISERROR(
|
|
'Non standard job seems to be here, check if "%s" should be dropped. (matched by %s)',
|
|
1,
|
|
1,
|
|
@job_name,
|
|
@job_source) WITH NOWAIT;
|
|
|
|
UPDATE @tbl_job_name
|
|
SET [treated] = 1
|
|
WHERE [job_id] = @job_id
|
|
AND [job_name] = @job_name;
|
|
END;
|
|
END;
|
|
|