Automatic Page Repair in Action

One of the cool features of Mirroring and Always On Availability Groups is Automatic Page Repair. With Mirroring or Availability Groups, when SQL Server encounters certain types of corruption it will look to the Mirror/Replicas to see if they have a clean copy of the page. If they do, it will repair the page in the Principal/Primary and make your life easy. Obviously any occurrence of corruption requires further investigation, but this is a handy feature where SQL Server can bail you out of certain scenarios. In this post I will step through an example of what happens when you corrupt a page in a normal database and then what happens when you corrupt the same page in a database that is part of an Availability Group.
Continue reading

Errors stating the 8060 bytes per row limit has been exceeded when sum of columns is well under 8060 bytes

Awhile back I worked on an issue where a client reached the 8060 byte limit for a row in SQL Server. 8060 bytes is the max size a single row can be as that is the size of a page minus the header (this doesn’t include LOB data types as those are handled differently). They were using a software functionality that adds and removes columns to the table as fields are added and removed within the software. The issue this particular client was seeing was that they had reached the limit when adding new fields (columns), but when they removed fields (dropped columns) to try to make room they still saw the error message.
Continue reading

How a read uncommitted/nolock select can block other processes

I thought I’d start this blog off by sharing something I have encountered more than once in the past few months. Most people believe that if they run their select queries in read uncommitted/nolock that there is no way they can block other processes. This belief is mostly true, but it actually still possible to unintentionally block other processes when running a select with nolock.
Continue reading