Database Corruption Presentation at Pass Summit 2017

Download PDF

Today I had  the opportunity to present on Database Corruption at the PASS Summit 2017 conference.

You can download the presentation as a PDF here: Summit 2017 Corruption.pdf

Here is the agenda from the corruption presentation.

  • What is Database Corruption
  • Causes of Corruption
  • Detecting Corruption
  • Tracking Corruption
  • Removing Corruption
  • Customer Examples
  • Demo

 

Related Links:

Need help with a corrupt database, let me know, we can help.

Tagged with: , , , , , ,

Microsoft SQL Operations Studio

Download PDF

Today at PASS Summit Microsoft announced a new SQL “Tool” for running queries against SQL Server, called the Microsoft SQL Operations Studio.

So what is the Microsoft SQL Operations Studio

This appears to be a new cross platform (Mac, Linux, and Windows) for running queries against SQL Server.

Perhaps this is a replacement for SQL Server Management Studio, or may just a subset of what SSMS does today, however it works cross platform, unlike SSMS.

This is the “free modern data operations tool for SQL everywhere”

Doing a quick search I wasn’t able to find any reference to this yet, so this is brand new and not even released yet.

Tagged with: ,

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

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';
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 ›

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:

 

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 ›

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 ›

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 ›

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:

 

Tagged with: , , ,
Top