SQL PASS GDL Conference: “SQL Server: From Darkness To Light”

It’s been some time since my last blog post, I had an emergency surgery starting September and guess my mind was not thinking in the blog. Fortunately everything went out Ok and I’m back to office and blogging. I’m a new blogger so I don’t want to get cold on this project and forget about it.

Yesterday, I had the chance, for the first time, to be a SQL PASS speaker, I live in Guadalajara, Mexico and some SQL DBAs started SQL PASS GDL local chapter. When I found out, I joined as fast as I could. This is a great opportunity to begin a local SQL Server Community just like the other hundreds around the world and hopefully in some time, months, maybe years, we may be able to have our first SQL Saturday. I’ll help this community in everything I can.

As I was saying, after attending several past conferences I was invited to speak and I accepted. The most difficult thing was to choose a topic, I wanted my first conference to be something that the audience could start using the next day in their companies, and in order to accomplish that I just remember the “old me”, that junior employee that was part of an on-call rotation getting calls almost every night until some kind of miracle happen and block/slow queries disappear. Long nights barely sleeping trying to fix the world with my lack of knowledge and sp_who.

Years gone by and now I don’t want other DBAs to learn the way I did, under a lot of pressure and stress. After I decided to get serious about SQL Server I bought books and online training, started reading a lot of articles and follow the authors and theirs blogs, studied for certification exams, I even failed my first attempt. So, I thought on all this and I made my first conference based on that, put together some tips and demos that people could start using immediately and that will have a big impact on their jobs and therefore their life. Less stress, more room for happiness, right?

So, if you attended my session yesterday, thank you! Hope these tips work for you as they have worked for me. As I told you during the presentation here’s the link to get PPT files and the queries I used on the Demos.

Warning: It’s a dropbox link, so just be careful if your company does not allow to download files from a dropbox link.

And remember if you have any questions about the presentation or the demos, let me know.

PS. Don’t forget to subscribe on “Follow this blog” section to receive all my new blog posts.

Happy SQL!

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.