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...

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...

Yes, finally I come clean and tell all. It's an open secret that there's an undocumented DBCC command called DBCC PAGE that you can use to look at the contents of database pages. I've recommended in forum postings that people use it and Product Support...

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...

This week's topic is data file shrinking. I've seen lots of mis-information in the last few weeks and I've had a bunch of questions about it. First up is auto-shrink.
In my opinion, this feature causes way more problems than it solves (in fact, I can...

(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...

I had a question this week from someone who'd heard me say at SQL Connections (paraphrasing) "database compatibility level is mostly about query parsing" and was having trouble trying to forcibly attach a 2005 or 7.0 database to a 2000 server.
His...

FILESTREAM is disabled by default in SQL2008. Before you can start to use FILESTREAM, you must enable FILESTREAM on the instance of the SQL Server Database Engine. Enabling/configuring FILESTREAM is a bit different from configuring other SQL features...

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...

(Been a week or so since the last post but I haven't burnt out with blogging yet - I was on vacation over the July 4th weekend and totally offline in and around a small town called Pullman in south-eastern Washington.)
In a previous post I described...

Whenever I’m discussing index maintenance, and specifically fragmentation, I always make a point of saying ‘Make sure the index is being used before doing anything about fragmentation’ .
If an index isn’t being used very much, but has very low page...

Emergency mode repair? In a couple of previous posts I explained how the two worst things you could do to your database are rebuilding your transaction log and running REPAIR_ALLOW_DATA_LOSS . Well, in SQL Server 2005, we combined them into a new documented...

10 points if you answered "don't be daft, never! " and minus several million is you answered anything else. Yes, if you have no backups and your hardware has corrupted your transaction log then you have no choice but to rebuild it, but this should never...

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...

Page checksum is new feature in SQL2005 that provides you a stronger mechanism than torn-page to detect any corruptions in IO path. Here are some details and scenarios
PAGE CHECKSUM:
Between the time a database page is written to the disk...

One of the key challenges in TempDB is that it is a common resource for all applications running on an instance and any misbehaving application or rouge user command can take up all the space in TempDB bringing down other applications with it. In my discussions...

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...

.. this is an old one, but its poorly understood and it just surfaced again today so I thought I'd share it.
Situation: customer keeps his indexes nicely defragemented during the day so that his range scan queries perform well. Every morning, he comes...

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...

This is a question I see every so often and it cropped up again this morning so I'll use it as the subject for this week's blog post.
There are several ways I could answer this:
the unhelpful answer - I've got no idea.
the almost-helpful answer...

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...