When was my last backup / CheckDB / index maintenance?

Articles, ME Trial, Minion Enterprise, Tech / By

Keep a good eye on your backup and maintenance; they’re kind of important to the health and well-being of your job wait I mean SQL Server instances.

So how do we go about this? Well, there’s the easiest way – you knew I’d be mentioning Minion Enterprise, right? – and then there’s the hard way.

The easiest way

If you have Minion Enterprise, it will tell you if any database is behind on its backups, integrity checks, or index maintenance.

You get an alert. The end.

Bonus: The alert emails contain code to defer alerts or make exceptions for particular databases or instances!

If you want to spot check any of these, you can query all of your databases at once for backup and CheckDB, on all instances:

SELECT ServerName
 , DBName
 , LastBackup
 , LastLogBackup
 , LastDiffBackup
 , LastCheckDB
FROM Collector.DBPropertiesCurrent
ORDER BY ServerName, DBName;

What’s more, if you’re using Minion Reindex integrated with ME, you can query the log tables Collector.IndexMaintLog and Collector.IndexMaintLogDetails centrally, too!

The hard way

For those of you who have not yet acquired what you need for the easy way, I’m afraid you’ll need to check your instances server-by-agonizing-server.  Which makes this not so much the hard way, as it does the time consuming way…

Query: latest backup

To get the latest backup date for all databases, consult MSDB.

USE msdb;
GO
SELECT d.name as database_name, 
	MAX(s.backup_start_date) as last_backup_start_date, 
	s.[type]
FROM sys.databases d
LEFT OUTER JOIN dbo.backupset s ON s.database_name = d.name
INNER JOIN dbo.backupmediafamily as m ON m.media_set_id = s.media_set_id 
	AND s.[type] ='D' -- Full backups! Log, check type='L'
GROUP BY d.name, s.[type]
ORDER BY last_backup_start_date;

We’re joining sys.databases to make sure we don’t miss any DB that doesn’t have an entry in dbo.backupmediafamily at all…that is, any database that’s never had a backup (or hasn’t had one in living memory).

Query: latest CheckDB

If you’re running a CheckDB routine, like Minion CheckDB, you can certainly look at the log for the routine and find the last run.

But if you’re not using a routine that has logging, or you haven’t run any integrity checks in a while, well…this one is even more fun.

Instead of ripping someone’s script off of their site, I’ll point you straight toward Jason Brimhall’s blog Last Time CheckDB Was Run. His script uses the undocumented DBCC PAGE to pull the last CheckDB run out of SQL Server innards.

Short of operation logs, I don’t know of a better way to get this information from SQL.

Query: latest index maintenance

Again, if you’re running a good routine (coff coff MR coff), you’ve got logs! If not, ugh.

You can make a kind-of-guess by looking at statistics update dates, but since the point of index maint is, well, maintaining indexes, we’re better off finding out how fragmented indexes are.

This is where “the hard way” becomes really time consuming, because this query is no sprinter.

USE [My_Database];
DECLARE @dbid TINYINT

SELECT @dbid = DB_ID('My_Database')

SELECT OBJECT_NAME(object_id) [IndexName],* 
FROM sys.dm_db_index_physical_stats (@dbid, NULL, NULL,NULL, NULL) 
ORDER BY page_count desc, avg_fragmentation_in_percent DESC;

This query will pull back all the indexes for one single database  – My_Database – ordered by the average fragmentation in percent. (If you’re not familiar with sys.dm_db_index_physical_stats,  take a look at the documentation.)

With some fiddling, we could make this query return the fragmentation info for every database on an instance…but it would take FOR FREAKING EVER.

Conclusion

Minion Enterprise is definitely the easiest way to manage your SQL Server instances, but use these methods while you’re getting ready to install!