Corruption Repair Complete
This weekend the Stedman Solutions team had the opportunity to help a new client repair their corrupt database. By Sunday evening we had their database… Read More »Corruption Repair Complete
This weekend the Stedman Solutions team had the opportunity to help a new client repair their corrupt database. By Sunday evening we had their database… Read More »Corruption Repair Complete
If you know me, you will know that I really enjoy working on and fixing database corruption, as well as working on SQL Server internals,… Read More »#SQLHELP and a mention on Knee Deep in Tech podcast
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.
With SQL Server 2017, there was a new compatibility level introduced, level 140 the new SQL Server 2017 Compatibility Level. SQL Server can run in a… Read More »SQL Server 2017 Compatibility Levels
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… Read More »Microsoft SQL Operations Studio
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.… Read More »Database Health Monitor October 2017 Version Released Today
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 »Script all Agent Jobs Using SQL Server Management Studio
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.… Read More »Backup and Restore – Pass Chapter Presentation
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,… Read More »Using Antivirus with SQL Server
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… Read More »Database Health Monitor Keyboard Shortcuts