Get last time Integrity Checks ran successfully on all databases

One of our responsibilities as DBAs is to perform all kind of maintenance tasks in our databases. Sometimes we create jobs and once these jobs work fine for some weeks we just forget about them and assume they’re all running fine all the time. Of course you set alerts in case one of those jobs failed but in environments where there are hundreds of production instances and thousands of databases you or your team may simply lost some of them.

So, you’re troubleshooting a database one day and you suspect you have an issue related to corruption, you need to know when was the last time Integrity Checks ran successfully in this database, if any.

No rocket science here, we all know you just execute:

DBCC DBINFO('TestDB') WITH TABLERESULTS
GO

Once you executed that query you received a big result set and start looking for “dbi_dbccLastKnownGood” field. Once you find it you just take a look to value field and well that’s the last date when Integrity Checks ran fine.

Now you want to do this for all the databases in the instance, if the instance has 3 or 4 user databases it won’t be difficult but what about an instance with 20, 50 or +100 databases? Not so easy now, right?

I had this necessity once while working with one client; therefore, I wrote a query that will gather this info from all databases in the instance and display the results easily and clean.

/******************************************************************************
Author: Victor Sosa
Blog: www.wisesql.com

Description: Get last known good DBCC CHECKDB() for all databases.
******************************************************************************/

CREATE TABLE #LastKnownGoodDBCC
(
[ParentObject] nvarchar(150),
[Object] nvarchar(150),
[Field] nvarchar(100),
[VALUE] nvarchar(150)
)
GO

CREATE TABLE #Results
(
[DBName] nvarchar(128),
[Field] nvarchar(100),
[VALUE] nvarchar(150)
)
GO

sp_MSforeachdb '
INSERT INTO #LastKnownGoodDBCC
EXECUTE (''DBCC DBINFO(''''?'''') WITH TABLERESULTS'')

INSERT INTO #Results
SELECT ''?'', [Field], [VALUE] FROM #LastKnownGoodDBCC
WHERE [Field] = ''dbi_dbccLastKnownGood''

DELETE FROM #LastKnownGoodDBCC
'

SELECT
DBName,
Field,
Value
FROM
#Results
WHERE
Field = 'dbi_dbccLastKnownGood'
ORDER BY
DBName
GO

DROP TABLE #LastKnownGoodDBCC
GO
DROP TABLE #Results
GO

I used this query often now and you can even include it to make some automation on your environment, hope it works for you as it has worked for me.

This is an image of the results on my Instance:

Last_Known_Good_CheckDB

Tip: If you see a date value = 1900-01-01 it means the database has never gone through Integrity Checks maintenance before.

See you next time.

Find Databases not backed up since specific date

One of our daily (and very important) tasks as Database Administrators is to ensure backups are being performed correctly. We also need to ensure that backups are functional but we talk about that in later posts.

You arrive to the office in the morning, tasting your first sip of coffee while your mailbox is loading all weekend mails just to find a bunch of alerts, in any other week you could safely ignore them, but not today. Today, your on-call week starts.

You start reading through the alerts and you have a bunch of servers where the backups failed. You have hundreds of databases in some instances and you don’t want your third party backup tool to just start backing up all databases all over again. Chances are that just one or two databases are missing so you need a quick way to find those out, with this information you could re-run the backup only for those databases and not for the entire instance, all of this in business hours impacting the performance doing backups out of the maintenance window.

You can use the query below to quickly find those databases that were not backed up, you just need to specify the date and time when the backup job/routine started. Let’s say your backups on that server run every night starting at 11 pm, well, the query below will return all DBs not backed up since 11 pm. With this information you can focus your attention only on those returned instead of restarting all databases. Also you can filter by backup type, the script focus by default on Full backups but maybe the alert you received was about Log backups, just change the filter on column “type”, replace letter ‘D’ for an ‘L’, or just comment that line to get the results for both.


/******************************************************************************
Author: Victor Sosa
Blog: www.wisesql.com

Description: Find DBs not backed up since specific date.
******************************************************************************/

USE msdb
GO

SELECT
dbs.name DBName, dbs.recovery_model_desc, dbs.state_desc
FROM
sys.databases dbs
LEFT JOIN (
SELECT
backup_set_id,
database_name,
backup_start_date,
backup_finish_date,
type,
backup_size / 1048576 backup_size_MB,
backup_size / 1073741824 backup_size_GB,
compressed_backup_size / 1048576 compressed_backup_size_MB --SQL 2008 and higher
FROM
dbo.backupset
WHERE
backup_start_date >= '2016-08-01 23:00:00.000' --Change the datetime
AND type = 'D' --Full backup (D) or Log backup(L)
--ORDER BY
--backup_finish_date DESC;
) AS bs ON dbs.name = bs.database_name
WHERE
bs.backup_set_id IS NULL
AND dbs.name <> 'tempdb'
ORDER BY
dbs.name
GO

In the example below I took a full backup of all databases except for “TestDB”, my backup job starts at 11 pm but assume it failed in the middle, I’ll set the datetime filter at 11 pm and run the query, as you can see “TestDB” database is the only one returned.

DBs_Not_BackedUp_Since_Specific_Date

Now that you know “TestDB” is the only one that was not backed up you can only focus your efforts on that one.

Tip: On the LEFT JOIN clause you will find a subquery, you can just select and run this subquery to obtain the opposite result, meaning all databases that were actually backed up since the specified date and time and a bunch of important information about those backups, like backup size, and what time the backup started and what time the backup finished.

See you next time.