• Skip to main content
  • Skip to primary sidebar

DallasDBAs.com

Explaining SQL Server in plain english

  • Services
    • SQL Server Health Check
  • Pocket DBA®
  • Blog
    • Speaking
  • Testimonials
  • Contact
  • About

Emergency

IRL #8: SQL Server Down

September 6, 2023 by Kevin3NF Leave a Comment

Problem:

My PocketDBA® client called in with a non-responsive SQL Server. Application errors, timeout, alerts firing. SQL Server box is up, and the services are running.

Background:

This is a stand-alone bare metal server located at a hosting facility with some local storage as well as NAS storage.  SQL Server 2016 Standard Edition, 8 cores, 128GB RAM.  The primary database is nearing 1TB and is has multiple filegroups (Active, Archive, Indexes, etc.).   This database and apps that touch it ARE the company.

Investigation:

  • Attempts to Login failed – SSMS, Dedicated Admin Connection (taken), SQLCMD
  • Errorlog accessed directly from the file system showed:
    • Log Backup failures
    • Multiple Slow I/O requests: “SQL Server has encountered 112 occurrence(s) of I/O requests taking longer than 15 seconds to complete on file [H:\Company_LOG\Company_log.ldf] in database id 42”
    • Sp_whoisactive revealed a long running LOG backup reading from the H drive, writing to the N drive.
    • Windows Resource Monitor revealed Disk H Active Time (%) at 100, Disk Queue length – 0.
  • During this process the database went into suspect mode as it lost its connection to the .LDF completely. Verified the file was still in the expected location.
    • Cannot bring offline or online via any method (due to the above log backup)

 

Process:

  • KILL the Log Backup and disable the job. No change after 30 minutes for the KILL to complete, no additional traffic on this database.
  • Contact the hosting firm to open a critical support ticket to check the NAS.
  • Host confirmed cable settings, O/S settings, network settings and restarted the NAS.

 

Results:

  • After the NAS reboot, everything came up and appeared normal.
  • Brought the database online with no issues and a quick run through the Recovery process

 

Conclusions:

  • Always verify:
    • Services are running
    • Drives are available
    • Connections can be made
    • Sp_whoisactive – run it early in the process
  • Often times, SQL Server is a victim of something else.
  • NAS vs SAN…largely a budget issue but has performance implications
  • Make sure you have the phone numbers handy for anyone supporting your systems and an SLA in place.
  • High Availability is a really good idea. This was a 3 hour outage.

 

Other posts in the IRL – In Real Life series:
https://dallasdbas.com/category/sql/irl/

 

Filed Under: Emergency, IRL, SQL, Troubleshooting Tagged With: syndicated

Announcement: Pocket DBA™ service

February 5, 2019 by Kevin3NF Leave a Comment

Several months ago, we posted a blog about keeping a Database Administrator on retainer for emergencies, called “Put a DBA in Your Pocket“

We cross-posted a version over to LinkedIn at the same time.

The response to both was overwhelmingly positive!

So much so that we are bringing this idea on as a new service offering, effective today!

This service comes with different levels for different needs and budgets.  It is primarily intended for you to be able to get a quick response from someone you trust (that’s us…) when bad things happen to your database server, which they eventually will.  Its the nature of tech.

In addition to being there for emergencies, you also get some periodic proactive benefits as well.

All of the pricing, benefits and terms are on the Pocket DBA™ page, separate from our current Services page.

Launch Special:  All new customers that sign up in February qualify for special rates and additional benefits… This is a limited-time offer, please message me directly for more details.

Contact us today with any questions, or to grab one of those 3 early-adopter spots!

This service is only available to US businesses at this time.

Filed Under: Dallas DBAs, Emergency, SQL, Troubleshooting

Primary Sidebar

Search

Sign up for blogs, DBA availability and more!


Home Blog About Privacy Policy
  • Home-draft
  • Blog
  • About Us

Copyright © 2023 · WordPress · Log in