Day 6 of 31 Days of Disaster Recovery: Dealing With Corruption in Allocation Pages

Yesterday, I covered corruption in nonclustered indexes, the easiest type of corruption to handle. Today, I’m going to move on to something slightly more complex, yet still really simple to manage. Today, I’m going to talk about what to do when you encounter corruption of an allocation page. Allocation pages cannot be repaired not can they be single-page restored. If you have corruption of an allocation page, you need to restore the whole database. If you missed any of the
read more

Day 5 of 31 Days of Disaster Recovery: Dealing With Corruption in a Nonclustered Index

Welcome to day 5 of my series on disaster recovery. I want to start digging into some corruption scenarios. We’ll start off with the easiest form of corruption to fix, a nonclustered index. The generic steps we will go through for any corruption scenario are as follows: Identify the corruption (DBCC CheckDB) Identify the objects and types of objects involved Take the appropriate steps to correct Sadly, one of the biggest mistakes people make is to jump straight to the
read more

Day 2 of 31 Days of Disaster Recovery: Protection From Restoring a Backup of a Contained Database

Welcome to day 2 of my month-long series on Disaster Recovery. For today’s post, I want to talk a little bit about restoring backups of contained databases. In particular, what protections are in place in case you are given a backup of a contained database to restore without being told it has containment enabled. If you missed yesterday’s post in the series, you can check it out here: 31 Days of disaster Recovery Does DBCC Automatically Use Existing Snapshot? Restoring
read more

Day 1 of 31 Days of Disaster Recovery: Does DBCC Automatically Use Existing Snapshot?

Welcome to my series on Disaster Recovery. I will spend the entire month of January focusing on all things related to disaster recovery including topics like corruption, data integrity, data loss, DBCC commands, and more. For my first post of this month, I want to take a look at the myth that the DBCC CHECK commands will automatically use an existing database snapshot if one exists for the database. This is a myth I believed myself at one time and
read more

Tempdb Contention That Can't be Soothed

Tempdb Contention That Can’t be Soothed By Adding Files I’ve talked a lot in the past about tempdb contention and how to monitor for and identify it in blog posts, a whitepaper, webcasts, and live presentations. Okay, so I tend to get a little preachy about certain things and configuring tempdb to prevent tempdb contention is one of my top pet projects. But there’s some common types of tempdb contention that can’t be fixed by adding data files. i got
read more

Is SELECT Identity() … INTO Minimally Logged in Simple Recovery?

Is SELECT Identity() … INTO Minimally Logged in Simple Recovery? I was having a discussion with Idera (@idera_software) dev lead Vicky Harp (blog|@vickyharp) about minimizing log impact when performing schema changes on really large tables. One option discussed was using SELECT INTO to create a new table and populate it in a minimally logged operation. We would also need to add an identity column to the new table definition, and we would include the IDENTITY() function to create it. Later
read more

T-SQL Tuesday #31 – Bulk-Logged Recovery Model and Point-in-time Restore

This blog entry is participating in T-SQL Tuesday #31, hosted this month by Aaron Nelson (Blog|@SQLVariant). You are invited to visit his blog to join the blog party. You are welcome to write your own participating blog post for the party or just to read more blogs participating in this month’s theme: Logging. For my part in this shindig known as T-SQL Tuesday, I’m going to write about the bulk-logged recovery model and point-in-time restore. When choosing a recovery model
read more

Why We Follow Best Practices

There are many reasons why we follow best practices. My own reasons have changed over the course of my career. Early in my career as a database developer and then later as a new DBA, I followed best practices because people who claimed to know more about SQL Server said to, and I assumed that their advice would be better than my own. This of course led to me adopting the best practice of not following the advice of someone
read more

The Barking Dog Analogy

A discussion started today on Twitter about CXPacket waits. There had been a heated debate on the subject on Twitter the previous night (wow, does that sound geeky), so many of us got looped into today’s discussion. Someone had experienced a problem with a delete query on a large table causing all CPUs to nearly max out (96% average). In his investigations, he looked at the total waits on the server and saw that the second highest waits were CXPacket
read more

Session Files for Replication Magic Presentation to WSSUG

Session Files for Replication Magic Presentation to WSSUG I presented my session on Replication Magic: Initializing from Backup to the Wichita SQL Server User Group last night. I took a little longer to post my session files so I could add comments to the SQL scripts. Session files are below. Powerpoint silde deck: ReplicationMagic.pptx (1.27 MB) Demo 1- Using Read/Write filegroups:       ReadWriteFGBackup.sql (3.63 KB)       ReadWriteFGRestore.sql (1.25 KB) Demo 2- Using filegroups by name:       FGByNameBackup.sql (3.53 KB)       FGByNameRestore.sql (1.3 KB)
read more