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.
Here is an example of a database with recent backups (Note the screen shot was take on September 3rd in the afternoon.
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.
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
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.
- 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
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.
The presentation is scheduled for 11:00 am to Noon in room 2767 at the College of Southern Nevada campus in Vegas.
Read more ›
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
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
WHERE object_id = (SELECT TOP 1 resource_associated_entity_id
WHERE request_session_id = @sessionID
AND resource_type = 'OBJECT'
AND resource_associated_entity_id <> 50);
Read more ›
Posted in Corruption
Tagged with: DECLARE
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],
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.
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 ›
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.
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 ›