Brent Ozar Blitz at SQL Server Connections

The first session of the day for me was Brent Ozars Blitz talk on how to quickly get information on a new server. I have seen Brent’s video on this and read some of his blog posts, but i wanted to see him go over the script in a session.

Brent is funny and has a way of interacting with the audience that makes his talks enjoyable. In this one, he walks you through the script, taking you through the list of items that he finds important to check when he first takes over a server. You can get the script from, or search Brent and Blitz in your favorite engine.

It’s important to go over certain things in a new server, and i would agree with what Brent checks. He looks for backups, DBCC execution, jobs, privileged accounts and more. He has scripts that query the system databases to find out this information. It’s a good practice to use these types of scripts to ensure that you have self-documenting information from your instance. It prevents your information from being out of date.

A few things I learned in here. One was to check for backup history in msdb since that can slow down your backup process over time. The msdb table for backup tracking isn’t well indexed and can fill over time 30-60 days should be enough history. Another is to check for encryption of your databases. I typically don’t work with Enterprise Edition so I don’t run into TDE. However its good to know this and prep your keys for a DR situation. Nothing worse than trying to restore and not having those keys. Also, once you encrypt a database, tempdb is always encrypted. Even if you remove the individual database encryption. That’s good to know. It might not e a big deal, but it is extra overhead.

I also found a nice check for objects in master or model, which isn’t a recommended practice, and you might not think to look and be aware of anything that is stored here. One thing i was not aware of is a query to check for Enterprise Edition features being used in a database since you cannot restore these databases in other editions. actually you can, but at the end the restore process will throw an error and then fail.

I would definitely recommend this session if you find Brent speaking at an event near you.

About way0utwest

Editor, SQLServerCentral
This entry was posted in Uncategorized and tagged , , . Bookmark the permalink.