Historic Database Rebuild

Overview

Historic monitoring keeps everything it collects in one database, DBHealthHistory, filled by a set of SQL Server Agent jobs. When that database is damaged badly enough that upgrades keep failing, objects go missing, or the historic reports fail to open, the quickest way back to working monitoring is often to throw it away and start again.

Rebuild the history database does exactly that. It drops DBHealthHistory, deletes the DBHealthMonitor agent jobs, and builds both again from nothing, the same way historic monitoring is set up the first time.

History is lost. A rebuild deletes every row the history database has collected. It is not a repair, and there is no undo other than the optional backup described below.

Where to find it

Route How
Historic Waits page The Options button on the third row of the toolbar
Test Historic Monitoring DB window The Rebuild History Database… button beside Test Again

Both open the Historic Options window for the instance you are looking at.

Try this first

A rebuild is the last step, not the first. Before you use it:

  • Run Test Historic Monitoring. It checks the connection, the monitoring list, the linked server and the collection procedure, and repairs a duplicated monitoring entry on its own.
  • If an upgrade failed on one step, the failure message names that step. A single missing procedure can usually be fixed without losing anything.

The Historic Options window

The window reads the history server first and shows what is there:

Item What it tells you
History database Which server holds DBHealthHistory, and whether the database is online, offline or missing.
Created When the database was first built.
Size How much space it takes on the server.
Schema version The version the upgrade chain has reached.
Monitoring Every instance this history database collects for.

Under that sits Rebuild the history database, with two choices:

  • Keep monitoring the same instances. Ticked by default. The list of monitored instances and their connections is read before the drop and written back afterwards, so every instance keeps being monitored. Untick it to set up only the current instance again.
  • Back up DBHealthHistory first. Unticked by default, and shows the size of the backup it would take. It writes a copy only backup to the server’s default backup folder before anything is dropped. If the backup fails, the rebuild stops and nothing is changed.

When the button is not available

The Rebuild history database… button stays disabled, with a message saying why, when:

Message What to do
The login is not a member of sysadmin Dropping the database and deleting agent jobs both need it. Use a login that has it for the history connection.
DBHealthHistory is in an availability group Remove it from the availability group first, then rebuild.
The history server could not be reached Check that the server is up and the saved history connection still works, then click Check again.

A DBHealthMonitor job that is running right now does not block the rebuild. It is stopped first. Click Check again once it has finished if you would rather wait.

A central monitoring server

History does not always live on the instance being monitored. A monitoring server can keep one DBHealthHistory for many instances. In that case the window names that server and lists every instance it monitors, because a rebuild deletes history for all of them, not just for the instance selected in the tree.

With Keep monitoring the same instances ticked, all of them are set up again. Linked servers live on the server rather than in the database, so the rebuild does not touch them.

The confirmation

Clicking Rebuild history database… opens one more window that lists:

  • Deleted: all collected history, with when it started and how big it is; the email alert setup, ignored wait types, assignments and daily checkup actions stored in the database; and the five agent jobs, which are created again.
  • Kept: the list of monitored instances, when that option is ticked.
  • Backed up first: when that option is ticked.

The Delete and rebuild button only works once you type the history server’s name. Letter case does not matter. Cancel and Escape back out without changing anything.

While it runs

The window shows each step as it finishes: stopping any running job, the optional backup, the drop, the five job deletes, the build, putting the monitoring list back, and the checks.

  • Anyone connected to DBHealthHistory is disconnected at once.
  • The build walks the whole schema upgrade chain, which is most of the time. A few minutes is normal.
  • The Database Health Monitor service keeps running its collection procedures on its own schedule. Those calls fail until the new database is ready, and show as failures in the tray history.
  • The window cannot be closed until the rebuild finishes, because closing part way would leave the history database half built.

Afterwards

When the rebuild succeeds, the new database is on the current schema version, the agent jobs are back, and collection starts with their next run. The historic reports stay empty until then, and charts that need several days of data fill in over the days that follow.

If you started from the Historic Waits page, you are taken to the Server Overview, since the page you were on was drawn from the old database.

Save report… writes an HTML record of every step, with how long each took and the schema version rows the build wrote.

If it fails

The window names the step it stopped at, and the steps above it say what happened. Try again runs the removal and the build again. The backup is not taken twice, and the monitoring list comes from what was read before the first attempt.

If the database was offline when it was dropped, SQL Server may leave its data and log files on disk, and the build then fails because a file already exists. Delete those two files from the data folder and click Try again.

Until a rebuild succeeds, the instance has no working history database. Configure Historic Database can also build a new one.

The same thing from the command line

DatabaseHealth2.exe /rebuildHistoricDB "instance" runs the same removal, build and checks with no window, for an instance whose history database is on the instance itself, and writes the same HTML report. It is meant for testing a first time setup.

Frequently asked questions

Can I keep my history and just fix the database? Not with this. A rebuild always starts empty. Tick Back up DBHealthHistory first if you might want the old data later: the backup can be restored under another name and queried.

Does it change anything on the monitored instances? No. Everything happens on the server that holds DBHealthHistory.

Why does it need sysadmin? Dropping a database and deleting agent jobs that another login may own both need it.

Is anything else lost besides history? Settings stored inside the database are: the email alert setup, ignored wait types, assignments and daily checkup actions. Settings stored in Database Health Monitor itself, such as your server list and the Hide choices on the Historic Waits toolbar, are not affected.