System Center Advisor: What is the email all about?…

The last time I left you with my adventures with System Center Advisor I was at the SeaTac airport checking out my alert dashboard before I jumped on to my plane back to Texas. And now that I’m back in the sunny skies of the Lone Star State (sorry Seattle I love travelling there but for a Texas boy there is nothing like the clear blue sunny skies in the spring), I decided to follow up on an email I had received for my live ID from System Center Advisor. The email looks like this:

I decided to focus on the fact that I have New Alerts so clicked on the “View all alerts” link at the right of the page. It prompted me to login to System Center Advisor. My alert dashboard showed me new alerts found on March 29th that perhaps I should look at:

I remember creating a new database called mavstothefinals (OK I can only dream as a Dallas Mavs fan this will happen). last week while in Seattle and I immediately created a backup for it (BTW. We have a rule that checks to see if you have ever backed up your database at least once so I didn’t hit that one).  But I get these warnings that seem to indicate I need to pay attention to a few things about this database. Let’s look at each of these in more detail:

The first rule indicates I have not run a consistency check on this database. You will notice that in the right corner it shows the date and time I created the database so tells me how many days have occurred since I’ve run DBCC CHECKDB with no errors (this is what we mean by “clean consistency check”). I know there is debate in the community on how often you should run CHECKDB, but we took the conservative route with System Center Advisor. Our rule simply checks that you have run DBCC CHECKDB with no errors at least one time since you created the database. Once you run a CHECKB with no errors this alert should be closed (We will prove this tomorrow).

The next rule is pointing out that my backup I created is on the same volume as my database files:

This might seem like a very simple rule but you would be surprised how often I’ve seen this from customers contacting CSS. If you are not sure why this is important think about what this rule is saying. You have put your backup of your production database on the same disk drive as where the database files are stored. Should this drive fail, you will have nothing. No data or a backup. Or consider a scenario where the drive is accessible but has problems and both the backup and the database are corrupted because of it.. We implement this rule by running a T-SQL query that joins the backupset and backupmediafamily tables in msdb with sys.databases and sys.master_files. The comparison is to look for your latest full database backup and see whether the target drive letter matches a a drive letter for any files for the database. The Information detected information in the right corner shows you the drives we detected for your latest full backup and the drives we detected for database files should there be a match.

So far these rules make logical sense. It makes sense to run a consistency check when I first put a database in production and it definitely makes sense to put my backups on a different storage location than my production database files. The third alert is all about keeping the size of the transaction log reduced and represents a very common problem we have seen from customers. Have you ever seen the transaction log grow for your database it seems infinitely? There can be several reasons why but a simple explanation could be for this rule:

This rule indicates my new database is using the FULL recovery model (the default for SQL Server) but I have no transaction log backup. This is not a problem if you never create a full database backup first. But once you create a full database backup, the log will never be truncated until you backup the transaction log. This rule is implemented by checking to see what type of backup is your last backup. If the last type is not a transaction log backup and the database is using the full recovery model, we fire the rule. As with our other two rules, this rule is very practical and will only fire if you are not taking transaction log backups when using the FULL recovery model. If you see this rule and think it does not apply to you then it is very likely you should be using the SIMPLE recovery model. If you see your transaction log growing forever, this rule could easily explain why. There is on catch to this rule. If you backup your transaction log once and continue to make changes but never back it up again (and never take any other type of backup), the log will grow but this rule would not fire. We don’t check for this condition (today) because we were not sure what threshold to look for comparing the size of the log vs the age of the log backup. Rather our rule reflects a common issue where customers create a new database, create a full backup, but never backup the transaction log.

I think all of these rules make sense, so I’m going to follow through with:

• Running CHECKDB against this database
• Creating a full backup of this database on a different drive
• Then creating a transaction log backup to avoid infinite log growth

In my next post, we will make sure these issues are resolved and then review the remaining alerts for my fresh installation of SQL Server 2008 R2.

Bob Ward, Microsoft