SQL Server 2008 R2

Be careful which node you install a clustered instance from

While installing a clustered SQL Server instance recently I ran across a bit of an annoyance.  When I was going through the SQL Installation process I didn't pay any attention to which node of the cluster currently owned all the disks that I was planning on using for SQL Server.  Because of this...

CPU Affinity Mask and virtualizating SQL Servers

If you have physical SQL Servers that you plan on moving into a virtual environment you'll want to double check your affinity mask settings before actually moving the machine from a physical server to a VM when using P2V software.  The reason for this is that if the affinity mask is set for...

Upgrading MS Ops Manager & SQL Server

Something which has come up when upgrading Microsoft Operations Manager 2007 to 2012 is that there is an extra step which isn't really documented in the Ops Manager upgrade guide.  You see when upgrading from Ops Manager 2007 to 2012 you also need to upgrade the SQL Server to SQL Server 2008 R2 as...

Init Replication From Backup

One of the great features with SQL Replication is the ability to initialize a subscription from backup instead of from a snapshot.  The official use for this is to take a database backup and restore it to a subscriber then replicate any additional changes to the backup. However this technique...

Schema Design Changes shouldn’t just be done once

Just because you did a schema design change before doesn't mean that you shouldn't look into doing another one.  A while back, before I started working with Phreesia, they decided to fix some performance problems by federating some data.  The data is question...

New SQL 2008 R2 SP1 trace flag adjusts autostats threshold

I recently learned about a new trace flag which has been introduced in SQL Server 2008 R2 SP1 (and SQL Server "Denali").  This trace flag, number 2371, changes the way that the SQL Server figures out when auto-stats should kick in. Up until now auto-stats was fixed to kick in every time that...

Database %d was shutdown due to error 9002 in routine ‘HeapPageRef::Update’.

So I hit the error in the title the other night while converting a heap to a clustered index on a production system.  After this error poped up, some other nasty stuff showed up in the error log, which I've included below.

Error: 9002, Severity: 17, State: 4. The transaction log for...

It’s #sqlpass summit session announcement time

So I've managed to trick the kind folks of the SQL PASS program committee once again this year.  I've gotten two sessions accepted for the summit. The first is a "regular session" which is the normal 75 minute community session during which I'll be presenting "Where should I be encrypting my...

Getting rid of those annoying backup successful messages

If you have a SQL Server, and you are doing transaction log backups on the database server you’ll notice that 99% of the messages in your ERRORLOG file are worthless.  All they say is that the log was backed up successfully.  Personally I don’t care that every 15 minutes all 30 databases had...

So you’ve changed the text display settings, and SSMS still won’t show more than 256 characters.

By default SQL Server Management Studio will only show you 256 characters per column.  The fix for this is pretty easy.  You click on Tools > Options to being up the options menu.  From there navigate to Query Results > SQL Server > Results To Text.  Change the "Maximum number of...