I've just spent a bunch time researching an answer to this question on the new disaster recovery forum because I couldn't find any definitive info on how to do this in SQL Server 2005. I pieced together a method to do this on previous releases of SQL...

This blogging thing sucks you in, doesn't it? Not content with having an ongoing series on disaster recovery and CHECKDB (with another 6 and 25 more posts planned respectively), I'm starting a new series on fragmentation. This will begin from first principles...

lock escalation...

I've just been itching to post this one. I'm doing a chalk-talk (AKA cabana session, this year AKA Technical Learning Centre session) at TechEd next Wednesday (8.30am 6/14) called 'DBCC CHECKDB: Magic, Monsters, and Myths'. A lot of the material in...

In my previous blogs, I described the types of objects in TempDB and how they are managed. I hope that it provided you with a good working knowledge of TempDB. Now the next question is how do I configure the TempDB for my production workload? In this...

As you know the tempdb is used by user applications and SQL Server alike to store transient results needed to process the workload. The objects created by users and user applications are called ‘user objects’ while the objects created by SQL Server engine...

(Final blog post of the year for me. Its been a bit of a wild ride the last 6 months - 7 TechEds on 3 continents, 46 blog posts and some major life changes - but now things have calmed down and I should be back to more regular posting in 2007. Tomorrow...

(Boston continues its reputation - with me at least - for great seafood - calamari and pan-seared fresh halibut this evening. Yum! I was tempted to get on the T - Boston's subway system - and see what's happening downtown but with no jacket I'd get wet...

As you can guess from the title, I'm planning a long series over the summer to go into the guts of how CHECKDB works (both the consistency checks part and the repair part). And as you can guess from 'CHECKDB', I'm already bored with putting DBCC in front...

For some reason The Clash's 'Should I Stay or Should I Go?' has been going round in my head today - 'Should I RE-pair or restore? Nah nah NA-na nana nah' - must be something to do with Redmond's good weather. Anyway, I get asked for help with this...

Last week I was teaching a class on snapshot isolation and we discussed the overheads of snapshot isolation. There are three main overheads that you need to be aware of when you enable snapshot isolation read-committed-snapshot on a database. First, each...

SQL Server deploys two strategies to compress the data · First, it stores all fixed length data types in variable length format. If you recall, SQL Server 2005/SP2 provided a new variable length storage format decimal and numeric. Please refer to the...

In the previous blog I had listed all bulk import optimizations. Now, I will describe each of these optimizations in detail in separate blog entries. Let me first start with minimal logging. By the way minimal logging is also called bulk-logging but the...

Hey folks, This is my last blog post on the SQL Server Storage Engine blog as I've made the decision to leave Microsoft and join Kimberly running her company, SQLskills.com. This lets me indulge my passion for teaching and consulting full-time and...

How to use OPENROWSET to preprocess data before importing...

This post describes the enhanced diagnostics for deadlocks in SQL2005 and how to minimize them...

Hmmm - I sat for 5 minutes thinking of something amusing to say to start this one off and nothing came to mind, so I'm afraid this will be a humor-free post. Maybe I'm jet-lagged from being on the East coast all last week. As with all things related...

There has already been a great deal of excitement over the release of SQLIOSim, and I know everyone is hungry for more details. First the final cut of SQLIOSim is available for download at the Microsoft Download center. http://download.microsoft...

For those who have been around databases for any length of time, the idea of putting a database that you care about from either a reliability or performance perspective on an (SMB – Server Message Block) file share seems like a crazy idea, but recent...

Here's an interesting scenario that cropped up today. You have a database on a RAID array that failed and has zero'd out a page. How can you get the data back? There are two ways to do it, depending on the database recovery model and version of SQL...

So I learned something about SQL Server at TechEd today. In Kimberly and Bob Beauchemin's pre-con, Kimberly mentioned that SQL Server has a 'black-box' trace, similar to an aircraft flight-recorder, which I'd never heard of. It's an internal trace that...

I've been asked several times over the last few weeks for an example corrupt database to play with, and for testing logic built around DBCC CHECKDB. The attached WinZip file contains a backup of a simple 2005 database called 'broken' (I can do a 2000...

As announced in Tech-Ed 2007, data compression is a new and exciting feature targeted to be available in SQL Server 2008. This is a huge topic to be covered in one BLOG post, I have decided to break it into a series of posts, each building on the previous...

Ok, now that we have sort of agreed ( http://blogs.msdn.com/sqlserverstorageengine/archive/2007/09/30/data-compression-why-do-we-need-it.aspx ) that data compression is a good thing, you may wonder how SQL Server compresses the data, what does this compression...

One evening last week I sat down with Kimberly for 5 minutes to come up with a top-of-our heads list of VLDB maintenance concerns for a company migrating a multi-TB database to SQL Server 2005. This isn't in any way based on the VLDB survey I've been...