SQL Server Row Data Linking to Off Row Data

Download PDF

 

The question came up as how to find a link from blog storage that is corrupt back to the table and row that contains that data.

The is no link from the blob storage back to the table and row, but this is a link from the data page containing the table and row off to the blob data.

 

First let’s start with row data and off row data. When SQL Server stores a row that contains variable size data like a VARCHAR, if all the lengths of all the values in the row are less than about 8k, SQL Server stores that entire row in a single data page. If a row contains a set of data larger than 8K, then some if it can be moved off row and stored in blob storage.

Picture the following table:


CREATE table test1
 (
 ID INTEGER IDENTITY PRIMARY KEY CLUSTERED,
 bigVarchar1 varchar(max),
 bigVarchar2 varchar(max)
 );

If you were to put small values into the varchar columns then the entire row would fit in a single data page (8k). But if you put 4k of data into one varchar(max) and 8k of data into the next varchar(max) then it will not fit in a page. One of the varchar values will be moved off page into a page that contains blob storage. No rocket science here yet, but when you want to find the relationship between these page it gets difficult.

 


CREATE DATABASE blobTest;
GO
USE blobTest;
GO

CREATE table test1
(
 ID INTEGER IDENTITY PRIMARY KEY CLUSTERED,
 bigVarchar1 varchar(max),
 bigVarchar2 varchar(max)
);
GO
-- fits into a single page
INSERT INTO test1(bigVarchar1, bigVarchar2) VALUES ('test row ', 'test row ');

--SELECT * FROM test1;

DBCC IND('blobTest','test1',-1) WITH NO_INFOMSGS;

Using the undocumented DBCC IND command you can see that the table (clustered index) is comprised of 2 data pages, the first one of type 10 which is the IAM page or Index Allocation Map, and the secon page of page type 1 which is a standard data row.
<img class=”aligncenter size-large wp-image-6595″ src=”http://stevestedman.com/wp-content/uploads/rowData1-1024×82.png” alt=”” width=”605″ height=”48″ />

Next we add another row.

INSERT INTO test1(bigVarchar1, bigVarchar2) VALUES ('test row ', 'test row ');
DBCC IND('blobTest','test1',-1) WITH NO_INFOMSGS;

And we can see that both rows are still only using the same 2 pages.

Read more ›

Tagged with: , ,

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: , , , , , , ,
Top