Skip to content

Database Health Monitor Version 2.4 Released

If you haven’t tried Database Health Monitor Version 2.4 might be a good time to try it out. Five and a half years of my development time has gone into Database Health monitor, and hopefully the features will show it.

Star_of_life

Here is what people have to say about Database Health Monitor.

Excellent – It’s the first tool I open every morning to see what is going on in my production environment! Thanks for the great work!

I personally used Database Health Monitor to manage over 100 instances of SQL Server, and it speeds up many of the daily maintenance checks.

Version 2.4 Release Notes

Over the last several months I have added several new features, and many bug fixes. Here are some of the features and fixes included in this update.

Read More »Database Health Monitor Version 2.4 Released

How Many VLFs is Too Many?

Here is an error that popped up in the SQL Server error log today:

5/6/2016 10:10:10 AM spid22s Database [DatabaseName] has more than 10000 virtual log files which is excessive. Too many virtual log files can cause long startup and backup times. Consider shrinking the log and using a different growth increment to reduce the number of virtual log files.

Virtual Log Files (VLFs) are part of the SQL Server log file. When space is allocated in the log due to growth, that new chunk of log is broken up into Virtual Log Files

After looking further into this server, the VLF count turned out to be around 163,000. That is certainly the highest VLF that I have ever seen.

My personal threshold for too many VLFs is usually around 200 with my preference to be less than 100 VLF files. Others may have their preferences, they will all be far less than 10,000, and certainly less than the 163,000 that I had the opportunity to see today.

Read More »How Many VLFs is Too Many?

Updating SQL Server Statistics

Updating SQL Server statistics may not be as obvious as it may sound.

IUpdating SQL Server Statisticsmagine this scenario. I invite you over for dinner and a game of Scrabble. After dinner, I start to do the dishes, while you set up the game. Rather than just doing the dishes that are dirty from our dinner, I decide to take all the dishes from the shelves, and drawers in my kitchen. I create a gigantic pile of all of the dishes, both clean and dirty on the kitchen counter. You get the game setup, and we start playing the game. Between turns, I jump back into the kitchen, and wash a few dishes, then on my turn, I jump back into the game and play my turn, then back to the kitchen again. During the game you are constantly waiting for me to jump back into the game to make my move. You could see how long this would draw out the game with me washing all the dishes, even the ones that were already clean. 4 hours later, I finally finish the dishes, and shortly thereafter we finish the game. It is unlikely, even if you love to play Scrabble that you would ever return to my house for dinner and a game again. Tomorrow night, when I have dinner with my family, I do the same thing, and every night after dinner I wash all the dishes in the kitchen, even the clean ones. You can see how wasteful this sounds, and perhaps some people would even label me as OCD or something worse in this scenario.

Although this is obviously the wrong way to do things, often times we end up doing the equivalent of this in our SQL Server maintenance tasks.

Read More »Updating SQL Server Statistics

Is enabling xp_cmdshell a security risk?

After listening to an interview of Sean McCown (twitter) and some lively discussion on xp_cmdshell, I decided it would be a worthy enough discussion to create a blog post. At a minimum it might stir up some opinions.

There are many varying opinions out there, including some really good opinions that applied to SQL Server 2000, but don’t really apply any more.

Read More »Is enabling xp_cmdshell a security risk?

Database Corruption Webcast – May 3rd

Please join Carlos and I for our first Database Corruption webcast on May 3rd. I have teamed up with Carlos and we will be presenting some thoughts on database corruption with an extended Q&A session.

One important detail is we will be taking questions and answering them live on the webcast so this will be a great way to engage and ask a follow up if needed. I think you will love this format–way better than soaking up 50 minutes of boring and then calling it quits.

The webcast will cover these major topics.
1) Preparing for database corruption and taking the right steps to ensure you can recover
2) A few lessons learned about our experience with database corruption

This weeks session will cover:

corruptionpodcast1

Corruption Prevention is Different Than Disaster Recovery

The best DR plan still needs to account for corruption.

Read More »Database Corruption Webcast – May 3rd

DBCC ShrinkDatabase – I want to shrink my database.

TL;DR summary: Don’t do it. Stop reading here if you want, but just don’t do it.

This post refers to shrinking your database files (mdf, or ndf files), not shrinking the log file. The log file is a completely different conversation, however shrink database does shrink the log file.

Not shrinking your database is one of the more counter intuitive things out there. You might think that a smaller database is a good thing, however there are some negative side effect if you shrink your database regularly, or have the autoshrink option enabled. Side effects of shrinking your database include:

  • Excessive I/O due to the shrink.
  • Index fragmentation (most likely all of your indexes).
  • Excessive I/O to defragment your indexes.
  • After the shrink is complete, inserting or updating rows that require more space in your database will be slowed due to the time involved with growing your data file.

Read More »DBCC ShrinkDatabase – I want to shrink my database.