Database information (online, readonly, backed up, etc.)

Have you ever noticed any inconsistencies in the various states of your databases?  Online? Read_only? Mirrored?  Not backed up?!?

I wrote a brief script that I could run on my central management server.  This script provides database information relating to the status of:  online, read only, mirrored, backed up locally.

/*

Gary Gwyn
1/27/14

This query provides information for all databases on a server:
Name: database name
Status: online or offline
ReadOnly: yes or no
Mirrored: yes or no
BackedUpLocally: yes or no (1 Full backup in past 24 hours)

*******************************/
SELECT db.NAME AS 'Name'
 ,db.state_desc AS 'Status'
 ,CASE
 WHEN db.is_read_only = 0
 THEN ''
 ELSE 'YES'
 END AS 'ReadOnly'
 ,CASE
 WHEN m.mirroring_state IS NOT NULL
 THEN 'YES'
 ELSE ''
 END AS 'Mirrored'
 ,CASE
 WHEN db.NAME IN (
 SELECT DISTINCT database_name
 FROM msdb..backupset
 WHERE backup_finish_date > GETDATE() - 1
 AND type = 'D'
 )
 THEN 'YES'
 ELSE ''
 END AS 'BackedUpLocally'
FROM sys.databases db
INNER JOIN sys.database_mirroring m ON db.database_id = m.database_id

Delete SQL Agent Jobs

I’ve recently been tasked with standardizing SQL maintenance jobs for a farm of several dozen SQL servers.  The previous DBA had a mixture of maintenance plans and custom scripts in place for scheduled maintenance.  I knew ahead of time that I wanted to use a Central Management Server and then mass deploy my maintenance job scripts (I use Ola Hallengren’s scripts).

I ran into a problem where I prematurely published maintenance scripts to all servers.  I also had some jobs enabled by default, while others were disabled by default.  The disabled jobs were scripts that were not quite ready for production.  In hindsight, I wished that I had never deployed those scripts.  So, I had the idea “Let’s delete every disabled job from every server”.  Doing so would allow me to start with a clean slate.

There is a system stored procedure for deleting jobs:   sp_delete_job

One caveat with deleting jobs is the @delete_unused_schedule parameter.  If unspecified, the stored procedure will delete the schedule that is associated with the deleted job (given that no other jobs are associated to the schedule).  I found out the hard way.  See the above link for writing your own job deletion code, or read further for a script to delete all disabled jobs from a server.  You can also run this on your Central Management Server to delete disabled jobs from all registered servers.

/***

Delete all disabled jobs.

Use a cursor to delete each job that is currently disabled.

Gary Gwyn
https://sqlexchange.wordpress.com
1/21/14
*************************/
DECLARE @j UNIQUEIDENTIFIER;
DECLARE @name VARCHAR(100);

DECLARE j_cursor CURSOR
FOR
SELECT job_id, NAME
FROM msdb..sysjobs
WHERE enabled = 0

OPEN j_cursor

FETCH NEXT
FROM j_cursor
INTO @j, @name

WHILE @@fetch_status = 0
BEGIN
 EXEC msdb..sp_delete_job @job_id = @j, @delete_unused_schedule = 0

PRINT 'Deleted job: ' + @name

FETCH NEXT
 FROM j_cursor
 INTO @j, @name
END

CLOSE j_cursor

DEALLOCATE j_cursor