Troubleshooting

Early Warnings of a Meltdown (and How to Catch Them)

Most SQL Server crashes don’t come out of nowhere.
They leave breadcrumbs – red flags that something’s not right. The problem? If you don’t know where to look, you miss the signs…until it’s 2am and your CEO’s calling.

Let’s talk about how to listen for those whispers before they turn into full-blown alarms.

The Canary in the Query Mine

Here are a few subtle warning signs I see often before a server falls over:

• Backup jobs start taking longer for no obvious reason.
• TempDB grows larger than usual
• Login failures spike
• High CPU or long-running queries suddenly become “normal.”
• End users QUIT complaining…because they’ve gotten used to the problem.
• DBCC CHECKDB gets skipped because “it blocks things”

If any of these ring a bell, your SQL Server might be trying to tell you something.

Logs You Shouldn’t Ignore

Quick reality check: Most people never check the logs until the server is on fire, or after

• SQL Server Errorlog:
  • Corruption messages (Severity 24 and 25).
    • Run this: Select * from msdb.dbo.suspect_pages
  • Failed login attacks (possible brute force or misconfigurations).
    • Some of these might be from vulnerability testing tools and can be ignored. Might.
  • IO warnings that your storage is too slow or unreliable.
  • How to find the SQL Server ErrorLog on disk
• Agent job failures that are just quietly failing in the background.
• Windows Event logs
• Cluster logs

Pro tip: Set a calendar reminder to review logs once a week. Seriously—it takes 5 minutes and could save your bacon.

DIY Monitoring: No Budget? No Excuse.

You don’t need fancy tools to catch problems early. Here are a few dead-simple tricks:

• SQL Agent job that checks disk space and emails you when it drops below 10%.
• Query to track the longest-running queries over time—schedule it to run daily.
• Custom SQL alerts for CPU over 90% for more than X minutes.

Set Up Built-In SQL Alerts (Takes 5 Minutes)

SQL Server has a native Alerting feature. No third-party tools required.

Here’s how to set up critical alerts for things you never want to miss:

--Step 1:

EXEC msdb.dbo.sp_add_operator 
    @name = N'DBA_Alerts', 
    @enabled = 1, 
    @email_address = N'[email protected]';
-- This requires Database Mail and an SMTP server

--Step 2
-- Severity 19-25 (from “Something’s wrong” to “We’re toast”)
-- Change and run for each severity you want to track. Sev 20 may give false positives

EXEC msdb.dbo.sp_add_alert 
    @name = N'Severity 19', 
    @message_id = 0, 
    @severity = 19, 
    @notification_message = N'Severity 19 error occurred', 
    @job_id = NULL, 
    @enabled = 1, 
    @delay_between_responses = 300, 
    @include_event_description_in = 1, 
    @database_name = N'master', 
    @operator_name = N'DBA_Alerts', 
    @notification_method = 1;

Repeat this for severity levels 19 to 25, or use a script to loop through them.
Bonus: Create alerts for SQL error numbers like:

• 823, 824, 825 – signs of potential data corruption
• 9002 – log file full

The Bottom Line

SQL Server doesn’t generally go down without a fight. But it does give off signals.
Learn to listen, and you’ll dramatically reduce those “why is the website down?”.

Database Emergencies Cost Thousands Per Minute 

When SQL Server fails, you need expert help fast.
For a limited time: First month of Pocket DBA® FREE with a 6-month plan.

• Senior DBAs available 24/7 • 1-hour SLA • Proactive problem prevention

Don’t wait for a disaster. Be prepared.
CLAIM FREE MONTH
Offer ends June 1st. Limited availability.

Thanks for reading!

–Kevin