Featured Report: Backup Status Report

Download PDF

As part of the Database Health Monitor version 2.1 release there were many new report includes, one of those was the backup status report. A quick way to page through all of your SQL Servers and check on the status of backups.

Here is an example of the backup status report showing a database that hasn’t had any recent backups, notice the amount of red text to quickly point out the databases with no recent backups.

Backup Status

Here is an example of a database with recent backups (Note the screen shot was take on September 3rd in the afternoon.

Backup Status

On of my favorite new features added to the Database Health Monitor in the version 2.1 release is the ability to page up and down through the different instances you are connected to. Lets say you have 30 SQL Servers that you need to check on, you can just connect to all of those with Database Health Monitor, then when you are viewing the report use the CTRL+Page Up or CTRL+UP arrow to jump to the previous instance, and you can use CTRL+Page Down or CTRL+Down arrow to jump to the next instance. This makes it very quick and easy to scan through all your SQL Servers and get a visual representation of their status regularly.

You can download Database Health Montor for free today at the Database Health Monitor download page at http://DatabaseHealth.com

Is it really free???   People keep asking me this, YES its really free, free for personal use, free for commercial use, free for consultants, corporations, anyone who wants to use it.


Posted in Database Health, DBA Tagged with: , ,

Database Health Monitor version 2.1 Just Released

Download PDF

I have just released version 2.1 of Database Health Monitor, this release involved 2 months of development since version 2.0 was released in July.

People often times ask me if this is free, or free to use in production, or free to use on more than one server. The answer is yes indeed, this is a totally free application to use on as many SQL Servers as you want, fully featured, not a trial version. Yes its free. I know that can be hard for some to understand in today’s world, but yes it is free.

Download link for Database Health Monitor


Database Health Monitor version 2.1

Version 2.1 Release Notes

Released September 7th 2015.
Two months since Database Health Monitor version 2 was released and there have been many new features and bug fixes.

New Features

  • Added support for the F5 key to refresh all report pages.
  • Hotkeys added for paging up and down, and back and forth through instance level reports.
    • CTRL + up arrow, or CTRL + PGUP to switch to the previous instance.
    • CTRL + down arrow or CTRL + PGDOWN to switch to the next instance.
    • CTRL + left arrow to switch to the previous report.
    • CTRL + right arrow to switch to the next report.
  • For historic monitoring, it is no longer required to enter your username and password to set up monitoring if you are monitoring the current instance.
  • From the connection advisor you now have the ability to kill the session you are viewing.
  • The main list of databases has been renamed to the Database Health Monitor – Performance Dashboard.
  • The performance dashboard now includes a summary of total server memory, and for SQL Server 2008 and newer it shows the available memory.
  • The performance dashboard now shows the active query count. Those that are in memory and running for more than a second.
  • Quick Scan Report – many additional checks
    • Reporting for autoshrink and autoclose added to the Quick Scan Report.
    • Check for the default max server memory setting.
    • Check for bloated error logs that haven’t been cycled in a while.
    • Adding check for DBCC CheckDB never run, or not recently run.
    • Check for page verify option of NONE or TORN_PAGE_DETECTION.
  • Added a percentage column to the CPU used by database report.
  • Instance reports panel added into the server overview page. Previously you could only get to the instance reports from the right click popup menu, now they are easier to get to.
  • Instance level reports added.
    • Backup Status Report
    • Configuration Values
    • CPU By Database
    • Database by Size
    • I/O by Drive
    • Job History
    • Sessions by Login
    • Trace Flags
    • What is Active
  • Right click ability to kill sessions from the current connections report.
  • Added a help text panel on the bottom left, which gives additional details on some of the reports.
  • CTRL+a for select all on the connections advisor dialog.
  • CTRL+a for select all on the backup advisor restore chain script.
  • CTRL+a for select all on the one time query advisor.


With all these new features and all of the existing features, this application is becoming extremely useful to DBAs and database developers worldwide.

One of my favorite new features in this version is the ability to page through the instance level reports and to quickly switch SQL Server instances with the CTRL+Up, CTRL+Down, CTRL+Right, and CTRL+Left key combinations. This saves me so much time when I am checking in on my databases.

Download it for free today at the Database Health Monitor download page at http://DatabaseHealth.com


Posted in Uncategorized Tagged with: , , , , , , ,

Presenting at SQL Saturday Las Vegas

Download PDF

This Saturday, September 12th, 2015 I will be presenting at SQL Saturday Las Vegas. My presentation is on Database Corruption, here are the details.

When Database Corruption Strikes – Will you be ready?

Duration: 60 minutes

Track: Enterprise Database Administration & Deployment

You are working along month after month with no problems in your database. Suddenly someone reports that their query won’t run. They get an error stating “SQL Server detected a logical consistency-based I/O error“, or something even scarier. Do you know what to do now? We will walk through 3 or 4 actual corrupt databases exploring ways to go about finding and fixing the corruption. More importantly we will explore how to prevent further data loss at the time corruption occurs. Learn what things you should do to protect yourself when corruption strikes. Learn what to avoid that will make things worse. You will leave with a checklist of steps to take when you encounter corruption. By the end of this session you will be ready to take on corruption, one database at a time.

SQL Saturday Las Vegas

The presentation is scheduled for 11:00 am to Noon in room 2767 at the College of Southern Nevada campus in Vegas.

Read more ›

Posted in Corruption, SQL Saturday Tagged with: , , , ,

mssqlsystemresource Database

Download PDF

I was looking through my SQL Server error logs to confirm that CheckDB was being run as I had scheduled based on my previous post to run DBCC CheckDB on all databases. I wanted to confirm that there was no corruption, and that all of the databases had been checked, and not had been missed. Going through this I noticed the logging of all of my databases, but one extra database showed up, the mssqlsystemresource database.

mssqlsystemresource database

Now the mssqlsystemresource database is an internal SQL Server database that is used by SQL Server, it also gets replaced by SQL Server when you do an upgrade of your SQL Server database. It is hidden so that people don’t have access to it, and it doesn’t show up when you run the undocumented sp_msforeachdb it doesn’t include the mssqlsystemresource database. Additionally the sys.databases view doesn’t include the mssqlsystemresource database.

Read more ›

Posted in SQL Internals Tagged with: , , , ,

Status of DBCC CheckDB

Download PDF

So you are checking your database with DBCC CheckDB and of course if you are like me you use the WITH NO_INFOMSGS parameter. But it turns out that CheckDB is taking longer to run that you expected, and you want to check and see what table is currently being checked.

Here is a quick SQL Script that I use to find the current table that is being checked by DBCC CheckDB

use [your database];

-- use this to find the session id of DBCC CheckDB
SELECT session_id, start_time, command, percent_complete, total_elapsed_time, 
       estimated_completion_time, database_id, user_id, last_wait_type
  FROM sys.dm_exec_requests  
 CROSS APPLY sys.dm_exec_sql_text(sql_handle)
 WHERE command like '%DBCC%';  

DECLARE @sessionID as integer = 53; -- enter the session ID of DBCC CheckDB Here

SELECT name, schema_id, type_desc
  FROM sys.objects 
 WHERE object_id = (SELECT TOP 1 resource_associated_entity_id 
                      FROM sys.dm_tran_locks
					 WHERE request_session_id = @sessionID
					   AND resource_type = 'OBJECT'
					   AND resource_associated_entity_id <> 50);

status of dbcc checkdb


Read more ›

Posted in Corruption Tagged with: , , ,

SQL Server Script to Display Job History

Download PDF

Its not always quick and easy in SQL Server to get a full list of the jobs that have been run, when they were run and how long they took. I created the following script to quickly check on the status and run time of SQL Server maintenance plans and jobs.

Display Job History

;WITH jobListCTE as
  SELECT j.name as job_name, 
		 msdb.dbo.agent_datetime(run_date, run_time) AS run_datetime,
         RIGHT('000000' + CONVERT(varchar(6), run_duration), 6) AS run_duration,
    FROM msdb..sysjobhistory h
   INNER JOIN msdb..sysjobs j ON h.job_id = j.job_id
   WHERE h.step_name = '(Job outcome)'
SELECT job_name as [JobStep], 
       run_datetime as [StartDateTime], 
	   SUBSTRING(run_duration, 1, 2) + ':' + 
	   SUBSTRING(run_duration, 3, 2) + ':' + 
	   SUBSTRING(run_duration, 5, 2) as [Duration], 
  FROM jobListCTE
 ORDER BY run_datetime DESC, job_name;

Just run the script in SQL Server Management Studio to get your job history ordered descending by when the job was run.

Posted in SQL Server, TSQL Tagged with: , , , ,

DBCC CheckDB All Databases

Download PDF

If you use the SQL Server maintenance plans to run DBCC Check DB, you are not allowed to include TempDB in your DBCC CheckDB sequence. For a while I was using maintenance plans, and ending up adding a separate job step to run TSQL to just check TempDB.

Rather than using two different steps, the regular maintenance plan, and a special step for Temp DB, I have instead dumped the regular maintenance plan step, and just used the following TSQL code to run DBCC CheckDB against all databases on the SQL Server.

Option 1: DBCC CheckDB All Databases using sp_msforeachdb

I have included Option 2 below since there are some pretty serious flaws in the sp_msforeachdb script as shown in option 1, specifically sp_msforeachdb will occasionally skip databases. It also has trouble with databases created with certain characters.  Anyway if you want it, here is, but I would recommend paging down to Option 2 which is much more reliable. I would like to thank Patrick Flynn who showed me the flaws in sp_msforeachdb.

The stored procedure sp_msforeachdb takes a parameter of query that will get run against all databases on your SQL Server Instance.

EXEC sp_msforeachdb 'DBCC CHECKDB(''?'') WITH NO_INFOMSGS;';

You can set it up as a maintenance plan like this:

Read more ›

Posted in Uncategorized Tagged with: , , ,

Database Health Monitor Version 2.1 coming soon

Download PDF

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 ›

Posted in Database Health Tagged with: , , , , ,

Introduction to SQL Server Class

Download PDF

From September 15th 2015 to October 1st 2015 on Tuesday and Thursday evenings I will be teaching a Introduction to SQL Server class. Interested you can sign up here:  Class Info and Signup

Introduction to SQL Server – Why take this course?

This course is intended as an Introduction to SQL Server for business persons or other professionals who have never used Microsoft SQL Server. Would you like to learn how to interact with a database in a meaningful way? We will cover the basics of getting started, including installation of the SQL Server product with associated tools. You will learn the foundations of structured query language including querying data, creating tables, and bringing data together from multiple tables. Once you understand how to query the data, we will look at building reports for a powerful presentation of your data. Over six evening classes we will go from zero (or minimal) knowledge of SQL Server to a solid understanding of how to query data, collect data, and most importantly how to present that data in a way that makes sense.

This class is made up of 6 sessions from 6-9 pm on Tuesdays and Thursdays starting Sept 15th at the Herald building conference room.

In this course we will be using SQL Server 2014, however almost all the topics we cover will apply directly to SQL Server 2008, SQL Server 2008R2, SQL Server 2012, SQL Server 2014 and SQL Server 2016.

Introduction to SQL Server



I am teaching this class in a partnership with CodeLily, a Computer Science and Web Development Education Studio located in Bellingham WA.

CodeLily Introduction to Databases: SQL Server

This will be similar to the Introduction to SQL Server class that I have taught at Western Washington University in the past, however it has been updated with new material and new technology.

This Introduction to SQL Server class is in person in a classroom type setting. Sorry if you are out of the area there is no virtual or remote option available.

Related Links

Posted in Classes Tagged with: ,

Silencing Backup Messages with Trace Flag 3226

Download PDF

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.ErrorLog_flooded

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 ›

Posted in Database Health Tagged with: , , , , , ,

SQL Server Performance Tuning

Need help with SQL Server Performance Tuning, contact Steve at Stedman Solutions, LLC for help, or take a look at the performance tuning page at the SteveStedman.com blog. We can help with tuning a single query or with figuring out why your SQL Server is running slow. Free 30 minute consultation.

Newsletter Signup

Stay informed of Database Corruption Challenge events, and other things happening at SteveStedman.com with my newsletter.
Newsletter signup form.

Stedman Solutions