Skip to content

Database Health

Database Health Monitor – Version 2.3 Released

With over 7500 downloads worldwide to date, I have just released the next installment of the FREE Database Health Monitor application.

Here what one person had to say about it “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!”

Download today:

 

Database Health Version 2.3 Release Notes

Released March 20th, 2016.

It has been about 3 months since the last release, and I have added several new features, and many bug fixes.

New Features

  • Reducing the number of different database connections used by Database Health Monitor. Reusing existing connections where applicable.
  • Backup Report
    • Database restore script for the backups report now includes the “RESTORE HEADERONLY” option.
    • Updated the restore script to include a FILELISTONLY option to check on file locations before restoring.
    • Recent backups are now shown in green so it’s easy to see which databases have recent backups and which do not.
    • Added column to show the size of the backup.
  • CheckDB dialog: Added the instance name to the CheckDB dialog so that you can know which instance the CheckDB is being run against.

Read More »Database Health Monitor – Version 2.3 Released

Using SQL Server Compressed Backups

The Quick Scan report in Database Health Monitor detects when you are performing SQL Server backups without compression, when the compression option is available. This applies to Full backups, Transaction log backups and Differential backups.

Not using compressed backups? Why not?

Benefits of compressed backups

  • Faster backup time
  • Faster restore time
  • Less I/O at backup and restore time
  • Since you are using less disk space, you can keep more backups around.

There are a couple minor drawbacks, the compressed backups take more CPU, not much more, but a tiny bit more.  Also the compressed backups don’t compress very much when your database is using Transparent Data Encryption (TDE).

Read More »Using SQL Server Compressed Backups

Database Health Monitor Version 2.1 coming soon

In the next week or two, I will be releasing version 2.1 of the Database Health Monitor application. I am just going through the final testing process now to get it ready for general release. There are a number of new features that have been requested since the release of version 2.0 that will be coming out in version 2.1. The most request feature was the addition of support for the F5 key to refresh reports. There have been several new server or instance level reports added, along with a new section to link to the instance level reports.

Database Health Monitor

Database Health Monitor Version 2.1 New Features

Here is a list of the newly requested features that are complete, and will be available in version 2.1 of the Database Health Monitor.

Read More »Database Health Monitor Version 2.1 coming soon

Silencing Backup Messages with Trace Flag 3226

ErrorLog_flooded

As a DBA you regularly schedule backups you might have regular full backups, incremental backups and hopefully regular backups. Over time you might realize that your error log is not longer filled with errors, but instead a majority of your error log is filled with backup messages. “Backup Database successfully … “,  “Database backed up…”, “Log was backed up”. This makes it a bit difficult to find the actual errors in this flood of backup messages. Your error log is no longer an error log, but it looks more like a backup log.

Trace Flag 3226

Trace flags in SQL Server are switches or parameters that can be used to turn on or off different features. If you use trace flag 3226 this will turn off all successful backup messages being written to the error log. There are a couple of ways to use trace flag 3226, one is to use DBCC TRACEON to turn on trace flag 3226 for the currently running instance. Using this option will turn it on until the next time the database instance is restarted.

Read More »Silencing Backup Messages with Trace Flag 3226

Understanding your Wait Statistics

SQL Server Performance Tuning Tips – Wait Statistics

Wait statistics are commonly overlooked ways to quickly find out what is causing your SQL Server to be slow. One of the reasons is it’s difficult to see how they are trending over time.

>>> It is not difficult with a monitoring tool. <<<

When someone reports a slow database yesterday at 2:00pm, do you know how to determine what’s causing it? You can check the logs, you can look at the history of running jobs, and you might even ask around to see if anyone was doing anything unusual at that time. If you are tracking wait statistics, it is quick and easy to zoom in on a point in time and see exactly what queries were slow and why they were slow.

You might be thinking the following:

  • Did someone run a slow ad-hoc query?
  • Was there something wrong with the network?
  • Did someone run an unscheduled SSIS ETL package?
  • Was an index being rebuilt?
  • Did someone change application code?
  • Was there a hardware failure of some kind?
  • Was DBCC CheckDB running?

What are Wait Statistics

Whenever SQL Server is waiting for something to happen it logs that information as a WAIT.

What type of things get logged:

  • If your SQL Server is waiting on I/O.
  • If it’s waiting on network traffic to another SQL Server.
  • If your database is waiting on a transaction to complete.

Most everything that SQL Server waits on gets logged. However this information doesn’t stay around for long.

Read More »Understanding your Wait Statistics

Database Health Monitor – Historic Waits

With the recent release of Database Health Monitor Version 2.0 I have decided to focus on of blogging about the features and benefits of the Database Health Monitor application. There are some incredibly valuable features that are often overlooked. The purpose of this blog series is to present some of the features of the product.

If you haven’t tried Database Health Monitor, you can download it at http://DatabaseHealth.com/download. It is completely free.

Historic Waits

The Historic Waits section of Database Health monitor is in my opinion the single most valuable part of the entire product. Other vendors sell products similar the Historic Waits feature for $1500 to $2000 per SQL Server instance, making it cost prohibitive for many.

The way that Historic Waits works is that it installs a small monitoring database on a SQL Server, that can be the same SQL Server you are monitoring, or it can be a separate SQL Server just to keep track of performance.

Over time data is collected in this monitoring database that allows you to then step back in time to see what was happening with the SQL Server at a specific point in time. For instance, if you have Historic Monitoring enabled, if someone comes to you and says “The SQL Server was slow and having problems at 2:00am yesterday”, you have the ability to track down what was happening at that point in time.

The main Historic overview page shows Waits, Plan Cache Hit Ratio, Page Life Expectancy and CPU Load over time.

HistoricOverview1

Read More »Database Health Monitor – Historic Waits