Quickie on programming equality tests

Just noticed this come up on my Facebook news feed – how to code assignment statements when programming (not in T-SQL): if (foo == 4) then … if (4 == foo) then … #1 is the most natural way to write the conditional expression, but has a *massive* potential for introducing bugs that are very, […]

Photos: amazing wall paintings from the Imperial Palace in Bangkok

In February this year we stopped off in Bangkok on the way to and from teaching in Hyderabad. On one of the days we did some sight-seeing, including the Imperial Palace in Bangkok. I took a bunch of photos of the wall paintings in the outer courtyard and I've posted an album up on Facebook (easier than a big, […]

Weekly survey: large-value character storage

(And it's official – this is blog post 39 this month, making this my most prolific month yet for blogging. An arguably dubious achievement…) In this week's survey, I'm interested in your views on the best way to store large-value character data. I'll report on the results next week (around 4/3/09). Thanks!

Index rebuilds depend on stats, which are updated by index rebuilds?!?

Jack Li, one of the Senior Escalation Engineers in Product Support, just posted details of an interesting case over on the CSS blog - his article is here. It talks about index builds and rebuilds, but the issue is the same for both, so I'll just talk about rebuilds. The jist of the problem is that […]

Is running repair on msdb safe?

Just saw this on a forum – running REPAIR_ALLOW_DATA_LOSS on msdb to cope with corruption. Yes, this will work but it's certainly not safe and something I'd only recommend as a last resort. Here's why: msdb stores your backup history and your Agent jobs. REPAIR_ALLOW_DATA_LOSS works by usually "delete what's broken and fix up all […]

Msg 602, Level 21, State 50, Line 1

(Yes, Kimberly's lecturing again…) Here's something that I've seen crop up a lot recently on corruption forums: Server: Msg 602, Level 21, State 50, Line 1 Could not find row in sysindexes for database ID 10, object ID 1, index ID 1. Run DBCC CHECKTABLE on sysindexes. This isn't corruption - it comes from trying to […]


(Continuing my habit of blogging while Kimberly's presenting – at least I'm not on stage this time…) In early versions of SQL Server, it was sometimes necessary to 'pin' the pages from a heavily used table in the buffer pool to help performance. In later versions, the buffer pool became very sophisticated and does an […]

New Microsoft Regional Director – Paul Randal

I'm very pleased, and deeply honored, to announce that I've been made a Microsoft Regional Director. This is one of a very small group of people (about 120 worldwide) who Microsoft sees as influencers of, and liaisons between, the Microsoft community at large. Apart from me, the other SQL MVPs who are also RDs are […]

Measuring ‘churn’ in a SharePoint content database using SQL Server

After teaching some of the MCM-SharePoint class last week, one of the attendees pointed me at a blog post about measuring churn in SharePoint databases. The poster gave code to measure how large full backups are, which really only measures how much data there is in the database, not whether existing data has changed. If […]

How to change a default constraint

While I was teaching the MCM-Database class last week, we were discussing fragmentation and the effect of a high-order GUID key on an index. Without going into too many details, having a random GUID – as generated from the NEWID() - function is bad, but having one generated by NEWSEQUENTIALID() isn't anyway near so bad (I'll […]

IAM page corruption examples

A thread cropped up on SQLServerCentral involving IAM chain corruption (see Inside the Storage Engine: IAM pages, IAM chains, and allocation units for details of IAM chains). The error from DBCC CHECKDB was: Server: Msg 2576, Level 16, State 1, Line 1 IAM page (0:0) is pointed to by the previous pointer of IAM page (1:394336) […]

Weekly survey: method of running consistency checks

In this week's survey, I'd like to know *how* you run consistency checks, not how often. I'll report on the results next week (around 3/27/09) Thanks!

Importance of index maintenance

Last week’s survey was on what kind of regular index maintenance you perform (see here for the survey) as a way of kicking off a new series I’m writing around index maintenance. Here are the results as of 3/21/09 – I find them very encouraging: As you can see, about 2/5 of respondents are performing some form […]

Ghost cleanup redux

Way back at the start of me blogging here I wrote a comprehensive description of ghost records and the ghost cleanup process – see Inside the Storage Engine: Ghost cleanup in depth. A question came up in the class I'm teaching this week that's worth answering in a blog post – do ghost records occur […]

FILESTREAM directory structure – where do the GUIDs come from?

I'm teaching the Microsoft Certified Masters – Database qualification this week here in Redmond, and in part of day one I discuss the FILESTREAM directory structure. I was asked the question where do the directory name GUIDs come from? so I started digging around in the system tables while Kimberly was lecturing. Take a look […]

Things you know now

There's a long-running discussion with people tagging each other to post advice for people new to SQL Server, about what they know now and wished they'd known ealier in their lives/careers – lot's of SQL MVPs and other luminaries have been doing it and I've been tagged now by my good friend Ward Pond – […]

SQL Connections Fall 2009: Call for Abstracts

Yes, it's that time again (well a little bit earlier than usual). With the Spring SQL Server Connections show coming up next week, its time to start planning for the Fall 2009 show. Once again Kimberly and I will be Co-Chairs of the Conference and we'd like to invite you to submit abstracts for the […]

Weekly survey: index maintenance plan

I'm about to start a new series of post about index fragmentation and removing it. For this week's survey, I'd like to know what index maintenance you do to address fragmentation (in any of its forms) – I'll report on it in a week. Thanks

Importance of validating backups

Last week I kicked off the first weekly survey – on whether you validate your backups or not (see here for the survey). The results are very interesting (as of 3/13/09): As you can see, almost 25% of respondents never validate their backups! And a further 25% only validate them occasionally, with 30% doing some […]

Great script for automating database maintenance

Ola Hallengren, who we meet every so often at SQL Connections, has a great script that helps automate consistency checking, backups, and index maintenance. He's constantly updating it and it's good quality code. Check out the latest version for  SQL Server 2005 and 2008: Release notes Documentation Script Enjoy! 

Facebook, or how to lose productivity forever

Ok – so Kimberly got on Facebook last weekend after peer-pressure from a bunch of people at Katie's 9th birthday party. I resisted, in vain it seems, knowing how devastating it would be to our productivity, given that we're both totally OCD. Now I joined (again) this afternoon and the prophecy came true. So unfortunately […]

FILESTREAM directory structure

After writing the FILESTREAM whitepaper for Microsoft, I've had lots of questions about the structure of the FILESTREAM data container. The FILESTREAM data container is the technical term for the NTFS directory structure where all the FILESTREAM data is stored. When you want to use FILESTREAM data, you first add a filegroup (during or after […]

Our book has been published!

  Microsoft SQL Server 2008 Internals finally starts to roll off the presses at Microsoft Press today! For me this is the first time in print (in book form at least) and my consistency checking chapter (about 1/10th of the book) represents a complete brain-dump of everything I know about how DBCC CHECKDB works. Kimberly […]

How to scan a network looking for SQL instances

I came across a good post over at the Data Management section of Less Than Dot, describing how to scan through a network looking for unsecure SQL instances. It makes use of the free SQL Ping tool and describes automating the process using SSIS. This is pretty interesting to me as I've just finished writing […]

TechNet Magazine: April 2009 SQL Q&A column

The April edition of TechNet Magazine is available on the web now and has the latest installment of my regular SQL Q&A column. This month's topics are: Disappearing errors with DBCC CHECKDB Provisioning tempdb when moving from 2000 to 2008 Does fillfactor prevent fragmentation and should it be set instance-wide Avoiding FILESTREAM performance problems Check […]

Microsoft Certified Master – Database AND Sharepoint

As you may know, Kimberly and I teach (and wrote) the first week of the 3-week Microsoft Certified Master – Database qualification. From next week onwards, we'll each also be teaching a day of the Microsoft Certified Master – Sharepoint certification too, as SQL experts. As such, we'll be learning a lot about how SQL […]

Couple of LOB corruption bugs fixed in the latest CUs for 2005

Both 2005 SP3 CU2 and 2005 SP2 CU12 contain fixes for two bugs that cause corruption in LOB data. The first bug may happen when a column type is converted to varbinary(max), change the large-value-types-out-of-row option to true, insert a row, and then try to update it – described in KB 961648. The second bug […]

CHECKDB From Every Angle: SQL and Windows bugs around database snapshots

As you may know, DBCC CHECKDB (and some of the other DBCC CHECK*) commands use an internal database snapshot to get a transactionally consistent view of the database (if you didn't, see my blog post CHECKDB From Every Angle: Complete description of all CHECKDB stages for an explanation). It's entirely possible that the database snapshot may […]

Misconceptions around instant file initialization

Whenever I teach a class I’m amazed at the number of DBAs who don’t know about instant file initialization. Without wanting to redo blog posts that others have done, in a nutshell it allows a file to be create or grown ‘instantly’ by not having to zero the space in the file. This is especially […]

What’s a valid question for a DBA interview?

There's been an interesting discussion on SQLServerCentral about whether this question is valid for a DBA interview: what's the name of the executable that runs SQL Server? My view is that it's a perfectly valid question, based on the cliched premise that the more you know, the further you go. I've conducted hundreds of interviews […]

How can a log backup fail but a full backup succeed?

This is an interesting case that cropped up yesterday – the transaction log is damaged so a log backup doesn't work (with the error below): Backup detected log corruption in database FakeDBName. Context is FirstSector. LogFile: 2 'F:\SQLLOGS\XYZ\FakeDBName_Log.ldf' VLF SeqNo: x502e VLFBase: x2ce40000 LogBlockOffset: x2d0a9000 SectorStatus: 2 LogBlock.StartLsn.SeqNo: x4ee3 LogBlock.StartLsn.Blk: x1348 Size: x200 PrevSize: x400 […]

Testing a new survey method: backup validation

I'm toying with the idea of having a weekly survey that'll highlight an interesting facet of database management. I've signed up with SurveyPopups.com, which is free and allows you to see the results as you vote on them. If you think this is a cool idea, vote in the survey using the options below. If […]

Database settings survey

We're doing some research into database settings that people use and the best way to get a good variety of systems is to ask you to send us some data. We're interested in whether people are using the defaults or changing them in any way. If your're interested, run this query (in the context of […]

Is SQL Server ‘just plumbing’?

One of the opinions that was expressed yesterday by our developer friends (a bunch of Regional Directors – and look, Kimberly's the featured RD today on http://theregion.com/) is that SQL Server is just plumbing. Consider the developers (or app architects) as plumbers. They see SQL Server as a few pieces of copper pipe joining a […]

Mandatory SQL breathalyzer test

Today we've spent a lot of the day in discussions with some folks about developers vs. DBAs, and how it's often the case that the two don't work together. Developers need to know the effect of their design choices on the database, and DBAs need to educate the developers. There should be a close working […]

Photos from India

In January I finally got to visit India for the first time. I've always wanted to go as my great-grandfather was in charge (I believe) of the British Royal Artillery armoury at Dum Dum, West Bengal (where the dum-dum bullet was invented) at the end of 19th century. This was Kimberly's 4th trip, but the first that […]

Common questions from newsgroups and forums

If you're new to the blog then you may not have seen my Search Engine Q&A series (or seen it and not realized what it is). It started out that I was watching the incoming search engine queries that hit the blog and worked out what common questions people were searching for, but then I […]

Website/blog problems

As you may have been seeing over the last few weeks, there are intermittent errors in some parts of mine and Kimberly's blogs. Our hosting site and our blog guy have figured out that there's an ASP version mismatch which is causing the problems – and they're going to fix it this week. So – […]

New whitepaper on SQL Server Audit in 2008

One set of features i haven't blogged about yet in SQL Server 2008 are the new security features: SQL Server Audit, Transparent Data Encryption, and Extensible Key Management. I've just finished writing a security article for the May 2009 TechNet Magazine (the annual security issue) and while trolling around TechNet I found that the security […]

Search Engine Q&A #27: How does the storage engine find variable-length columns?

This question came up in a class I was teaching a few weeks ago: given that the variable-length column offset array in a record only stores offsets, how does the storage engine find a particular variable-length column? The question comes because there's nothing in the record to say which variable-length column is which – so […]