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, [...]
Paul S. Randal
In Recovery...
Quickie on programming equality tests
Posted on: March 27, 2009 1:10 pm
Photos: amazing wall paintings from the Imperial Palace in Bangkok
Posted on: March 27, 2009 10:25 am
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
Posted on: March 27, 2009 9:58 am
(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?!?
Posted on: March 27, 2009 7:28 am
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?
Posted on: March 25, 2009 9:02 am
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
Posted on: March 25, 2009 8:13 am
(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 [...]
DBCC PINTABLE
Posted on: March 24, 2009 9:40 am
(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
Posted on: March 23, 2009 1:08 pm
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
Posted on: March 23, 2009 10:33 am
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
Posted on: March 22, 2009 8:04 am
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
Posted on: March 22, 2009 6:14 am
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
Posted on: March 21, 2009 5:20 pm
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
Posted on: March 21, 2009 3:50 pm
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
Posted on: March 19, 2009 10:44 am
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?
Posted on: March 17, 2009 4:19 pm
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
Posted on: March 15, 2009 8:19 pm
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
Posted on: March 15, 2009 1:24 pm
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
Posted on: March 13, 2009 6:06 pm
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
Posted on: March 13, 2009 5:17 pm
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
Posted on: March 13, 2009 10:20 am
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
Posted on: March 12, 2009 6:02 pm
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
Posted on: March 11, 2009 3:50 pm
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!
Posted on: March 11, 2009 3:24 pm
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
Posted on: March 11, 2009 2:12 pm
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
Posted on: March 11, 2009 12:57 pm
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
Posted on: March 10, 2009 5:02 pm
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
Posted on: March 10, 2009 4:49 pm
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
Posted on: March 10, 2009 1:10 pm
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
Posted on: March 10, 2009 11:51 am
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?
Posted on: March 8, 2009 5:28 pm
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?
Posted on: March 7, 2009 8:57 am
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
Posted on: March 5, 2009 5:54 pm
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
Posted on: March 5, 2009 1:32 pm
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’?
Posted on: March 5, 2009 12:02 pm
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
Posted on: March 4, 2009 6:38 pm
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
Posted on: March 4, 2009 1:56 pm
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
Posted on: March 3, 2009 4:54 pm
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
Posted on: March 3, 2009 12:59 pm
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
Posted on: March 2, 2009 4:41 pm
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?
Posted on: March 1, 2009 10:42 am
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 [...]
