How I Check Hundreds of SQL Agent Jobs in 60 Seconds with Powershell

Checking that your Agent Jobs have completed successfully is a vital part of any DBA’s responsibility. It is essential to ensure that all of the hard work you have put into setting up the jobs can be quickly and easily checked. In a large estate this can be very time consuming and if done manually prone to human error. I have repeatedly mentioned John Sansoms Blog Post entitled “The Best DBAs Automate Everything” and I follow that advice. Today I will share with you one fo the first scripts that I wrote.

When I started as a DBA I was told that my first job every morning was to check the Agent Jobs and resolve any errors. This is still something I do first before anything else. (Except coffee, experience has taught me that you get your coffee before you log into your computer otherwise on the bad days you can miss out on coffee for many an hour) I have two scripts to do this. The first sends me an email if the number of failed jobs on a server is greater than zero. This helps me to quickly and simply identify where to start in the case of multiple failures and is also a backup to the second script.

The second script runs on a different server and creates an excel worksheet and colour codes it. This makes it very simple to quickly scroll through the sheet and spot any red cells which designate failed jobs and also provides a nice easy to understand method to show management that on that specific day everything went well (or badly)

As with any Powershell script which manipulates Office applications you first need to create an object and add the workbook and worksheet to it. I also set a filename date variable and a Date variable for the Sheet.

When you use Powershell to manipulate Excel you can access individual cells by identifying them by Row and Column. I use this to create a description for the work book as follows

There are lots of properties that you can play with within Excel. As with any Powershell the best way to find what you need is to use the Get-Member Cmdlet. If you run

($cells.item(1,3)|Get-Member).Count


You will see that there are 185 Methods and Properties available to you (in Office 2013 on Windows 8.1)

The snippet above creates the following

As you can see we are going to colour code the Job Status according to the three available results Successful, Failed and Unknown. We are also going to colour code the date column to see when the job was last run, this will enable you to easily identify if the last time the job ran it was successful but last night it didn’t kick off for some reason.

The next step is a fairly standard loop through available servers by picking them from a SQLServers text file, a list of the server names (ServerName\Instance if required) that you wish to check. You could also just create an array of server names or pick them from a table with Invoke-SQLCmd but which ever way you do it you need to be able to iterate through the array and then the .Jobs Collection in the JobServer Namespace as follows

What the script then does is to use the following properties of the $Job object and write the Excel File according to the logic in the description

$Job.Name
$Job.IsEnabled
$Job.LastRunOutcome
$Job.LastRunDate

To finish up save the workbook to a share available to all of the DBA Team and quit Excel. Notice that I use a double whammy to make sure Excel is really gone. First I quit the .com object and then I stop the process. I do this because I found that on my server quitting the .com object left the Excel process running and I ended up with dozens and dozens of them. If you have Excel open before you run this script either comment out the last line or save your work (You should save your work anyway regulary!)

As always I take no responsibility for your environment, that’s your Job! Don’t run this on Production unless you know what it is doing and are happy that you have first tested it somewhere safely away from any important systems. Make sure that you understand the correct time to run this job and have qualified the impact on the box it is running on.

Here is a screen shot of the finished Excel Sheet

As you can see the Data Transfer Job needs investigation! The reason I add to yellow rows above and below each servers list of jobs is to help me identify any server that is not responding as that will be easily recognised as two lots of yellow with nothing between them

I have considered improving this script by inputting the data into a database and running a report from that database but have not had the need to do so yet.

Here is the script

#############################################################################################
#
# NAME: Agent Job Status to Excel.ps1
# AUTHOR: Rob Sewell http://newsqldbawiththebeard.wordpress.com
# DATE:22/07/2013
#
# COMMENTS: Iterates through the sqlservers.txt file to populate
# Excel File with colour coded status
#
# WARNING - This will stop ALL Excel Processes. Read the Blog Post for more info
#
# ————————————————————————

# Get List of sql servers to check
$sqlservers = Get-Content ''; # from a file or a SQL query or whatever

# Create a .com object for Excel
$xl = new-object -comobject excel.application
$xl.Visible = $true # Set this to False when you run in production

$wb = $xl.Workbooks.Add()
$ws = $wb.Worksheets.Item(1)

$date = Get-Date -format f
$Filename = ( get-date ).ToString('ddMMMyyyHHmm')

$cells = $ws.Cells

# Create a description

$cells.item(1, 3).font.bold = $True
$cells.item(1, 3).font.size = 18
$cells.item(1, 3) = "Back Up Report $date"
$cells.item(5, 9) = "Last Job Run Older than 1 Day"
$cells.item(5, 8).Interior.ColorIndex = 43
$cells.item(4, 9) = "Last Job Run Older than 7 Days"
$cells.item(4, 8).Interior.ColorIndex = 53
$cells.item(7, 9) = "Successful Job"
$cells.item(7, 8).Interior.ColorIndex = 4
$cells.item(8, 9) = "Failed Job"
$cells.item(8, 8).Interior.ColorIndex = 3
$cells.item(9, 9) = "Job Status Unknown"
$cells.item(9, 8).Interior.ColorIndex = 15


#define some variables to control navigation
$row = 3
$col = 2

#insert column headings

$cells.item($row, $col) = "Server"
$cells.item($row, $col).font.size = 16
$Cells.item($row, $col).Columnwidth = 10
$col++
$cells.item($row, $col) = "Job Name"
$cells.item($row, $col).font.size = 16
$Cells.item($row, $col).Columnwidth = 40
$col++
$cells.item($row, $col) = "Enabled?"
$cells.item($row, $col).font.size = 16    
$Cells.item($row, $col).Columnwidth = 15
$col++    
$cells.item($row, $col) = "Outcome"
$cells.item($row, $col).font.size = 16
$Cells.item($row, $col).Columnwidth = 12
$col++
$cells.item($row, $col) = "Last Run Time"
$cells.item($row, $col).font.size = 16    
$Cells.item($row, $col).Columnwidth = 15
$col++

   
# Load SMO extension
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") | Out-Null;


# Loop through each sql server from sqlservers.txt
foreach ($sqlserver in $sqlservers) {
    # Create an SMO Server object
    $srv = New-Object "Microsoft.SqlServer.Management.Smo.Server" $sqlserver;
 
    # For each jobs on the server
    foreach ($job in $srv.JobServer.Jobs) {

        $jobName = $job.Name;
        $jobEnabled = $job.IsEnabled;
        $jobLastRunOutcome = $job.LastRunOutcome;
        $Time = $job.LastRunDate ;

        # Set Fill Colour for Job Enabled
        if ($jobEnabled -eq "FALSE")
        { $colourenabled = "2"}
        else {$colourenabled = "48" }         

        # Set  Fill Colour for Failed jobs
        if ($jobLastRunOutcome -eq "Failed") {
            $colour = "3" # RED
        }
            
        # Set Fill Colour for Uknown jobs
        Elseif ($jobLastRunOutcome -eq "Unknown")
        { $colour = "15"}       #GREY        

        else {$Colour = "4"}   # Success is Green    
        $row++
        $col = 2
        $cells.item($Row, $col) = $sqlserver
        $col++
        $cells.item($Row, $col) = $jobName
        $col++
        $cells.item($Row, $col) = $jobEnabled    
        #Set colour of cells for Disabled Jobs to Grey
    
        $cells.item($Row, $col).Interior.ColorIndex = $colourEnabled
        if ($colourenabled -eq "48") { 
            $cells.item($Row , 1 ).Interior.ColorIndex = 48
            $cells.item($Row , 2 ).Interior.ColorIndex = 48
            $cells.item($Row , 3 ).Interior.ColorIndex = 48
            $cells.item($Row , 4 ).Interior.ColorIndex = 48
            $cells.item($Row , 5 ).Interior.ColorIndex = 48
            $cells.item($Row , 6 ).Interior.ColorIndex = 48
            $cells.item($Row , 7 ).Interior.ColorIndex = 48
        } 
        $col++

        $cells.item($Row, $col) = "$jobLastRunOutcome"
        $cells.item($Row, $col).Interior.ColorIndex = $colour

        #Reset Disabled Jobs Fill Colour
        if ($colourenabled -eq "48") 
        {$cells.item($Row, $col).Interior.ColorIndex = 48}

        $col++

        $cells.item($Row, $col) = $Time 
    
        #Set teh Fill Colour for Time Cells

        If ($Time -lt ($(Get-Date).AddDays(-1)))
        { $cells.item($Row, $col).Interior.ColorIndex = 43}
        If ($Time -lt ($(Get-Date).AddDays(-7)))
        { $cells.item($Row, $col).Interior.ColorIndex = 53} 
              
    }
    $row++
    $row++

    # Add two Yellow Rows
    $ws.rows.item($Row).Interior.ColorIndex = 6
    $row++
    $ws.rows.item($Row).Interior.ColorIndex = 6
    $row++
}


$wb.Saveas("C:\temp\Test$filename.xlsx")
$xl.quit()
Stop-Process -Name EXCEL

 

If you have any questions please use the comment box below

Rationalisation of Database with Powershell and T-SQL part two

In the previous post I showed the script to create an Excel Workbook, colour coded showing the last used date for all of the databases on servers in my sqlservers.txt file. After gathering that information over several months, there is then a requirement for someone to make a decision as to which databases can be removed.

Obviously there will be some databases that are read-only or if not set specifically as read-only may only be used for reference without data being added. You should hopefully have knowledge of these databases and be able to take them off the list quickly.

There are other challenges for a DBA to overcome prior to any action. Many questions need to be answered such as

Who owns the database?
Who is the service owner responsible for the service/application in use by the database?
Even though they may be the service owner who will ultimately sign off permission to remove the database are they aware of how important it is for their people? Or what times of the year it is important to them?
You may find test and development databases that have not been used for months but will they be required next week?
Is it important enough for them to take the time to give the permission?

And plenty more… Add some in the comments below.

Our Primary responsibility is the data. We need to be able to ensure that the data is safe and can be made available quickly and easily. In this situation we need to have a valid backup and a quick and easy method of restoring it. I chose to solve this by creating a T-SQL script which will :-

The reasoning for these steps is best explained by watching this video and yes I always perform the last step too J

I could have used PowerShell to do this by examining The SMO for the Server and the JobServer but this time I decided to challenge myself by writing it in T-SQL as I am weaker in that area. The script below is the result of that work. It works for me. I expect that there are other ways of doing this and please feel free to point out any errors or suggestions. That is how I learn. Hopefully these posts will be of use to other DBAs like myself.

As always with anything you read on the internet. Validate and test. This script works for me on SQL Servers 2005, 2008,2008R2 and 2012 but if you are thinking of running it in your own Production Environment – DON’T.

Well not until you have tested it somewhere safe first J

The first challenge I encountered was that I wanted to only have to change the name of the database to be able to run the script and perform all of these steps. That will also lead onto a stored procedure and then I can automate more of this process and schedule at times to suit the database servers as well. I accomplished this by using a temp table and populating it with the variables I will need as shown below

-- Drop temp table if it exists
IF OBJECT_ID('tempdb..#vars') IS NOT NULL
DROP TABLE #vars 
-- Create table to hold global variable
create table #vars (DBName nvarchar(50), PATH nvarchar(300),DataName nvarchar(50),LogName nvarchar (50),DataLoc nvarchar (256),LogLoc nvarchar (256))
insert into #vars (DBName) values ('DATABASENAME')
-- Declare and set variables
DECLARE @PATH nvarchar(300)
Set @Path = (SELECT 'PATH TO RATIONALISATION FOLDER WITH TRAILING SLASH' + @DBName + '_LastGolden_' + + convert(varchar(50),GetDate(),112) + '.bak' )
DECLARE @DataName nvarchar(50)
Set @DataName = (SELECT f.name
FROM sys.master_files F
join sys.databases D
on d.database_id = f.database_id
WHERE F.type = 0
AND d.Name = @DBNAME)
-- Print @DataName
DECLARE @LogName nvarchar (50)
Set @LogName = (SELECT f.name
FROM sys.master_files F
join sys.databases D
on d.database_id = f.database_id
WHERE F.type = 1
AND d.Name = @DBNAME)
-- PRINT @LogName
Declare @DataLoc nvarchar (256)
Set @DataLoc = (SELECT f.physical_name
FROM sys.master_files F
join sys.databases D
on d.database_id = f.database_id
WHERE F.type = 0
AND d.Name = @DBNAME)
--Print @DataLoc
Declare @LogLoc nvarchar (256)
Set @LogLoc = (SELECT f.physical_name
FROM sys.master_files F
join sys.databases D
on d.database_id = f.database_id
WHERE F.type = 1
AND d.Name = @DBNAME)
--Print @LogLoc
update #vars Set PATH = @PATH
update #vars Set DataName = @DataName
update #vars Set LogName = @LogName
update #vars Set DataLoc = @DataLoc
update #vars Set LogLoc = @LogLoc
-- Select * from #vars

I then use the variables throughout the script by selecting them from the temp table as follows

DECLARE @DBName nvarchar(50)
Set @DBName = (Select DBNAme from #vars)

And using the variables to create and execute the T-SQL for each of the steps above.

It is pointless to move onto the next step of the previous one has failed so I created some error handling as follows

if @@error != 0 raiserror('Rationalisation Script failed at Verify Restore', 20, -1) with log
GO

I created the T-SQL for the agent job by first creating the restore script and adding it to a variable and then right-clicking on a previously created restore database job and using the script to new window command

It was then a case of adding single quotes and reading the code until it would successfully run

/***
Rationalisation Script

Script to Automatically Backup, Drop and create Agent Job to restore from that backup

AUTHOR - Rob Sewell http://sqldbawithabeard.com
DATE - 19/01/2014

USAGE - You need to Change the Database Name after " insert #vars values (' "
		You also need to check that the folder after " Set @Path = (SELECT ' " is correct and exists 
		and Find and replace both entries for THEBEARD\Rob with the account that will be the owner of the job and the database owner
		
Once this has been run AND you have checked that it has successfully backed up the database and created the job and you have checked hte job works
You may delete the backups but keep the backup folder under UserDbs

***/

 --Drop temp table if it exists 
IF OBJECT_ID('tempdb..#vars') IS NOT NULL
DROP TABLE #vars	

--Create table to hold global variable
create table #vars (DBName nvarchar(50), PATH nvarchar(300),DataName nvarchar(50),LogName nvarchar (50),DataLoc nvarchar (256),LogLoc nvarchar (256))
insert into #vars (DBName) values ('SQL2012Ser2012DB'
					)

--Declare and set variables	

DECLARE @DBName nvarchar(50)
Set @DBName = (Select DBNAme from #vars)			

DECLARE @PATH nvarchar(300)
Set @Path = (SELECT 'PATH TO RATIONALISATION FOLDER' + @DBName + '_LastGolden_' + + convert(varchar(50),GetDate(),112) + '.bak' )

DECLARE @DataName nvarchar(50)
Set @DataName = (SELECT f.name
FROM sys.master_files F 
join sys.databases D
on
d.database_id = f.database_id
WHERE F.type = 0
AND d.Name = @DBNAME)

--Print @DataName

DECLARE @LogName nvarchar (50)
Set @LogName = (SELECT f.name
FROM sys.master_files F 
join sys.databases D
on
d.database_id = f.database_id
WHERE F.type = 1
AND d.Name = @DBNAME)

--PRINT @LogName

Declare @DataLoc nvarchar (256)
Set @DataLoc = (SELECT f.physical_name
FROM sys.master_files F 
join sys.databases D
on
d.database_id = f.database_id
WHERE F.type = 0
AND d.Name = @DBNAME)

--Print @DataLoc

Declare @LogLoc nvarchar (256)
Set @LogLoc = (SELECT f.physical_name
FROM sys.master_files F 
join sys.databases D
on
d.database_id = f.database_id
WHERE F.type = 1
AND d.Name = @DBNAME)

--Print @LogLoc

update #vars Set PATH = @PATH 
update #vars Set DataName = @DataName
update #vars Set LogName = @LogName
update #vars Set DataLoc = @DataLoc
update #vars Set LogLoc = @LogLoc

-- Select * from #vars
-- DBCC

DECLARE @DBCCSQL nvarchar (4000)
SET @DBCCSQL = '
USE [' + @DBName + ']
DBCC CHECKDB WITH NO_INFOMSGS, ALL_ERRORMSGS
'
-- Print @DBCCSQL

EXECUTE(@DBCCSQL)

-- Break out if error raised We need to do some work if there are errors here

if @@error != 0 raiserror('Rationalisation Script failed at DBCC', 20, -1) with log
GO

-- Declare and set variables	
			
DECLARE @DBName nvarchar(50)
Set @DBName = (Select DBNAme from #vars)

DECLARE @PATH nvarchar(300)
Set @Path = (SELECT PATH from #vars)

Declare @BKUPName nvarchar(300)
Set @BKUPName = (SELECT 'Last Golden Backup For ' + @DBName + '- Full Database Backup')

DECLARE @BackupSQL nvarchar (4000)
SET @BackupSQL = '
BACKUP DATABASE [' + @DBName + '] TO  DISK = N''' + @PATH + '''
WITH INIT,  NAME = N''' + @BKUPName + ''', 
CHECKSUM, STATS = 10
'

--- PRINT @BackupSQL

-- Backup database to Golden backup location

EXECUTE(@BackupSQL)
GO

-- Break Out if there are errors here - If there is no backup we don't want to continue

if @@error != 0 raiserror('Rationalisation Script failed at Backup', 20, -1) with log
GO

DECLARE @PATH nvarchar(300)
Set @Path = (SELECT PATH from #vars)

RESTORE VERIFYONLY
FROM DISK = @PATH;

if @@error != 0 raiserror('Rationalisation Script failed at Verify Restore', 20, -1) with log
GO
-- Declare variables for dropping database

DECLARE @DBName nvarchar(50)
Set @DBName = (Select DBNAme from #vars)

DECLARE @DROPSQL nvarchar (4000)
SET @DROPSQL = '
USE [master]
ALTER DATABASE [' + @DBName + '] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
DROP DATABASE [' + @DBName + '] 
'
-- PRINT @DROPSQL

--Drop database

EXECUTE(@DROPSQL)
GO
if @@error != 0 raiserror('Rationalisation Script failed at Drop Database', 20, -1) with log
GO

--Declare variables for creating Job

DECLARE @DBName nvarchar(50)
Set @DBName = (Select DBNAme from #vars)

DECLARE @PATH nvarchar(300)
Set @Path = (Select PATH from #vars)

DECLARE @DataName nvarchar(50)
Set @DataName = (Select DataName from #vars)

DECLARE @LogName nvarchar (50)
Set @LogName = (Select LogName from #vars)

Declare @DataLoc nvarchar (256)
Set @DataLoc = (Select DataLoc from #vars)

Declare @LogLoc nvarchar (256)
Set @LogLoc = (Select LogLoc from #vars)

DECLARE @RestoreCommand nvarchar(4000)
Set @RestoreCommand = '''RESTORE DATABASE [' + @DBName + '] 
FROM  DISK = N''''' + @PATH + '''''
WITH  FILE = 1,  
MOVE N''''' + @DataName +  ''''' TO N''''' + @DataLoc + ''''',  
MOVE N''''' + @LogName + ''''' TO N''''' + @LogLoc + ''''',  
NOUNLOAD,  REPLACE,  STATS = 10

'''
--print @RestoreCommand

--Create Job creation tsql

DECLARE @JOBSQL nvarchar (4000)
SET @JOBSQL = 'USE [msdb]

BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
/****** Object:  JobCategory [[Uncategorized (Local)]]]    Script Date: 01/18/2014 14:12:04 ******/
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N''[Uncategorized (Local)]'' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N''JOB'', @type=N''LOCAL'', @name=N''[Uncategorized (Local)]''
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

END

DECLARE @JOBNAME nvarchar(300)
set @JOBNAME = ''Rationlised - - Restore '  + @DBName + ' from Last Golden Backup''

Declare @JobDesc nvarchar(300)
Set @JobDesc = '' Rationalised Database Restore Script for ' + @DBName + '''

DECLARE @jobId BINARY(16)

EXEC @ReturnCode =  msdb.dbo.sp_add_job @job_name= @JOBNAME, 
		@enabled=1, 
		@notify_level_eventlog=0, 
		@notify_level_email=0, 
		@notify_level_netsend=0, 
		@notify_level_page=0, 
		@delete_level=0, 
		@description=@JobDesc, 
		@category_name=N''[Uncategorized (Local)]'', 
		@owner_login_name=N''THEBEARD\Rob'', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object:  Step [Restore Database]    Script Date: 01/18/2014 14:12:04 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N''Restore Database'', 
		@step_id=1, 
		@cmdexec_success_code=0, 
		@on_success_action=3, 
		@on_success_step_id=0, 
		@on_fail_action=2, 
		@on_fail_step_id=0, 
		@retry_attempts=0, 
		@retry_interval=0, 
		@os_run_priority=0, @subsystem=N''TSQL'', 
		@command= ' + @RestoreCommand + ', 
		@database_name=N''master'', 
		@flags=4
/****** Object:  Step [Set Owner]    Script Date: 01/19/2014 10:14:57 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N''Set Owner'', 
		@step_id=2, 
		@cmdexec_success_code=0, 
		@on_success_action=1, 
		@on_success_step_id=0, 
		@on_fail_action=2, 
		@on_fail_step_id=0, 
		@retry_attempts=0, 
		@retry_interval=0, 
		@os_run_priority=0, @subsystem=N''TSQL'', 
		@command=N''USE [' + @DBName + ']

EXEC sp_changedbowner @loginame = N''''THEBEARD\Rob'''', @map = false'', 
		@database_name=N''master'', 
		@flags=0		
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N''(local)''
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
    IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:


'
--PRINT @JOBSQL

--Create Agent Job

EXECUTE(@JOBSql)

if @@error != 0 raiserror('Rationalisation Script failed at Create Job', 20, -1) with log
GO

DROP Table #vars

 

The process I have used is to change the database name in the script and run it and then run the Agent Job and check the database has been created. Then and only then can I drop the database and disable any jobs for the database. Yes that was the last step in the video J as Grant says “a file is just a file, a backup is a restored database”

Using this script you can reduce the footprint and load on your servers by removing unneeded or unused databases whilst still guaranteeing that should there be a requirement for them you KNOW you can easily restore them. You will still need to take some additional steps like adding a stop to the Agent Job to recreate any users and any other jobs that the database needs but that is more specific to your environment and you will be best placed to achieve this