June, 2006

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

Ever see something like this in your SQL Errorlog? SQL Server has encountered 1 occurrence(s) of I/O requests taking longer than 15 seconds to complete on file [T:\MSSQL\DATA\tempdb.mdf] in database [tempdb] (2). The OS file handle is 0x00000838. The...

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

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

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

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

(I'm sitting here in Seattle airport at 7am on Sunday waiting to catch the same flight to Boston that I caught two weeks ago. Instead of TechEd, this time I'm going to a training course at MIT. I'd enjoy the air travel a lot more with a bigger gap in...

I've reached a point in my various ramblings where I need to do some glossary work. First up is an explanation of IAM chains. This will be in two parts, detailing how they're used in SQL Server 2000 and then in 2005. (Probably tomorrow as the sun's shining...

In my chalk/talk at TechEd yesterday, I asked the crowd "what's the purpose of repair?" One person got it right. The purpose is not to save data. Surprised? The purpose is to get the database back to a structurally and transactionally consistent state...

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

(Ok - another flight - another blog post. This time its Boston back to Seattle. The three of us who'd come over for the training course upgraded to 1st Class on Alaska for the flight back (great value at $100 for a 6-hour flight) and so there's oodles...

This is a question that's come up several times at TechEd already and is a nice second part to the post I made late last night about shrink. The answer is usually a most emphatic 'no, its far better to manage the growth of your data files manually'. ...

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

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

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

Having multiple users each owning various objects is commonplace in the database world. When one user wants to give access of their object to another user -- that’s when administration of databases gets rather interesting. Consider the following...

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

With the release of SQL Server 2005 comes a plethora of new security related features. Over time we will cover these in detail. To start, let's look at the world of encryption. When it comes to protecting data we can take a mile high view of this problem...

Have you ever seen an error like: Process 51:0 (dbc) UMS Context 0x018DA930 appears to be non-yielding on Scheduler 0. Or in SQL Server 2005: Process 51:0:0 (0xdbc) Worker 0x036BA0E8 appears to be non-yielding on Scheduler 1. Thread creation...

I've had some questions sent in comments and I wanted to reply to some of them using a post so others who aren't subscribed to the comment sections can see the answers. Wesley Backelant said: I was wondering what exactly you...

Sounds kinda funny, doesn't it? I mean, who here doesn't have someone breathing down your neck every time you go past your backup window? So why would you NOT optimize for fast backups? It just makes sense. You do backups every day, so they should be...

Defines couple of ways to load UNIX data file...