Sometimes I need to stop alle jobs on a SQL server for some reason, but SQL jobs need to be stoppend and I also want to make sure thas they not automatic start again, until I want them to start.<\/p>\n\n\n\n
Therefore I wrote a scipt to disable SQL jobs, log which jobs was disables and waited for alle SQL jobs to finish. After running the script, I can do my maintenance without worrying about if something should be running.<\/p>\n\n\n\n
The script creates a table in master database, so if I need to upgrade my SQL server, I choose another database for the table.<\/p>\n\n\n\n
USE master\nGO\n\nDROP TABLE IF EXISTS DisabledJobs\n\nSELECT j.job_id, IIF(ss.freq_type = 64,1,0) StartAgentStart -- 64 - Starting at agent start\n INTO DisabledJobs\n FROM msdb.dbo.sysjobs j\n JOIN msdb.dbo.sysjobschedules js ON js.job_id = j.job_id\n JOIN msdb.dbo.sysschedules ss ON ss.schedule_id = js.schedule_id\n WHERE j.enabled = 1\n\n\nDECLARE @job_id UNIQUEIDENTIFIER\n\nDECLARE job_cursor CURSOR READ_ONLY FOR \nSELECT job_id\n FROM DisabledJobs\n\nOPEN job_cursor \nFETCH NEXT FROM job_cursor INTO @job_id \n\nWHILE @@FETCH_STATUS = 0\nBEGIN\n EXEC msdb.dbo.sp_update_job @job_id = @job_id, @enabled = 0\n FETCH NEXT FROM job_cursor INTO @job_id \nEND\n\nCLOSE job_cursor \nDEALLOCATE job_cursor\n\nDECLARE @RunningJobs INT = 1\n\nWHILE @RunningJobs > 0\nBEGIN\n\n ;WITH last_activity AS (\n SELECT dj.job_id, MAX(session_id) session_id\n FROM DisabledJobs dj\n JOIN msdb.dbo.sysjobactivity sja ON sja.job_id = dj.job_id\n WHERE StartAgentStart = 0\n GROUP by dj.job_id\n )\n SELECT @RunningJobs = COUNT(*)\n FROM last_activity la\n JOIN msdb.dbo.sysjobactivity sja ON sja.job_id = la.job_id AND sja.session_id = la.session_id\n JOIN msdb.dbo.sysjobs j ON j.job_id = la.job_id\n WHERE sja.stop_execution_date IS NULL\n AND sja.start_execution_date > CAST(GETDATE() AS DATE) -- skal v\u00e6re startet i dag\n \n IF @RunningJobs > 0\n WAITFOR DELAY '00:00:05';\nEND\n<\/code><\/pre>\n\n\n\nThe script also mark the SQL jobs that starts at SQL Agent startup. This is to make sure that SQL jobs are started again when I enable alle disabled jobs. Especially CDC jobs could be a problem if that is ignored.<\/p>\n\n\n\n
So when maintenance is finished I run my script to eneable SQL jobs and start the SQL jobs that needs to be started.<\/p>\n\n\n\n
USE master\nGO\n\nDECLARE @job_id UNIQUEIDENTIFIER\n ,@StartUp SMALLINT\n\nDECLARE job_cursor CURSOR READ_ONLY FOR \nSELECT j.job_id, dj.StartAgentStart\n FROM DisabledJobs dj\n JOIN msdb.dbo.sysjobs j ON j.job_id = dj.job_id\n WHERE j.enabled = 0\n\nOPEN job_cursor \nFETCH NEXT FROM job_cursor INTO @job_id, @StartUp\n\nWHILE @@FETCH_STATUS = 0\nBEGIN\n EXEC msdb.dbo.sp_update_job @job_id = @job_id, @enabled = 1\n\n IF @StartUp = 1\n EXEC msdb.dbo.sp_start_job @job_id = @job_id\n\n FETCH NEXT FROM job_cursor INTO @job_id, @StartUp\nEND\n\nCLOSE job_cursor \nDEALLOCATE job_cursor\n\nDECLARE @DisabledJobs INT\n\nSELECT @DisabledJobs = COUNT(*)\n FROM DisabledJobs dj\n JOIN msdb.dbo.sysjobs j ON j.job_id = dj.job_id\n WHERE j.enabled = 0\n\nIF @DisabledJobs = 0\n DROP TABLE DisabledJobs\nELSE\n SELECT @DisabledJobs\n<\/code><\/pre>\n","protected":false},"excerpt":{"rendered":"Sometimes I need to stop alle jobs on a SQL server for some reason, but SQL jobs need to be stoppend and I also want to make sure thas they not automatic start again, until I want them to start. Therefore I wrote a scipt to disable SQL jobs, log which jobs was disables and… L\u00e6s mere Stop\/start SQL jobs due to maintenance<\/span><\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[5],"tags":[],"class_list":["post-68","post","type-post","status-publish","format-standard","hentry","category-scripts-using-for-maintenance","entry"],"_links":{"self":[{"href":"https:\/\/oraclas.dk\/index.php\/wp-json\/wp\/v2\/posts\/68","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/oraclas.dk\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/oraclas.dk\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/oraclas.dk\/index.php\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/oraclas.dk\/index.php\/wp-json\/wp\/v2\/comments?post=68"}],"version-history":[{"count":1,"href":"https:\/\/oraclas.dk\/index.php\/wp-json\/wp\/v2\/posts\/68\/revisions"}],"predecessor-version":[{"id":69,"href":"https:\/\/oraclas.dk\/index.php\/wp-json\/wp\/v2\/posts\/68\/revisions\/69"}],"wp:attachment":[{"href":"https:\/\/oraclas.dk\/index.php\/wp-json\/wp\/v2\/media?parent=68"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/oraclas.dk\/index.php\/wp-json\/wp\/v2\/categories?post=68"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/oraclas.dk\/index.php\/wp-json\/wp\/v2\/tags?post=68"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}