Skip to content

Database Health

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

Database Health Monitor Version 2 Released

After 4 years of beta and 5500 installs of Database Health Monitor beta releases in the last 2 years, Database Health Monitor version 2 is finally complete. It is no longer in beta. If you are curious about the history of Database Health Monitor, take a look at my post from earlier in the week.

Here is a preview of one of the latest reports added in the Version 2.0 release of Database Health Monitor.

Database Health Monitor Version 2

Read More »Database Health Monitor Version 2 Released

Database Health Monitor Version 2 Almost Done

Over the last 4 years I have been working on the Database Health Monitor. I am about a week away from releasing Version 2, which will finally be out of the beta process. In preparation for the release I am sharing some of the history of this program.

In 2011 version 1 was released as a set of my favorite monitoring queries that had been formatted and made available through SSRS. As people started to try it out in late 2011 and early 2012, I quickly discovered that the process for distributing shared queries via SSRS reports wasn’t very easy for the people trying to use them. Many people quickly discovered the difficulty of installing these reports. The set of reports looked something like this.

OnetimeUseQueries

 

There were 10 reports that were linked from the top panel in the SSRS user interface. Although SSRS has some great features, it was challenging to switch database connections and monitor multiple servers.

In April of 2012 I gave up on the concept of using SSRS reports, and converted all the queries into a windows application and started enhancing the program. In September of 2012, I finally had the Beta 1 release of Database Health Reports out. It was a bit rough in the beginning, but from 2012 to 2015 through a series of almost 20 beta releases labeled names like Beta 1, Beta 8.2, Beta 10.1, and so forth I kept enhancing the program. Here is an early screenshot of somewhere around Beta 1 or Beta 2.

Read More »Database Health Monitor Version 2 Almost Done

Database Health Monitor -Beta Version 10.1 Released Today

Today is the release of Database Health Monitor Beta 10.1. If you haven’t see the Database Health Monitor before, its a free application that I have been building over last 4 years.

It has been a while since the last release, so I thought I would just walk through and show a few of the features.

One thing that changed right off was in the previous version, these was a advertisement for my IndieGoGo campaign where I attempted to crowd fund the next phase of development on Database Health Reports. The IndieGoGo campaign has been removed.

Real Time Reports

The real time reports are a category of reports that query the database directly to find out something that is currently happening right now. See the Historic Reports below for the ability to find out what was happening at some point in the past.

One of the new reports added is the Suspect Paged report. If you have been participating the Database Corruption Challenge you will certainly understand the value of this report.

SuspectPages

Read More »Database Health Monitor -Beta Version 10.1 Released Today