Database Health Monitor October 2017 Version Released Today

Download PDF

With over 2000 downloads since the last updated to Database Health Monitor, I figured it was time to get another release of the product out.

New Features in 2.6

  • Added a Job Schedule instance level report.
  • Added an option on the statistics report to show statistics for all databases, and to generate the script to rebuild statistics for all databases. (Customer request)
  • Added a Quick Scan Report check for logins that have a non-existent default database.
  • Added a Quick Scan Report for SQL Server Express Edition, and display a warning.
  • Modified the database mail check in the Quick Scan Report to display a different message when it detects that database mail is not enabled if the SQL Server is running express edition.
  • Modified the Blocked Queries report to better show the blocking query and to differentiate it from those being blocked.
  • Added a Quick Scan Report check for SQL Agent Jobs with no notification on failure.
  • Updated the link to the support site. It now goes to http://support.sqldatapartners.com which is vastly improved over the previous support site.
  • Added a Quick Scan Report check for power settings, recommending that a High Performance option be used.

Bug Fixes in 2.6

  • Fixed a bug when clicking on a database to drill down on the queries needing params report. Previously it was crashing, now it has been fixed.
  • Fixed a bug in the database overview incorrectly reporting unused indexes.
  • Fixed a bug in the server overview panel when displaying the machine or VM type where it was failing on SQL 2012.
  • Fixed a bug with the “Instance Reports” title overlapping the buttons to get to the instance reports on certain resolutions with larger fonts.
  • Fixed some timing related bugs on the Blocked Queries report. It was erroneously showing blocked queries without the query blocking them being shown.
  • Fixed various bugs around compatibility level 70 and 80 and crashing on some of the reports. Yes, people are still running databases at level 70 and 80.
  • Fixed a bug on the server overview page on very large databases, greater than 1TB causing the query to crash.

 

If you already have Database Health Monitor installed, just start up the application and it will check for updates and download the latest version. If you don’t have database Health Monitor installed, you can get it at http://DatabaseHealth.com/Download.

Enjoy,

-Steve Stedman

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

Building a SQL Restore Script When Your Backup Runs

Download PDF

It is handy to have a SQL Restore script ready to go for every backup that you run.  Here is a script that will help with that.

 



-- available at http://databasehealth.com/shared-scripts/


-- Created by Steve Stedman
--    http://SteveStedman.com
--    http://StedmanSolutions.com
--    http://DatabaseHealth.com
--    twitter:   @SqlEmt
--    email:  Steve@StedmanSolutions.com
-- 

-- to use the procecure, first run the whole script to create it, then 
--    uncomment the following line and replace database name with the name 
--    of your database. Do not include square brackets on the database name.
--   NOTICE: the actual script may need to be modified for updated paths or 
--     filenames.
--  examples
--EXECUTE [DBHealthHistory]..[dbhLogChain] 'DBHealthHistory';
---- or
--EXECUTE [DBHealthHistory]..[dbhLogChain] 'DBHealthHistory', 'display';
---- or 
--EXECUTE [DBHealthHistory]..[dbhLogChain] 'DBHealthHistory', 'FileInBackupDir';


-- to use the FileInBackupDir option you need to enable the 'Ole Automation Procedures'
-- uncomment and execute the following 8 lines.
--sp_configure 'show advanced options', 1; 
--GO 
--RECONFIGURE; 
--GO 
--sp_configure 'Ole Automation Procedures', 1; 
--GO 
--RECONFIGURE; 
--GO 

use DBHealthHistory; -- replace this with master, or your dba type database.

-- in case it exists and you need to drop it.
IF OBJECT_ID('dbhLogChain', 'P') IS NOT NULL
BEGIN
	DROP PROCEDURE [dbo].[dbhLogChain];
END
GO



CREATE PROCEDURE dbhLogChain @dbName VARCHAR(2048), 
						     @outputSqlScript VARCHAR(20) = 'display'
-- database name should be quoted with no square brackets
AS
BEGIN
-- ============================================================================
-- Copyright (c) 2015-2017 Steve Stedman 
--    http://DatabaseHealth.com
--    twitter:   @SqlEmt
--    email:  Steve@StedmanSolutions.com
-- ============================================================================

	SET NOCOUNT ON;


	IF LOWER(@dbName) = 'all_databases'
	BEGIN
		DECLARE @DatabaseID as INT;
		DECLARE @DatabaseName as NVARCHAR(50);
 		DECLARE @DatabaseCursor as CURSOR;
 		SET @DatabaseCursor = CURSOR FOR
		SELECT name, database_id
		  FROM sys.databases 
		  WHERE name not in ('tempdb');
 
		OPEN @DatabaseCursor;
		FETCH NEXT FROM @DatabaseCursor INTO @DatabaseName, @DatabaseID;
 
		WHILE @@FETCH_STATUS = 0
		BEGIN
			EXECUTE [DBHealthHistory]..[dbhLogChain] @DatabaseName, 'FileInBackupDir';
			FETCH NEXT FROM @DatabaseCursor INTO @DatabaseName, @DatabaseID;
		END
 
		CLOSE @DatabaseCursor;
		DEALLOCATE @DatabaseCursor;
	END
	ELSE
	BEGIN
		DECLARE @outputBuffer AS VARCHAR(MAX) = '';
		DECLARE @mostRecentBackupFile AS VARCHAR(MAX) = '';
		DECLARE @maxDifferential AS DATETIME;
		DECLARE @maxFull AS DATETIME;

		--Use to enter extra comments
		SET @outputBuffer += 'RAISERROR (''Caution running this script will overwrite the database [' + @dbName + '].'', 20, 1) WITH LOG; ' + char(13) + char(10);
		SET @outputBuffer += '--Make sure to change restore file paths if needed' + char(13) + char(10);
		SET @outputBuffer += '--Consider using STOPAT to recover mid transaction log. Syntax STOPAT = ''YYYYMMDD HH:MM:SS'' Add After NORECOVERY before STATS Example NORECOVERY, STOPAT = ''20150616 15:04:31'', STATS' + char(13) + char(10);

		SELECT TOP 1 @maxFull = bs.backup_finish_date
		  FROM msdb.dbo.backupset bs WITH (NOLOCK)
		 INNER JOIN msdb.dbo.backupmediafamily bmf WITH (NOLOCK)   
			ON bs.media_set_id = bmf.media_set_id
		 WHERE bmf.physical_device_name <> 'NUL' -- special case to deal with Veeam
		   AND NULLIF(bmf.physical_device_name, '') is not null
		   AND database_name = @dbName
		   AND bs.is_copy_only = 0
		   AND bs.is_snapshot = 0
		   AND bs.type = 'D' -- Full Database
		 ORDER by backup_set_id desc;  -- DatabaseHealth

		SELECT TOP 1 @maxDifferential = bs.backup_finish_date
		  FROM msdb.dbo.backupset bs WITH (NOLOCK)
		 INNER JOIN msdb.dbo.backupmediafamily bmf WITH (NOLOCK)   
			ON bs.media_set_id = bmf.media_set_id
		 WHERE bmf.physical_device_name <> 'NUL' -- special case to deal with Veeam
		   AND NULLIF(bmf.physical_device_name, '') is not null
		   AND database_name = @dbName
		   AND bs.is_copy_only = 0
		   AND bs.is_snapshot = 0
		   AND bs.type = 'I' -- Differential
		 ORDER by backup_set_id desc;  -- DatabaseHealth

		 IF @maxFull > @maxDifferential OR @maxDifferential is NULL
		 BEGIN
			SET @maxDifferential = @maxFull;
		 END

		SET @outputBuffer += 'use [master];' + char(13) + char(10);
		SET @outputBuffer += '--ALTER DATABASE ' + QUOTENAME(@dbName) + ' SET SINGLE_USER WITH ROLLBACK IMMEDIATE;'  + char(13) + char(10);

		SELECT TOP 1 @outputBuffer += '--RESTORE DATABASE ' + QUOTENAME(database_name) + 
			   ' FROM DISK = ''' + bmf.physical_device_name + ''' WITH <<<<<MOVES>>>>>--REPLACE, NORECOVERY, STATS = 1;'  + char(13) + char(10),
			   @mostRecentBackupFile = bmf.physical_device_name
		  FROM msdb.dbo.backupset bs WITH (NOLOCK)
		 INNER JOIN msdb.dbo.backupmediafamily bmf WITH (NOLOCK)   
			ON bs.media_set_id = bmf.media_set_id
		 WHERE bmf.physical_device_name <> 'NUL' -- special case to deal with Veeam
		   AND NULLIF(bmf.physical_device_name, '') is not null
		   AND database_name = @dbName
		   AND bs.is_copy_only = 0
		   AND bs.is_snapshot = 0
		   AND bs.type = 'D' -- full database
		 ORDER by backup_set_id desc;  

		IF NULLIF(@mostRecentBackupFile, '') IS NOT NULL
		BEGIN
		
			DECLARE @moves AS VARCHAR(MAX);
			SET @moves = char(13) + char(10);
			DECLARE @Table TABLE 
			(
				[LogicalName] varchar(128),
				[PhysicalName] varchar(128), 
				[Type] varchar, 
				[FileGroupName] varchar(128), 
				[Size] varchar(128), 
				[MaxSize] varchar(128), 
				[FileId] varchar(128), 
				[CreateLSN] varchar(128), 
				[DropLSN] varchar(128), 
				[UniqueId] varchar(128), 
				[ReadOnlyLSN] varchar(128), 
				[ReadWriteLSN] varchar(128), 
				[BackupSizeInBytes] varchar(128), 
				[SourceBlockSize] varchar(128), 
				[FileGroupId] varchar(128), 
				[LogGroupGUID] varchar(128), 
				[DifferentialBaseLSN] varchar(128), 
				[DifferentialBaseGUID] varchar(128), 
				[IsReadOnly] varchar(128), 
				[IsPresent] varchar(128), 
				[TDEThumbprint] varchar(128)
				--,[SnapshotUrl] nvarchar(360)  --- ONLY SQL 2016 or NEWER
			);
		
			INSERT INTO @table
			EXEC('RESTORE FILELISTONLY  FROM DISK=''' + @mostRecentBackupFile  + '''   ');

			SELECT @moves = @moves + '--MOVE ''' + LogicalName + ''' TO ''' + PhysicalName + ''',' + char(13) + char(10) from @Table;

			SET @outputBuffer = REPLACE(@outputBuffer, '<<<<<MOVES>>>>>', @moves);

			SELECT TOP 1 @outputBuffer += '--RESTORE DATABASE ' + QUOTENAME(database_name) + 
				   ' FROM DISK = ''' + bmf.physical_device_name + ''' WITH NORECOVERY, STATS = 1; -- Differential'  + char(13) + char(10),
				   @mostRecentBackupFile = bmf.physical_device_name
			  FROM msdb.dbo.backupset bs WITH (NOLOCK)
			 INNER JOIN msdb.dbo.backupmediafamily bmf WITH (NOLOCK)   
				ON bs.media_set_id = bmf.media_set_id
			 WHERE bmf.physical_device_name <> 'NUL' -- special case to deal with Veeam
			   AND NULLIF(bmf.physical_device_name, '') is not null
			   AND database_name = @dbName
			   AND bs.backup_start_date > @maxFull
			   AND bs.is_copy_only = 0
			   AND bs.is_snapshot = 0
			   AND bs.type = 'I' -- differential
			 ORDER by backup_set_id DESC;  

			 SELECT TOP 1 @mostRecentBackupFile = bmf.physical_device_name
			  FROM msdb.dbo.backupset bs WITH (NOLOCK)
			 INNER JOIN msdb.dbo.backupmediafamily bmf WITH (NOLOCK)   
				ON bs.media_set_id = bmf.media_set_id
			 WHERE bmf.physical_device_name <> 'NUL' -- special case to deal with Veeam
			   AND NULLIF(bmf.physical_device_name, '') is not null
			   AND database_name = @dbName
			   AND bs.backup_start_date > @maxDifferential
			   AND bs.is_copy_only = 0
			   AND bs.is_snapshot = 0
			   AND bs.type = 'L' -- log backup
			 ORDER by backup_set_id DESC;  

			SELECT @outputBuffer += '--RESTORE DATABASE ' + QUOTENAME(database_name) + 
				   ' FROM DISK = ''' + bmf.physical_device_name + ''' WITH NORECOVERY, STATS = 10; -- Log Backup'  + char(13) + char(10)
			  FROM msdb.dbo.backupset bs WITH (NOLOCK)
			 INNER JOIN msdb.dbo.backupmediafamily bmf WITH (NOLOCK)   
				ON bs.media_set_id = bmf.media_set_id
			 WHERE bmf.physical_device_name <> 'NUL' -- special case to deal with Veeam
			   AND NULLIF(bmf.physical_device_name, '') is not null
			   AND database_name = @dbName
			   AND bs.backup_start_date > @maxDifferential
			   AND bs.is_copy_only = 0
			   AND bs.is_snapshot = 0
			   AND bs.type = 'L' -- log backup
			 ORDER by backup_set_id ASC;  

			SET @outputBuffer += '--RESTORE DATABASE ' + QUOTENAME(@dbName) + ' WITH RECOVERY;'  + char(13) + char(10);
	
			SET @outputSqlScript = LOWER(@outputSqlScript);
			IF @outputSqlScript = 'display'
			BEGIN
				PRINT @outputBuffer;
			END

			IF @outputSqlScript = 'fileinbackupdir'
			BEGIN
				DECLARE @ole INTEGER;
				DECLARE @fileID INTEGER;
		
				SET @mostRecentBackupFile = @mostRecentBackupFile + '.restore_script.sql';
				--print @mostRecentBackupFile;
			
				EXECUTE sp_OACreate 'Scripting.FileSystemObject', @ole OUT;
				EXECUTE sp_OAMethod @ole, 'OpenTextFile', @fileID OUT, @mostRecentBackupFile, 2, 1;
				EXECUTE sp_OAMethod @fileID, 'WriteLine', Null, @outputBuffer;
				EXECUTE sp_OADestroy @fileID;
				EXECUTE sp_OADestroy @ole;
			END
		END
	END
END
GO
EXECUTE [DBHealthHistory]..[dbhLogChain] 'ALL_DATABASES', 'FileInBackupDir';
Posted in SQL Server Tagged with: , , , , , , ,

Script all Agent Jobs Using SQL Server Management Studio

Download PDF

While doing a recent server move, I came across the need to script all agent jobs and save them to a file so that if we needed to reference what was there after the move we could get at the agent jobs without having to restart the old SQL Server.

It made me realize that sometimes what you want to do is available in SQL Server Management Studio, but that perhaps it is not always entirely obvious.

 

In the example below, where I have four jobs (imagine if it was 150 jobs), I want to script all of them out and save them for future use.

I could just right click on each one, choose “Script Job As, CREATE To, New Query Window”, which wouldn’t be that bad for 4 jobs, but assume you need to do this for 150 jobs, it could take you a while.

Read more ›

Posted in Uncategorized Tagged with: , , ,

Backup and Restore – Pass Chapter Presentation

Download PDF

Yesterday I had the opportunity to practice one of my sessions on Backup and Restore that I will be presenting at PASS Summit this year. I presented it at two different SQL Server Users Group meetings, one in Bellingham WA, and one in Hartford CT. The one in Hartford was a remote presentation.

This is an introductory level presentation targeted at anyone who is responsible for backup and recovery of their SQL Servers.

Here is an example of where I was explaining what Recovery Time Objective (RTO) and Recovery Point Objectives (RPO) are.

RPO and RTO with SQL Server Backup and Restore

In this session we cover how to improve your backup strategy how to improve your Recovery Point Objective, and how to reduce your Recovery Time. Differential backups are often overlooked in a SQL Server backup strategy, learn how to utilize Differential backups and to save disk space.

If you have ever had to restore from backup and your manager has been looking over your shoulder asking “When will it be done”, then this is the session for you.

Backup and Restore

The topics covered in this presentation were:

  • Recovery Point Objectives and Recovery Time Objectives
  • Types of Backups
  • RPO and RTO Scenarios
  • Demo: Backup
  • Demo: Restore
  • Building a Restore Script
  • Changes in SQL Server 2017

One of my favorite items in this presentation is the creation of the automatic restore script. If you can create the restore script at the time the backup is run rather than when you have a failure you can dramatically reduce the time it takes to get that restore started, thus reducing your Recovery Time.

The slides and sample code are available for download here: Backup and Restore _ 9_12_2017.zip.

 

If you are looking for a speaker for your PASS Chapter or SQL Server Users Group, please contact me, I would be happy to present this or other sessions at your users group meeting.

 

See Also:

 

Posted in PASS Chapter Tagged with: , , , , ,

Visualizing VLFs – Another update

Download PDF

A while back a wrote a blog post with a query to Visualize the VLF’s in your database. Today I have an update to that script.

The previously updated script added another column called “TextStatus” to the output to give you a better idea of what the different statuses mean. You now get 3 statuses shown, “In Use”, “Available”, and “Available Never Used”. The If you have lots of VLFs that are “Available Never Used” that may be an indication that your log file may be larger than you need. If you don’t have any that are “Available Never Used” the log may be smaller than you need.

This latest update changed the character shown in the bar chart to a X or an O depending on if the current file is in use.

Read more ›

Posted in SQL Server Tagged with:

Determining how much of your data file has been modified

Download PDF

SQL Server 2017 introduces a new column in the sys.dm_db_file_space_usage system table. The column is modified_extent_page_count, which tells how many pages have been modified in your data file since the last full backup.


SELECT * 
FROM sys.dm_db_file_space_usage; 

What is really interesting about this is that with a little math you can calculate the percentage of your data file that has been modified.  This would be useful when running differential backups to be able to determine if it would make more sense to run a full backup or a differential backup. Basically when your differential backup gets large enough, based on the number of modified_extent_page_count pages, then it may make sense to do a full backup and reset this counter, and get back to smaller differential backups.

Here is an example

SELECT df.name, df.physical_name,
 total_page_count,
 allocated_extent_page_count,
 modified_extent_page_count, 
 100.0 * modified_extent_page_count / allocated_extent_page_count as PercentChanged
FROM sys.dm_db_file_space_usage fsu
INNER JOIN sys.database_files df on df.file_id = fsu.file_id;

 

Now based on the Percent Changed column we can make some assumptions on the size of the differential backup, and decide if we want to do a differential backup or a full backup.

Read more ›

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

My lucky day

Download PDF

It appears to be my lucky day. Who would have thought that someone I don’t even know would want me to invest $48,000,000 for them. Wow it is so exciting.

 

ok… now that I am through the sarcasm, I am just logging this to share some of the crazy scams that we come across every day.  Sorry Anthony Martinez, but I am going to be unable to take you up on your scam, maybe one of the other recipients listed on the To: line will be able to help you.

Read more ›

Posted in Scam Tagged with:

Using Antivirus with SQL Server

Download PDF

In a perfect world, your SQL Server would be so secure that you would not need antivirus software, you would have behind layers of firewalls, nobody would ever connect with remote desktop to install anything, and it would always have all of the latest security patches… But that is not the real world.

Given that your SQL Server often times contains extremely valuable information, and that the damage that could be done by virus software, malware, and ransomware could be so great then it is strongly recommended that you run antivirus software on your SQL Server. There are some files that you will want to exclude from the virus check.

SQL Server Antivirus Exclusion List:

You will want to exclude the following files from the antivirus check list.

  • SQL Server data and log files (.mdf, .ldf and .ndf files)
    • It is probably a good idea to just exclude the entire directory that holds your data or log files. The reason that I suggest this is that there are other files created in the same location as the data file when certain things run, for instance CheckDB creates temporary snapshot files in the data file directory. Excluding the data and log file directory would be the safe way to go here.
  • SQL Server backup files
  • Full-Text catalog files (if you are using full text search)
  • Trace files (if you have traces running)
  • SQL audit files (for SQL Server 2008 or later versions)
  • SQL query files (.sql extension)
  • The directory that holds Analysis Services data
  • The directory that holds Analysis Services temporary files that are used during Analysis Services processing
  • Filestream data files

 

The problem you run into is when someone installs antivirus on the SQL Server, without being aware of the exclusions, you can run into a number of issues such as the following:
• When the SQL Server is running, data and log files are generally locked preventing other processes from being able to change or read them. This can cause problem with the antivirus software blocking attempting to get to these files.
• If you restart the system, or restart the SQL instance, it is possible that the antivirus software could start first and open a data or log file to scan it for viruses, at which point when SQL Server starts it may not be able to access that file, therefore preventing SQL Server from starting.
• If the antivirus file finds some pattern that it suspects as a virus in your data or log file, and it attempts to quarantine that file it could lead to extreme problems with the SQL Server.
My recommendation is to always run antivirus software on your SQL Server, but be sure to exclude the files mentioned above to avoid problems.

Related Links:

 

Posted in SQL Server Tagged with: , , ,

Database Health Monitor Keyboard Shortcuts

Download PDF

Over the years developing Database Health Monitor I have added a number of keyboard shortcuts to make life easier when working in the application.  Some of these are listed here to share since they are not all completely obvious.

 

Backspace Key – Jump to the last report viewed

The Backspace key can be used to jump to the previous report that you were viewing. Not just that, but hit it multiple times and you can browse back through the history of reports that you have recently viewed. This can be very handy so you can jump to where you previously were without having to navigate through the menus.

 

F5 Key – Refresh the current report

When you are viewing a report and the data behind it may have changed, you can hit the F5 key to refresh or reload the current report. This works for most of the reports in Database Health Monitor.

 

Navigating the Instance Reports

When you viewing any of the instance reports listed above, you can use keyboard shortcuts to quickly navigate between those reports, but to navigate between these you need to use the ctrl key combined with either page up, page down, or one of the arrows on the keypad.

Ctrl+PageUp: If you have more than one SQL Server connected with Database Health Monitor Ctrl+PageUp will switch the current instance report that you are viewing to be the previous instance in the list.

Ctrl+PageDown: With more than one SQL Server connected to Database Health Monitor Ctrl+PageDown will switch the current report to view the next instance in the list.

An example.  If you have 25 SQL Servers connected and you wanted to view the Blocking Queries report for each of these 25 servers, you would first navigate to the Blocking Queries report for one of the instances, then hit the Ctrl+PageDown or Ctrl+PageUp 25 times to navigate through each of the instances looking for any blocked queries. You can combine that with the Backspace key described above and do a very quick review of many SQL Servers.

 

Ctrl+Up Arrow:  Same as Ctrl+PageUp described above.

Ctrl+Down Arrow:  Same as Ctrl+PageDown described above.

Ctrl+Left Arrow: Switch to the previous instance report for the same SQL Server instance. Example: If you viewing the Disk Space report and you hit the left arrow you would go to the Databases by Size report, then hit it again and you go to the CPU Load by Hour report.

Ctrl+Right Arrow: Switch to the next report for the same SQL Server.

 

I hope that this collection of Keyboard shortcuts for Database Health Monitor can give you an improved experience using the application.

 

Enjoy, and have a great day!

-Steve Stedman

 

Posted in Database Health Tagged with: , , ,

2 Sessions Accepted for PASS Summit 2017

Download PDF

I am excited to announce that 2 of the sessions that I proposed for PASS Summit 2017 have been accepted. This is the first year that I have had 2 sessions accepted for Summit, and I am looking forward to it.

The two sessions are:

Basics of Database Corruption Repair

Your database is running fine month after month with no problems. 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 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 can do to protect yourself when corruption strikes. Learn how to avoid making things worse, and how to protect your data if things do get worse. You will leave with a checklist of steps to use when you encounter corruption. By the end of this session you will be ready to take on corruption, one database at a time

 

Your Backup and Recovery Strategy

As a DBA you are tasked with running regular backups. How do you know if you backups are working? How do you know if they are not? Do you have the right recovery strategy? Learn why your recovery strategy should be the goal, not a backup strategy. This session will cover Recovery Point Objectives, and Recovery Time Objectives, along with different backup types that apply to specific objectives, including full backups, differential, log and copy only backups. Learn which backup type is right for your needs? Learn what SQL Server 2017 introduces to help with your regular backups. Find out about backup encryption and compression. Find out how to create your restore script when the backup is run, rather than at a groggy 4:00am when the restore is needed. You will leave this session equipped to do backups, and more importantly restores, with confidence.

 

If you are going to be attending PASS Summit 2017, please look me up, attend one of my sessions, or just say hi. For our SQL Data Partners Podcast Listeners, be sure to look me up and say hello, it would be great to meet you.

More information:

See you there!

-Steve Stedman

Posted in Corruption, PASS Summit Tagged with:
Top