Database Health Beta 9.3 Released Today

Today I had the pleasure of releasing Beta 9.3 of the Database Health Monitor. It has been a whole week since the last beta update, and here is the outline of the new features and bug fixes.

Download at DatabaseHealth.com

New Features

  • New report, Partitioned Tables. This new report shows all of the partitioned tables in a database. Double clicking for drill down shows the new Partitioned Tables Advisor that shows details on the number of partitions, rows in each partition and the range for each partition.
  • The entire color scheme has been updated, not so blue.
  • Added double click action on the one time use query report.
  • The duplicate indexes chart has been migrated over to the new report format and colors.

Bug Fixes

  • Performance improvements and bug fixes on the table size advisor. It is still a bit slow on really huge tables, but it works well on most.
  • Fixed the drilldown when double clicking the chart view on the disk space report.
  • Improved exception handling on the needs parameters report.
  • Increased query timeout on the index fragmentation report.
  • Improved the column layout and sizing on the following reports:
    • Missing Indexes Report
    • Unclustered Tables Report
  • Improved error handling on all background threads when attempting to connect to a database. Better handling when the connection fails.
  • Fixed a bug on the plan cache panel not linking to the right report.
  • The enter key now does the same thing as double clicking for drilldown on all grids in the application.

Other Notes

  • Based on improved crash reporting in beta 9.1 and 9.2, I was able to learn more about the common problem areas in the application. This lead to improved exception handling in many of the areas where a database connection is established, along with better error messages when a connection fails for some reason.

 

The Partitioned Tables Report was a new addition to the Database Health monitor with this release.

The partition tables report shows a list of all the partitioned tables in a database. As shown here, there is just one partitioned table in this test database.

PartitionedTables2

Double clicking on the table [dbo].[orders] brings up the Partitioned Table Advisor which shows the number of rows in each partition, the percentage of the table represented by that partition, and the range items for the partition. In this example the partitions are split over date ranges.

 

 

PartitionedTablesAdvisor

I plan to keep the updates coming for some time.  Every week I am learning something new to help make the product even stronger.

Download at DatabaseHealth.com

-Steve Stedman

Database Health Monitor Beta 9.2 Released Today

I released Database Health Monitor Beta version 9.2 today.  No new features, just lots of bug fixing.

I don’t want to make it sound like database health reports is unstable, it is very stable, however with any software there are occasionally unexpected things that cause a crash. My goal is to get Database Health monitor as close to zero crash reports as possible, and to keep it there.

After doing some fixup on the crash reporting in the preview beta, I am now getting better details when something crashes in the application. For this beta and the next I will be mostly focused on overall stability.

Bug Fixes

  • Improved the column formatting on the Unclustered Tables report and the Connections report.
  • Improvement of the Pie chart pages, including
    • Reducing flashing on pie charts with only once piece.
    • Pie slice should highlight on mouse over not just on click
  • The Active Queries Report, now supports click or double click to show query advisor dialog.
  • Adding better exception handling and error bailout on the real time overview page
  • Cleaning up some of the layout on the technical debt dialogs.
  • Re-factoring code around the beta expiration dates
  • Removing reference to obsolete modules

Other Notes

Based on the increased popularity of Database Health Monitor, with more people using the program, there are more fringe case crash reports coming in.  There were many fixes in this update to fix more of those fringe case crash reports. The overall crash reporting has also been enhanced to allow for better tracking and fixing of crash reports in the future.

 

Remember when you run the install program there is a checkbox to allow the program to send crash reports to the developer. Please keep this checked so that I can have the most information possible when something does crash.

 

Links:

My SQL Server is Out Of Disk Space

As the DBA, you get a call from the IT Team who monitors disk space and they tell you the following:

“One of our server that has SQL Server running on it is running low on disk space. It has gone from around 60% disk utilization to 95% disk utilization just overnight. Whats the problem?”

In this case it happens that this is a server that you have never seen before. Someone installed a SQL Server to test out something product along the way, they never told the DBA about it, and suddenly this sever has become an important part of the company internal tools.

Sound familiar?  I know I have been hit with this before.

 

Right off, you have never seen this server before, you have no established baseline to compare it to, and you need to find out why it is running out of disk space rapidly. For me, some of the first things that would cross my mind would be:

  • Has anyone installed a new database or product new on this server recently?
  • Are any of the databases growing rapidly?
  • Are backup files being saved locally?
  • Are backups being run at all?
  • How big are each databases data files and log files?
  • Is there something outside of SQL Server taking up space?

Before I start troubleshooting I am going to just remote desktop to the server and double check the disk space myself to be sure that someone hasn’t misinterpreted the disk utilization.

DiskSpace1

 

At first glance the free disk space is 4.39GB out of 63GB.

DiskSpace2

 

 

As I am thinking about where to look first, the disk space drops to 2.28GB free.  Now I am very concerned.  I know that the C: Drive is filling up, this sever only has a C: drive, and if the Windows operating system runs out of disk space on the boot drive C:, then the server will crash, and often times will no longer boot.  Now things are serious, and must be dealt with quickly.

Now to start hunting, I could dig out several dozen of my favorite trouble shooting queries, or I could just run Database Health Monitor to start troubleshooting the issue. Database Health Monitor is my quick choice to track down problems like this.

I start up Database Health Monitor and connect to the SQL Server that is running out of space, to start looking around.

Initial View

 

Not immediately jumps out.  I see 12 databases on this SQL Server, and I see that the PerformanceTrouble database is using more CPU than any other database on the system.  Now to start looking at individual databases. The first database listed is called BadDB.  For this demo I am using a SQL Server that I use to test the Database Health Monitor Application, and I tend to use names that are descriptive of what the database is intended to do.

FirstDatabase

 

From this database I can see a few red flags. Well, we learn that the Database and Log files all exists on the C: Drive, and that this could cause the database to run out of disk space if the database grows, but this database isn’t very big, its only a few Megabytes, so I am not worried here.

Next I take a look at the Real Time Reports for Backup Sizes:

BackupSizes

 

For this database I can see that it has grow over the last year from around 2 megabytes to around 10 megabytes. All really small.  I also notice from the File names that the backups are being saved on the C: drive too.

As I click through the rest of the databases I see very similar results, its not great, but none of them are very big until I get to the database called PerformanceTrouble. Database Health Monitor shows this overview.

ProblemDatabase

 

Now this page points out a few things.  In bright red, there is a notice that shows the amount of log space allocated and being used by this database is around 24GB, for a database that is only 341MB in size.  This is an indication that backups are not being run, or that backups are not being run often enough, for instance log backups.  On this same page, I can see that the last full backup was run today at 5:00am.  I then click through the rest of the databases and nothing jumps out anywhere near this problem.

At this point I have spent about 3 minutes running Database Health Monitor, and I have found one problem that is using up about 33% of our entire hard drive.

 

How do we fix this issue?

First if I just doubleclick the Large Log File warning at the bottom of the application, my browser will open with one solution. There is a solution presented that looks a lot like this:

First we run this query to get the names of the log files.


-- check the size of the files.
SELECT size / 128.0 as sizeMB, name
FROM sys.database_files;

LogFileSizing

From that query we see that the name of the log file is PerformanceTrouble_log, the script below would get updated that your actual database name and log file name. The following script will shrink the log file for this database to around 1mb.

WARNING: Running this script will disconnect all other users from the database. Only run this on a production database if you have no other option.

-- Truncate the log by changing the database recovery model to SIMPLE.
ALTER DATABASE PerformanceTrouble SET RECOVERY SIMPLE;
GO
-- Shrink the truncated log file to 1 MB.
DBCC SHRINKFILE (PerformanceTrouble_log, 1);
GO
-- Reset the database recovery model.
ALTER DATABASE PerformanceTrouble SET RECOVERY FULL;
GO
-- Be sure to do a full backup, then kick off transaction log backups

-- check the size of the files.
SELECT size / 128.0 as sizeMB, name
FROM sys.database_files;

Once the script is run we can see that the log file has been shrunk from the 24GB to be around 1MB.

When we check the drive space on the server it now looks like this:
CDriveAfterScript
Now we are no longer at the the critical point where we think the server is going to run out of space, but there are two things that should be done next.

1. Run a full backup of this database.  If your SQL Server were to crash before the full backup is run you would have no way to recover back to the current point in time.

2. Monitor and confirm if the database log is continuing to grow, or if this was maybe a one time event that caused it to grow this large. If you determine that it is still growing then research and find out what is causing the transaction log to fill up.  You can use many of the features of Database Health Monitor to do this research.

 

The overall troubleshooting process took about 3 minutes, and the time to fix this issue only took around 2 minutes.

Setting the Board Background Image in Uzility

On of the fun things to do with Uzility is to customize your board background image, this allows you to personalize the look of the board to something that fits your personality or interests.

 

 

Steps to set your board background.

  1. Right click on the background of a board.
  2. From the popup menu choose Backgrounds, then Set Board Background.
  3. Browse for a file on your local computer that you want to upload as your board background.
  4. Choose upload, and that file will be set as your board background.

Setting Time Zones in Uzility

Uzility supports distributed teams in multiple time zones by allowing each user to set their own time zone for their login.

Here is a short YouTube video showing how to change the time zone on your account in Uzility.

 

Visit Uzility.com for more details.

Database Health Reports Beta 9 – Released

Today I have released beta 9 of the Database Health Monitor.

It has been 27 months since the release of Database Health Beta 1, and almost 4 years since the original SSRS implementation of Database Health reports.

To be honest for the first half of 2014, I didn’t contribute much to the Database Health Reports, then in the couple of months, I realized just how many people were really using Database Health Reports, with over 15oo active installs world wide. This changed my outlook on database health reports and I decided to buckle down and focus on the project again.

Beta9

Beta 9 includes the following new features and bug fixes.

New Features

  • Added new real time report for Active Queries. This report shows the queries that are currently running (or blocked and waiting) on the current database. This was based on a customer request.
  • Right click menu option in the database tree view to sort the list of databases by either the database Id or alphabetically by name. This was based on a customer request.
  • New real time report to show database files with IO and stalls for database files and logs.

Bug Fixes

  • Improved the column formatting on the Identity Column Usage report. In some cases the columns were sized too small to read the column titles. This has been fixed.
  • Broken links have been fixed. There are a number of help links in the system that were linking to http://databasehealth.stevestedman.com, these have been fixed to link to http://databasehealth.com.
  • Fixed crash caused by attempting to connect to a SQL Server that no longer exists.

With Beta 9 there has been more extensive testing with SQL Server 2014 to confirm that all the reports work correctly, so far there have been no problems with SQL server 2014.

Another infrastructure change with Beta 9 is that I migrated the source control system from Subversion and switched to Git. This has allowed for much easier branching and the addition of new code into the system.

 

If you are using Database Health Beta 8, I recommend you update to the latest free beta version at http://databaseHealth.com/download.