Category Archives: System

How to disable all enabled user job schedules in SQL Server with T-Sql

I’d really recommend not running the output of this script on a live environment! This is just a little something I whipped up to test on a development environment.

Running the below script will output the T-Sql required to disable all enabled job schedules on the SQL Server instance. Just copy the outputted text and paste into a new SSMS window and execute to disable the jobs.

SET NOCOUNT ON

DECLARE @Schedule TABLE (ScheduleId INT)
DECLARE @ScheduleId AS INT
DECLARE @Sql AS VARCHAR(MAX)

SET @ScheduleId = ''

INSERT INTO @Schedule (ScheduleId)
SELECT s.schedule_id
FROM msdb.dbo.sysschedules AS s
WHERE s.[enabled] = 1
	AND s.owner_sid <> 0x01
ORDER BY s.NAME ASC

WHILE @ScheduleId IS NOT NULL
BEGIN
	SET @ScheduleId = (
			SELECT MIN(ScheduleId)
			FROM @Schedule
			WHERE ScheduleId > @ScheduleId
			)
	SET @Sql = '
EXEC msdb.dbo.sp_update_schedule @schedule_id = ' + '''' + CONVERT(VARCHAR(5), @ScheduleId) + '''' + '
	,@enabled = 0; 
'

	PRINT @Sql
END

How to disable all enabled jobs in SQL Server with T-Sql where the job name contains specific keyword text

Running the below script will output the T-Sql required to disable all enabled jobs on the SQL Server instance where the job name contains a specific keyword. Just copy the outputted text and paste into a new SSMS window and execute to disable the jobs.

For example, if you change the text value ‘PLACEHOLDER’, for the variable @KeyWord, to ‘Backup’ then any job which has ‘Backup’ in its name will have T-Sql generated to disable it. You could change the PRINT to EXEC but I wouldn’t recommend it.

DECLARE @KeyWord AS VARCHAR(256)

SET @KeyWord = 'PLACEHOLDER'
SET NOCOUNT ON

DECLARE @Job TABLE (JobName SYSNAME)
DECLARE @JobName AS SYSNAME
DECLARE @Sql AS VARCHAR(MAX)

SET @JobName = ''

INSERT INTO @Job (JobName)
SELECT j.NAME
FROM msdb.dbo.sysjobs AS j
WHERE j.[enabled] = 1
	AND j.NAME LIKE '%' + @KeyWord + '%' 
ORDER BY j.NAME ASC

WHILE @JobName IS NOT NULL
BEGIN
	SET @JobName = (
			SELECT MIN(JobName)
			FROM @Job
			WHERE JobName > @JobName
			)
	SET @Sql = '
EXEC msdb.dbo.sp_update_job @job_name = ' + '''' + @JobName + '''' + '
	,@enabled = 0; 
'

	PRINT @Sql
END
Space Sniffer image showing how much space AVG Web Tuneup using

AVG Web Tuneup you jerk!

So I finally got bothered enough to find out what the hell was eating up all the space on my C drive. I had just assumed it was something to do with the enforced Windows 10 upgrade but it turns out it was the AVG Web Tuneup application writing to the AppData directory. (Sorry for blaming you Windows 10)

For anyone else experiencing low disk space that is going unnoticed by disk cleanup utilities it might be worth checking this out. I used the fantastic utility Space Sniffer. This utility doesn’t require an install, it’s super light and super quick and will visually display all the files and folders on the drive you choose to scan. (Tip: run as administrator so it doesn’t throw warnings about directories it doesn’t have access to)

Space Sniffer image showing how much space AVG Web Tuneup using

To access the AppData folder on Windows 10 go to start and type %AppData%.

To rectify  this specific problem caused by AVG Web Tuneup utility first uninstall it. If this doesn’t get rid of the folder then access the AppData folder and navigate to AppData\Local\Packages\windows_ie_ac_001\AC\AVG Web TuneUp and delete that damn directory.

So long AVG Web Tuneup you jerk!