June, 2006

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

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

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

Apologies for the lack of a proper image in the previous post - as several people have pointed out. I only just realized that c&p-ing an image into the blog post only works if the image has a URL that's accessible outside MS.

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

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

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

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

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

I'm sure you've been there - something causes corruption in the database. You blame the hardware, the hardware guys blame the software. There's no smoking gun and the hardware diagnostics come back clean. What can you do? SQLIOStress This is the...

If you're interested in a little background, I did an email interview with Steve Jones of sqlservercentral.com over the last few weeks. Check it out at http://www.sqlservercentral.com/columnists/sjones/spotlightonsqlserverwithpaulrandall.asp

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

Here is the summary and the link to the white papers. Working with TempDB This white paper describes how SQL Server 2005 uses tempdb . Many improvements in SQL Server 2005 optimize tempdb usage and make it easier to manage and to troubleshoot. A...

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

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

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

I just saw this on one of the forums - it's another wonderful piece of advice to studiously avoid. Scenario: Drive goes bad and causes a torn page error. User wants to fix the problems and move the database to a different drive. The advice given...

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

I was asked this several times at TechEd so I'd like to answer it publicly. It's kind of complicated to explain so bear with me as I do it. Basically, NO! Earlier versions of SQL Server (for example, 6.5) had their problems but from 7.0 the storage...

TechEd's done and dusted for another year, and what a blast we had! It's incredible to think of the number of customers who stopped by the SQL Server DAT section of the Technical Learning Center to pepper us with questions, and even more so to think...

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

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

So, I'd like to make a couple of assertions about backup and disaster recovery that will hopefully get you to thinking, and just might change the way you think about the topic. There are two distinct motivations for doing backups, and they imply differing...

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

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