• 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

SQL

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

SQL Server is not a Car

September 5, 2023 by Kevin3NF 1 Comment

Lets compare them anyway, just for fun.

  • Both have an engine
  • Both have a monitoring device (Dashboard/Activity Monitor)
  • Both need power (Gas/Electricity)
  • Both need to be told what do to (start/drive, add Databases/code)
  • Both need to be properly secured (Locked / security best practices)

SQL Server maintenance – Car maintenance;

  • Index defragmentation – Fill up on gas. Generally not needed every day, but regularly, depending on your workload/driving habits
  • Backups – Car Insurance. Sure, you can drive without insurance but what if you crash, or someone steals it (ransomware?)
  • Corruption Check – body check – look for rust, corrosion, scratches, etc. Database corruption may not always impact your app, but eventually could lead to data loss.
  • Statistics updating – air pressure in the tires. You should do this every day, but if you want to drive around on 10lbs of pressure, go for it!
  • Index and Query tuning – Oil change. Review your performance metrics regularly as workload and app functionality change. Indexes need tuning to keep the engine from locking up.

What would you add to this list of essentials, to keep your SQL Server/Car running?

Thanks for reading!

Kevin3NF

Follow @Dallas_DBAs

Filed Under: SQL Tagged With: syndicated

Test Restore Your SQL Server Databases

July 25, 2023 by Kevin3NF Leave a Comment

Pain Point: Something bad happened and you need to restore a SQL Server database.

Pain Point you didn’t know you had: The backup files are all corrupt due a problem with the storage subsystem.

Solution: Do test restores with one line of code from DBATools.io

Set-DbatoolsInsecureConnection -SessionOnly 

Test-DbaLastBackup -SqlInstance Precision7770-1

You will need a modern version of PowerShell and the DBATools module. Also a destination for the test restores to land.

This video is not sponsored by Sean or Jen from MinionWare, they are just really nice people selling a SQL Server maintenance solution for large enterprises 🙂

Thanks for reading!

Kevin3NF

Follow @Dallas_DBAs

Filed Under: backup, Dallas DBAs, Restore, SQL Tagged With: DBATools.io, syndicated

Storytelling for DBAs and Other Technical Folk 

June 20, 2023 by Kevin3NF Leave a Comment

(for your reading pleasure, written by Jules (L). –ed)

We have all had that sinking feeling of defeat: you’ve just finished explaining a very important and highly technical thing to people who are not in IT, and you can read it in their eyes that they just don’t get it. Half of communication is on the listener’s part, it’s true. They have to pay attention, they have to ask questions, they have to care. But none of that is in your control. All that is within your control is how you present the information. And that’s where storytelling comes in. You may think you don’t need storytelling skills, but the truth is, you are a human, talking to another human, trying to convey a premise, stakes, characters, plotlines, and possible resolutions. That’s a story, and you need to know how to tell it. 

The Hook: Give them something to care about, inspiring curiosity, fear, hope, etc. in plain English. Example: “SQL02 had some problems over the weekend, but I’ve talked with some very smart folks at Microsoft, and we have a clever plan to not only fix it but keep it from happening again.” 

The Stakes: It doesn’t have to be earth-shattering, or even company-ending, for the stakes to be high. But you do have to make them CARE about what’s at stake. Emphasize what’s important about the problem, in non-technical terms. Example: “Getting our data in line with this compliance isn’t just government red tape. It demonstrates our integrity, and it a great sign of reliability to both clients and investors. Failing to do so could make an otherwise minor data breach into national news.”

The Characters: To be sure, your IT systems are characters in your narrative, and communicating their essential identity markers to your audience is key. You don’t have to tell them numbers. Tell them how to feel about those servers: “SQL02 has been unreliable, but we can change that, make it almost as good as SQL09, which as you all know is rock-solid and jet-fast.” Programs can be characters, code can be characters, and certainly your IT crew can be characters (but be nice about them, they’re real people. Bad-mouth SQL02 all you want, you can’t hurt its feelings.) 

The Plot: This is the tricky part, to summarize what may be a complicated and years-long battle with legacy code, difficult people, old hardware, etc. As simply as you can, tell them what the major conflict is, and what is holding you back. Think about it in terms of actions, not thoughts or feelings; think about what is HAPPENING. Keep it to what they need to care about right then, in this story. It may be that the Big Bad Wolf was also the problem in Three Little Pigs, but that doesn’t matter if the story you’re telling is Little Red Riding Hood. Stick to your current narrative. There’s always time to chat about backstory later. 

The Resolution: Now! Tell them what needs to happen! What you need from them! And what it will be like afterwards. Wrap this thing up and bring it home. Again, keep it simple; instead of “I need to patch SQL02 which will require a restart, which will cause x problems,” frame it as “The solution is to patch and restart SQL02, and have a plan we developed ahead of time for handling the outage, after which we will be more stable and secure.” You may or may not include “And we all lived happily ever after.”

So go! Tell the story of your server environment! Work out a plotline of advancements, with a redemption arc story for SQL02! Make your audience CARE! And may we all get more good work done, and be heroic, and ride off into the sunset in our Hondas. 

–Jules

Filed Under: SQL Tagged With: syndicated

Code That Writes Code

April 28, 2023 by Kevin3NF Leave a Comment

“Work Smarter, not Harder”

We’ve all heard it before, pretty much any job, anywhere.

In our DBA slice of the IT world, this is very relevant to how we manage SQL Servers and the databases on them.

From scripts to check for Bad Things™, to writing efficient code.

In this video, I show a simple way to get SQL Server generate scripts you can run.  In this case I needed to run the same command against a large number of databases. Running it manually, or worse, doing it in the GUI is just not an option.  Enjoy!

The code that I will now use as a starter script:

-- Don't run this if you don't understand it 100%

Select 'ALTER Database ['+[name]+'] set Page_Verify Checksum, recovery Full, auto_shrink off;
go'
From master..sysdatabases
Where DBID > 4

Thanks for reading!

Kevin3NF

Follow @Dallas_DBAs

Filed Under: SQL, TSQL, video Tagged With: SQL, syndicated

Organize Your Tabs in SQL Server Management Studio

November 2, 2022 by Kevin3NF Leave a Comment

I blogged about this in August, but wanted to do this video as well:

Thanks for reading and/or watching!

Kevin3NF

Follow @Dallas_DBAs

Filed Under: SSMS Tagged With: syndicated

  • Go to page 1
  • Go to page 2
  • Go to page 3
  • Interim pages omitted …
  • Go to page 30
  • Go to Next Page »

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

 

Loading Comments...