5 Signs Your SQL Server Isn’t Wearing Pants

We see a lot of SQL Servers.

During our sales process, prospective customers run sp_Blitz on their SQL Server, and we talk through the results. I see a lot of horrifying stuff.

Over time, I’ve built up a pretty good sense of signs that a SQL Server is running around loose in the data center, juggling chainsaws, drinking Keystone Beer, not wearing any pants.

5. Some of the databases haven’t been backed up. sp_Blitz alerts you if there’s no backups in the last two weeks, which is bad. You would probably think if that’s the case, none of the databases have been backed up – but that’s almost never true. What usually happens is that someone set up backups once, picked the specific databases they wanted backed up, and then never looked at that list again. They added more databases, but they didn’t configure backups to match.

4. CPU schedulers and memory offline. There are two install ISOs for SQL Server Enterprise Edition with very confusing names (one just says Enterprise Edition, and the other one says Core-Based.) One of them only supports 20 physical cores. Way too often, I see SQL Servers where 1/4-1/2 of the CPU cores and memory simply aren’t even turned on.

My reaction to your lack of pants. Photo by Yu Tang, no relation to the clan.

3. Build of SQL Server with known corruption & security bugs. This bug and this bug were fixed years ago, and yet many, many database servers out there are still running known dangerous builds.

2. No CHECKDB, ever. sp_Blitz reports on the last time DBCC CHECKDB finished successfully. I’m constantly amazed that people have never even tried to run CHECKDB on any of their databases, period. At the same time, when I ask how much data they want to lose, the answer is, “None.”

1. Databases in full recovery with no transaction log backups. A popular myth (and I used to believe it too, once!) is that full backups clear out the transaction log. Folks put a database in full recovery model, do full backups, and think they’re fine because those two “full” terms match up. Meanwhile, their database is 50GB, and their log file has grown to 500GB.

When I see this stuff, I buckle the belt on my office chair, get ready for a bumpy ride, and ask the client not to turn on their webcam (or at least not point it down.)

Do us both a favor. Run sp_Blitz on your servers today, and put some pants on them.

Previous Post
From The Mailbag: DBCC CHECKDB And Read Only Databases
Next Post
We’re Changing Our Online Training – Here’s Why.

6 Comments. Leave new

  • Running databases off a pen drive, accidentally installing express edition.. pants everywhere (and I don’t mean trousers)

  • DoubleBarrellDarrell
    March 24, 2017 11:26 am

    OK Brent!
    I know the database is the important part of the story.
    Why don’t you want the webcam pointed down?
    Please restrain my thoughts.
    Thank You.

  • How about no max mem setting and 4 GB vm, everything spilling to disk so users just think sql server is slow technology?

    • Erik Darling
      March 24, 2017 9:49 pm

      If you’re only giving 4 gb of memory to a SQL VM I don’t think I’m laughing at max memory not being set.

      My phone has like 3 gb.

      • Lol, ya agreed but when the VM farm is short on memory and it’s a sandbox server…its not as silly as not doing backups on a production box is it? We had recent power outage where ups failed and found out none of our incrementals company wide were any good. Hmmm…backups that aren’t tested aren’t really backups are they?

        • Erik Darling
          March 25, 2017 9:36 am

          It’s apples and oranges, but…

          If you have stage 4 something horrible and then you have a massive stroke, well, you were screwed either way.

          Sounds like you’re on the ‘either way’ plan 😀


Leave a Reply

Your email address will not be published. Required fields are marked *

Fill out this field
Fill out this field
Please enter a valid email address.