Back to Insights

Create developer SQL maintenance plans using SQL Agent Jobs and PowerShell

Habanero has a lot of SQL servers in our virtualized development environments. Since these databases aren't in a critical production environment, they aren't a priority for backup. As a result, the log files don't get properly truncated and can fill up dynamically expanding virtual hard disks quickly. There are many solutions to this problem, but I've found the easiest is to set the databases to simple recovery mode so all data is written directly to the mdf (Database) file, bypassing the ldf (Log File).

To speed up the creation process, I wrote a simple PowerShell script which will create a SQL Agent Job that runs on a schedule and sets all databases within an instance to simple recovery mode, then compacts the databases. The Job will be scheduled to run once a week, recurring on the day of the week the script is initially run, between 12:00 am and 8:00 am. The reason this task is scheduled is that many of the developer databases are created, then dropped, then recreated many times throughout the life of a Virtual Machine, and the easiest way to ensure that all the settings are consistent is to automate setting them on a set schedule.

This script should be compatible with all versions of SQL Server since Version 2005 (though I haven't tested it on anything other than 2008 R2 and 2012.) The script must also be run with elevated privileges as a user that has sysadmin access to the SQL Instance.

NEVER use this in a production environment!

			Create a default maintenance plan for development servers that will run on a weekly schedule
			This will create a Default Maintenance Plan that will recur sometime between 12 AM and 8AM (Randomly) on the day that this script is run.
			Steps that are run on each database are
			-Check Database for Errors
			-Set Recover Mode to Simple
			-Shrink the database
			Filename	: New-SQLMaintenanceJob.ps1
			Author		: Jon Wright
			Company		: Habanero Consulting Group
			Email		:
			Define the name of the Job as it will appear in the SQL Agent Configuration
		.PARAMETER DropExisting
			If a Maintenance Job with the same name already exists, it will be removed before being recreated. If not
			specified, the script will exit if it finds an existing Maintenance Job with the same name.
		.PARAMETER InstanceName
			Only to be specified if a non-default instance is being used (Default instance is: MSSQLSERVER).
			.\New-SQLMaintenanceJob.ps1 -DropExisting
	param (
		[string]$JobName = ("{0}_SQL_Maintenance" -f $env:COMPUTERNAME),
		[switch]$DropExisting = $false,
	[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") | Out-Null

	#If an instance name is supplied
		$srv = New-Object -TypeName Microsoft.SqlServer.Management.SMO.Server($InstanceName)
			$InstanceName = ($InstanceName.Split("\")[1])
	} else {
		#Get the Default Instance
		$srv = New-Object -TypeName Microsoft.SqlServer.Management.SMO.Server
		#An instance name must be supplied for the rest of the script.
		$InstanceName = "MSSQLSERVER"
	#Get the SQL Agent Service for the Instance
	$Service = Get-Service -DisplayName ("SQL Server Agent ({0})" -f $InstanceName)
	#Set the SQL Agent Service startup type to Automatic and attempt to start it
	if($Service -eq $null){
		Throw ("Sql Sserver Agent ({0}) Not Found" -f $InstanceName)
	} else {
	Write-Host (Get-Date) ": Setting SQL Agent Startup type to Automatic"
		$Service | Set-Service -StartupType Automatic -ErrorAction Stop
		if($Service.Status -ieq "Stopped"){
			Write-Host (Get-Date) ": Agent Not Running"
			Write-Host (Get-Date) ": Waiting for Service to Start." -NoNewline
			While($Service.Status -ieq "StartPending"){
				Write-Host "." -NoNewline
				Start-Sleep 2
			if($Service.Status -ine "Running"){
				Write-Host ""
				Throw "An error occurred while starting `"SQL SERVER AGENT ($InstanceName)`". Try starting the service Manually. Then re-run this command."
			Write-Host ".Started"
			Write-Host (get-Date) ": Sleep for 10 Seconds While Agent wakes up..."
			Start-Sleep 10 
			Depending on the speed of your server, it may take longer than 
			10 seconds for the agent to wake up. You will know if the rest of the script fails.
	#Look for Existing Jobs with the same name.
		$Jobs = $srv.JobServer.Jobs
		foreach($Job in $Jobs){
			if($Job.Name -ieq $JobName){
				Write-Host (Get-Date) ("This Server already has a Job Called `'{0}`'." -f $JobName)
					Write-Host (Get-Date) ("Dropping Job: `'{0}`'." -f $JobName)
				} else {
	} Catch {
		Throw $_

	#Define a Job object variable by supplying the Agent and the name arguments in the constructor and setting properties. 
	$job = New-Object -TypeName Microsoft.SqlServer.Management.SMO.Agent.Job -argumentlist $srv.JobServer, $JobName

	#Create the job on the instance of SQL Server Agent.
	Write-Host (Get-Date) ("Creating Job: `'{0}`'." -f $JobName)
	$job.ApplyToTargetServer("(local)") #Job will run against the Agent's Local Server

	#Define a JobStep object variable by supplying the parent job and name arguments in the constructor. 
	$jobstep = New-Object -TypeName Microsoft.SqlServer.Management.SMO.Agent.JobStep -argumentlist $job, "Maintenance_Steps" 
	#JobStep Command, this will check and set each database except for tempdb, master, model, msdb.
	$jobstep.Command = "DECLARE @s nvarchar(4000);
	SELECT @s = '
	            IF ''?'' NOT IN (''tempdb'',''master'',''model'',''msdb'')
	                  USE [?]
	            END '
	EXEC sp_MSforeachdb @s ;"

	$jobstep.OnSuccessAction = [Microsoft.SqlServer.Management.SMO.Agent.StepCompletionAction]::QuitWithSuccess; 
	$jobstep.OnFailAction = [Microsoft.SqlServer.Management.SMO.Agent.StepCompletionAction]::QuitWithFailure; 

	#Create the job step on the instance of SQL Agent. 

	#Define a JobSchedule object variable by supplying the parent job and name arguments in the constructor. 
	$jobsch =  New-Object -TypeName Microsoft.SqlServer.Management.SMO.Agent.JobSchedule -argumentlist $job, "Weekly_Job_Schedule" 

	#Set properties to define the schedule frequency, and duration. 
	$jobsch.FrequencyTypes = [Microsoft.SqlServer.Management.SMO.Agent.FrequencyTypes]::Weekly
	$jobsch.FrequencyInterval = [Microsoft.SqlServer.Management.SMO.Agent.WeekDays]::(Get-Date -Format "dddd")
	$jobsch.FrequencyRecurrenceFactor = 1
	$timespan =  New-Object -TypeName TimeSpan -argumentlist (get-random -Minimum 0 -Maximum 8), (get-random -Minimum 0 -Maximum 59), 00 #Randomly Between 12 AM and 8 AM
	$jobsch.ActiveStartTimeOfDay = $timespan
	$jobsch.ActiveStartDate = (Get-Date -format "MM/dd/yyyy")

	#Create the job schedule on the instance of SQL Agent. 
	Write-Host (Get-Date) ("Job: `'{0}`' Successfully Created..." -f $JobName)


EDIT (March 4, 2013): Removed DBCC CheckDB due to performance impact on shared environment.