I bet you'd love the answer to be "SQLskills.com"? Well now it can be. Over the last few months some of our clients for whom we've performed SQL Server health checks on their critical servers have asked us if we'd consider a regular service where we perform mini-health checks on those same servers and also [...]
Paul S. Randal
In Recovery...
Who is watching over your precious SQL Servers while you’re busy?
Posted on: September 28, 2011 8:37 am
Tempdb configuration survey results and advice
Posted on: March 9, 2011 10:42 am
A few weeks ago I kicked off a survey about tempdb configuration – see here for the survey. I received results for more than 600 systems! Here they are: These are very interesting results, for several reasons: It shows the relative distribution of core-count for SQL Servers, with a pronounced shift to 8+ [...]
My name is Kyle, and I’ve got SQLskills
Posted on: February 10, 2011 6:10 am
It's all very well hearing about how cool our training classes our from *us*, but what do attendees like you think? I recently did an email interview with Kyle Hayes, a member of the Production DBA Team for Bridgepoint Education. He attended our Immersion Event back in August 2010 in Bellevue, WA. Here's what Kyle [...]
PASS Performance VC recording and slides on index fragmentation available
Posted on: January 19, 2011 9:19 am
Yesterday I presented my lecture Index Fragmentation: The Hidden Menace for the PASS Performance Virtual Chapter. The recording of the session, plus a PDF of my slides, and the demo scripts are now available from the PASS website. Check it out here (look for January 18, 2011). Enjoy!
T-SQL Tuesday #012 : Summary of why DBA skills are necessary
Posted on: November 10, 2010 1:14 pm
Last Tuesday I hosted T-SQL Tuesday #012 (see here for the call for participation) and posed the question "why are DBA skills necessary?" This month broke the participation record with 46 people contributing posts – fabulous! Lots of people contributed for the first time too. And what I think is also really cool is that 45 [...]
TechNet Magazine Article: Top-10 Secrets of a SQL Server Expert
Posted on: November 1, 2010 7:36 pm
The November 2010 edition of TechNet Magazine is now available on the web and contains my latest feature article. In this article I give my top-10 secrets for success as a DBA, covering: Take inventory Standardize configurations Understand the I/O susbsystem Create a customized maintenance plan Ensure the security of your system Get on good terms with [...]
Paul and Kimberly interview from MVP Summit
Posted on: April 29, 2010 12:45 pm
For about 6 months we'd bee trying to hook up online with SharePoint MVP Hilton Geisenow for an interview but unfortunately the bandwidth to South Africa is pretty limited so we decided to wait until the recent MVP Summit in Redmond, WA. We managed to hook up and record an interview for Hilton and he's just published [...]
Important things for DBAs to consider
Posted on: April 12, 2010 4:31 pm
(Look in the Misconceptions blog category for the rest of the month’s posts and check out the 60-page PDF with all the myths and misconceptions blog posts collected together when you join our Insider list, plus my online Myths and Misconceptions training course on Pluralsight.) Over the last month or so I've found myself handing [...]
In the news: our maintenance audits get rave reviews
Posted on: March 9, 2010 8:23 am
Back in January I offered a promotion as a way of introducing our maintenance/operations auditing services. Now I've completed a bunch of them, with some excellent results for customers. One international customer, Plex Systems, was so pleased with the outcome of my audit of their manufacturing ERP software hosting databases that they issued a press release today [...]
TechNet Magazine: feature article on recovering from disasters using backups
Posted on: November 5, 2009 8:36 am
The November 2009 edition of TechNet Magazine is up on the web and contains my latest feature article, the second in a 3-part series on backups/restores/repairs. In this article I explain all about using the RESTORE command. Not much point having backups if you don't know how to use them! Topics include: The four phases [...]
New script: is that database REALLY in the FULL recovery mode?
Posted on: October 7, 2009 1:27 pm
One of the perennial problems facing both experienced and involuntary DBAs is how to tell whether a database is really in the FULL recovery model or not? This is complicated by the fact that when you switch a database into the FULL recovery mode, it actually behaves as if it's in the SIMPLE recovery mode [...]
SQL Server 2008 High Availability whitepaper published on MSDN
Posted on: September 28, 2009 12:42 pm
The 35-page whitepaper on high availability I wrote for the SQL team over the summer has been published on MSDN. It's a 2-300 level whitepaper that describes the various high-availability technologies in SQL Server 2008 and how they can be used to mitigate disasters. It's chock-full of links to other whitepapers, technical articles and Books [...]
Survey: how do you create and maintain statistics
Posted on: September 15, 2009 12:10 pm
This survey is based on a suggestion from Jonathan Kehayias (blog/Twitter). In this survey I'm asking two questions: When you're designing/implementing a database, do you manually create statistics or let SQL Server create them automatically? How do you maintain statistics? Manually or letting SQL Server do it for you? Or both? I'll probably collaborate with Kimberly on [...]
Importance of having a good disaster recovery plan
Posted on: September 15, 2009 11:17 am
A few weeks ago I kicked off a survey asking what kind of disaster recovery guide/run-book/plan (I'll just call it a plan from now on) you have (see here for the survey). Here are the results as of 9/15/09: Out of all these answers, IMHO the last answer is the only acceptable one for [...]
Survey: what kind of disaster recovery guide do you have?
Posted on: August 26, 2009 1:50 pm
In this survey I'd like to find out what kind of disaster recovery guide/run-book/plan you have, if any. When a disaster occurs, do you just wing-it, or is there something written-down that was worked out in advance to help you along the way. I'll report on the results in a couple of weeks. *Please* no comments [...]
Importance of running regular consistency checks
Posted on: August 3, 2009 7:19 am
A long time ago, in a blog post far, far away (well before I went offline in July) I kicked off a weekly survey about how often you run consistency checks (see here for the survey). Now I'm back online again, and so here are the results as of 8/3/09. The results are actually [...]
Weekly survey: how often do you run consistency checks?
Posted on: June 24, 2009 7:00 pm
In this week's survey I'd like to know how often you run consistency checks on your *most critical* production database, regardless of *how* you run them (we did that survery already – see Importance of how you run consistency checks). I'll report on the results around July 4th. I'd only like you to answer for [...]
Importance of data file size management
Posted on: June 24, 2009 6:55 pm
In last week's survey I asked how you manage the size of your database *data* files – see here for the survey. Here are the results as of 6/24/09. The 'other' values are as follows: 5 x 'manual file growths and a custom mom alter to when the datafiles are 98% full. autogrow set to [...]
Why you should not shrink your data files
Posted on: June 24, 2009 10:02 am
(Be sure to join our community to get our bi-weekly newsletter with exclusive content, demo videos, and other SQL Server goodies! Also check out our online training courses.) One of my biggest hot-buttons is around shrinking data files. Although I used to own the shrink code while I was at Microsoft, I never had a [...]
Weekly survey: how do you manage your data file sizes?
Posted on: June 15, 2009 7:22 pm
In this week's survey I'd like to know how you manage the sizes of your database *data* files (remember we've already done log file size management). I'll report on the results around 6/21/09. I say this every week in the PS, but I'm moving it up here because I don't like having to delete comments [...]
Becoming an involuntary DBA – you’re not alone
Posted on: June 15, 2009 5:35 pm
In last week's survey I asked you what you think is the hardest thing when becoming an involuntary DBA – see here for the survey. Here are the results as of 6/15/09. The 'other' responses were: 3 x 'Learning to tell good advice from bad advice' 1 x 'Learning to know *that* you don't know' [...]
Weekly Survey: what’s the hardest part of becoming an involuntary DBA?
Posted on: June 7, 2009 9:35 pm
This week's survey is all about being a DBA or involuntary DBA – what do you think is the hardest thing when becoming an involuntary DBA? I'll report on the results sometime over the weekend of 6/13/09. As always, a big Thanks! for contributing to the blog by responding. Please shoot me an email (Contact [...]
Backup monitoring and reporting
Posted on: June 4, 2009 10:20 am
Chad Miller over on SQLServerCentral has put together a really great collection of scripts that allow you to easily monitor and report on what backups are happening on your system. These should be especially useful for involuntary DBAs, rather than having to dig into the backup history tables themselves. I'll be including a link to [...]
Importance of defining and measuring SLAs
Posted on: May 30, 2009 4:00 pm
In last week's survey I asked you two things, as a precursor to a whitepaper I'm writing for Microsoft The first question was what is your maximum allowable downtime SLA (either for 24×7 operation or not). See here for the survey. Here are the results as of 5/30/09. The Other values were all about not having [...]
Weekly survey: maximum allowable downtime – target and actual
Posted on: May 25, 2009 1:58 pm
This week's survey is inspired by this morning's Kimberly+Paul hot-tub conversation around data-dependent routing vs. network load balancing, which then turned to SLAs. Yes, we lead an exciting life . This survey is a *four*-parter. Part 1 is for each of your databases, which survey option is closest to your target maximum allowable downtime SLA (Service-Level [...]
Importance of testing your disaster recovery plan
Posted on: May 25, 2009 1:00 pm
In last week’s survey I asked whether you’re ever tested your disaster recovery plan, and if so, what happened? (See here for the survey). Here are the results as of 5/25/09: The ‘other’ responses are: 2 x “restored to test env regularly. don’t know if sla would be met.” 2 x “test it regularly, most [...]
Weekly survey: have you ever tested your disaster recovery plan?
Posted on: May 17, 2009 5:38 pm
This week's survey is inspired from many stories I saw on the forums and Twitter this week – mostly bad, one good (someone I'm following is spending the weekend testing their disaster recovery plan – cool!). I'd like to know whether you're ever tested your disaster recovery plan, and if so, what happened? I'll report [...]
Clustered or nonclustered index on a random GUID?
Posted on: May 14, 2009 9:34 am
Quickie this morning to start the day off. I saw a question on a forum: if I *have* to use a GUID and *must* have a primary key, should I make the primary key clustered or nonclustered? Now, I'm not getting into the whole GUID vs. bigint identifier, or random GUID vs. GUID generated by [...]
A sad tale of mis-steps and corruption (from today)
Posted on: May 12, 2009 8:36 pm
This is a true story, and unfolded over the last few days. It's deliberately written this way, I'm not trying to be patronizing – just illustrating the mistakes people can make if they don't know what not to do. Once upon a time (well, a few days ago), there was a security person who had access [...]
How to get all the corruption messages from CHECKDB
Posted on: May 12, 2009 4:56 pm
Hopefully all of you reading my blog already know to use the WITH ALL_ERRORMSGS option whenever you run DBCC CHECKDB (which is now the default in 2005 SP3, haven't checked if it made it into 2008 SP1). If you're doing some quick analysis of the results to see, for example, whether it's just nonclustered indexes [...]
Importance of having a manageable environment
Posted on: May 10, 2009 11:22 am
Last week's survey was two-fold – what's the largest SQL Server database you manage, and how many SQL Server databases are you responsible for managing. Here are the results as of 5/10/2009. As far as the database sizes are concerned, the distribution curve isn't surprising, but the average size of databases *is*. 70% of [...]
Misconceptions around corruptions: can they disappear?
Posted on: May 5, 2009 8:30 am
This has come up a few times now, most recently in an email question this morning - subsequent runs of DBCC CHECKDB show varying numbers of corruptions, and sometimes no corruptions – what's going on? Even more strange – a maintenance job runs a DBCC CHECKDB, which shows errors, but then in the morning – no [...]
Do transactions rollback when DBCC CHECKDB runs?
Posted on: May 4, 2009 4:04 pm
Recently there’s been a spate of people noticing strange behavior from active transactions when DBCC CHECKDB (or any of the other DBCC consistency checking commands run). For example, I’ve create a database call DbccTest with a single table. In one connection I do: BEGIN TRAN INSERT INTO t1 VALUES (1, 1); GO And in another [...]
Weekly survey: what’s your largest database, and how many do you manage?
Posted on: May 2, 2009 7:58 am
Jason Massie posted an interesting statistic yesterday – Facebook has 1.5 petabytes of image storage, and it grows by 25TB daily – I wonder how they store and manage it? In this week's survey, I'm interested in two things: what's the largest single SQL Server database in your company and how many SQL Server databases [...]
TechNet Magazine: June 2009 SQL Q&A column
Posted on: May 1, 2009 8:08 am
The June 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: Combining index and statistics maintenance How some Enterprise-only features can prevent a database restoring on lower editions Why database mirroring can switch between SYNCHRONIZED and SYNCHRONIZING Use a [...]
SQL 2005 SP2 maintenance plan bug masking corruption
Posted on: April 15, 2009 11:54 pm
Not much to do here in Tokyo while waiting for the Bangkok flight except read and blog, and I've already done enough reading for one day on the flight here – so that leaves blogging! Here's an interesting case that I got involved in on SQLServerCentral (here's the original thread, linked here with permission of [...]
Importance of proper transaction log size management
Posted on: April 10, 2009 12:21 pm
Last week's survey was on how *you* manage the size of your transaction log (see here for the survey). Here are the results as of 4/10/2009 – the most popular survey yet: In my opinion (guarantee that I'm going to say stuff that some of you won't agree with!), there are only two valid answers in [...]
TechNet Magazine: feature article on Common SQL Server Security Issues and Solutions
Posted on: April 3, 2009 7:35 am
The May 2009 TechNet Magazine is now available online, and it's the annual security issue. In there is an article I wrote highlighting 10 common security issues (and solutions) you should worry about if you're not a security-savvy DBA. It covers: Physical security Network security Attach surface minimzation Service accounts Restricting use of administrator privileges [...]
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 [...]
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 [...]
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!
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 [...]
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 [...]
Easy monitoring of high-severity errors: create Agent alerts
Posted on: February 23, 2009 10:34 am
In my previous post today I talked about error 825 and how you should have an Agent alert set up to catch it when it happens. There are two ways you can set up Agent alerts – using SSMS or using the stored-proc sp_add_alert. In a post from April 2008 I showed how to create an [...]
A little-known sign of impending doom: error 825
Posted on: February 23, 2009 10:15 am
There are two pretty well-known I/O errors – 823, and 824 – but there's also one called 825 which most DBAs do*not* know about, and definitely should. From SQL Server 2005 onwards, if you ever see an 823 or 824, SQL Server has actually tried that I/O a total of 4 times before it finally [...]
Performance impact of lots of VLFs in the transaction log
Posted on: February 9, 2009 6:18 pm
One of the things I mentioned in my recent TechNet Magazine article on Understanding Logging and Recovery was the need to manage the transaction log so the number of VLFs (virtual log files) does not get too large (with too large being more than, say, 100 VLFs). Linchi Shea (a fellow MVP) has just posted [...]
TechNet Magazine: February 2009 SQL Q&A column
Posted on: January 16, 2009 10:15 am
OK – last content post today. I forgot that the February TechNet Magazine also has the latest edition of my regular SQL Q&A column. This month's column covers: Should backup compression be enabled at the instance level? Client redirection during database mirroring failovers Partition-level lock escalation in SQL Server 2008 Is it ever safe to [...]
TechNet Magazine: feature article on understanding logging and recovery
Posted on: January 16, 2009 10:01 am
Wow – today is all about new content. As if I haven't already blogged about enough stuff to keep you reading through next week, the February issue of TechNet Magazine is now available and contains a feature article I wrote about understanding how logging and recovery work inside SQL Server. The article covers: What is [...]
TechNet Radio interview on database corruption
Posted on: January 12, 2009 11:00 am
Well, we're back from vacation finally (only for 10 days and then off to India and Thailand for 3 weeks… phew) and I've got a bunch of blogs posts to catch up on. First up – I did an interview with TechNet Radio in mid-December where I talked about database corruption and things to do [...]
Conference Questions Pot-Pourri #10: Shrinking the database before taking a backup
Posted on: November 26, 2008 5:43 am
Here's a question I got from someone who attended our database maintenance workshop at PASS last week (paraphrased): I attended your pre-conference session on database maintenance and found it to be very informative. From what you told use though, I think I need to change my nightly backup procedure. I like to get my databases [...]
Follow-on from instant initialization privilege checking
Posted on: August 30, 2008 7:26 pm
I got bored on the first leg of the journey from Seattle to London so thought I’d bang out a quick blog post. After my previous post on checking whether a SQL instance is able to use instant initialization (see here), I had a discussion with Scott R., who regularly comments on blog articles. He [...]
Creating, detaching, re-attaching, and fixing a SUSPECT database
Posted on: August 29, 2008 7:55 pm
(Be sure to join our community to get our bi-weekly newsletter with exclusive content, demo videos, and other SQL Server goodies! Also check out our online training courses.) This is a post I’ve been trying to get to since I started blogging a couple of years ago: how to re-attach a detached SUSPECT database. This [...]
RunAs Radio interview on being an “involuntary DBA”
Posted on: August 28, 2008 6:28 pm
On Tuesday I recorded a couple of sessions with Richard and Greg on RunAs Radio. It’s been a while since either Kimberly or I have been on the show – last November at TechEd in Barcelona. In the first session we discussed what it is to be an “involuntary DBA” – someone who’s thrown into the [...]
TechNet Magazine: Effective Database Maintenance article and August SQL Q&A column
Posted on: July 8, 2008 5:35 pm
Well, we’re just back from vacation (photo blog post to follow) and I’ve heard that the feature article on Effective Database Maintenance I wrote for the August issue of TechNet Magazine is live on the web. It also includes a 5 minute long screencast I recorded where I demo the effect of database shrink on index [...]
