SQL Server Crashes and Critical Failures

A Step By Step Approach To Recovering The Most Critical SQL Server Databases
4.2 (50 ratings) Instead of using a simple lifetime average, Udemy calculates a
course's star rating by considering a number of different factors
such as the number of ratings, the age of ratings, and the
likelihood of fraudulent ratings.
3,862 students enrolled
Instructed by Mike West IT & Software / Other
Free
Start Learning Now
  • Lectures 25
  • Length 1 hour
  • Skill Level Intermediate Level
  • Languages English
  • Includes Lifetime access
    30 day money back guarantee!
    Available on iOS and Android
Wishlisted Wishlist

How taking a course works

Discover

Find online courses made by experts from around the world.

Learn

Take your courses with you and learn anywhere, anytime.

Master

Learn and practice real-world skills and achieve your goals.

About This Course

Published 10/2015 English

Course Description

Software crashes. WE can't stop that. What WE can do is prepare ourselves through education and practice to be able to act quickly and resolve these critical issues.

In the early days of SQL Server many support calls were server down issues that ended up being bugs in SQL Server. While this still happens, today many of those calls aren't the fault of SQL Server.

For example. On Microsoft's case history of database corruption 99% of ALL database corruption can be traced back to the IO subsystem.

Either way, when SQL Server crashes it can be a traumatic experience for a DBA. If a production box crashes during core hours WE need to know what steps to take to resolve the problem as quickly and as cleanly as possible.

This course will take US beyond where to look. WE will cover RECOVERY INTERNALS and the fundamentals of how to analyze, diagnose and resolve critical errors.

WE will cover the SQL Server Error Logs in great detail. One of the first places as SQL Server DBAs WE look to diagnose critical failures is the SQL Server Error logs.

WE will cover server startup failures and analyze SQL Server stack dumps. Additionally, we will analyze several commonly occurring exception signatures.

WE will create a troubleshooting task list as use it as a template for resolving these crashes and critical errors.

While this course has no prerequisites a strong background in SQL Server is highly recommended.

What are the requirements?

  • There are no prerequisites for this course but a solid foundation in SQL Server would be helpful.
  • All of the materials will be free or included in the course. There will be no outside purchases necessary to take this course.
  • Any recent version of SQL Server will do but I'd recommend SQL Server 2014.

What am I going to get from this course?

  • You will be comfortable with reading the SQL Server error log in great detail
  • By the end of this course you will be able to diagnose and resolve critical SQL Server engine errors.
  • You will be able to read the basics of SQL Server stack dumps.

What is the target audience?

  • SQL Server DBAs wanting to expand their knowledge of how and why SQL Server crahses
  • This is not an entry level SQL Server course. I would not recommend this course for beginners.
  • SQL Server DBAs looking to understand exception signatures and other vernacular associated with critical failures in SQL Server

What you get with this course?

Not for you? No problem.
30 day money back guarantee.

Forever yours.
Lifetime access.

Learn on the go.
Desktop, iOS and Android.

Get rewarded.
Certificate of completion.

Curriculum

Section 1: Introduction
01:26

As DBAs we don't spend a lot of time practicing how to react to crashes.

That's because SQL Server rarely crashes.

That makes this course so important.

Since this is something we don't do often we need to be even more cognizant of how to handle these critical failures.

Article

A little about me.

Download Course Content Here
Article
03:30

In this lecture we are going to create shortcuts to the tools we need to take the course.

We don't want to hunt these down every time so let's pin them to our task bar.

01:27

There are three broad categories of crashes.

Let's learn what they are in this lecture.

Article

Let's cover what we learned in this section.

Section 2: SQL Server Startup Parameters
04:09

SQL Server has 3 parameters used upon engine startup.

1) The location of the master data file

2) The location of the master log file.

3) The location of the errorlog.

Let's take a look at these startup options. We will also create a shortcut to the command line because we will use it most of the time to troubleshot startup failures.

03:53

There are two kinds of instances.

The default instance... which is the name of the server, laptop... etc.

Then there are named instances... A named instance is identified by the network name of the computer plus the instance name that you specify during installation.

05:51

The most common reason the SQL Server engine will fail to start is permission errors associated with the user login you've designated to start that service.

Let's learn to troubleshoot this and correct it quickly.

03:22

Often times DBAs like to change the default port of the instance for security purposes.

However, they often forget to see if that port is already in use.

In this lesson we are going to learn how to check for used ports and change the port number for our instance.

Article

Let's go over what we learned in this section.

Section 2 Quiz
15 questions
Section 3: Error Log Dissection
01:37

We need to see what a clean errorlog looks like so we can spot the anomalies when they happen.

01:46

Int this first lecture we will begin combing through the SQL Server errorlog line by line.

03:42

In this lecture we will continue to comb through the SQL Server errorlog line by line.

02:59

In this lecture we will continue to comb through the SQL Server errorlog line by line.

02:09

In this lecture we will wrap up dissecting our errorlog.

Article

In this lecture we will summarize what we've covered in the SQL Server errorlogs.

Section 3 Quiz.
10 questions
Section 4: SQL Server Recovery Troubleshooting
05:59

The resource db is a hidden system database.

It's a true database but we back it up and restore it differently.

Additionally, there are a few tricks we can employ to recover it in an outage.

03:36

There is a straightforward way to restore the master database but it's contingent on two things.

Is the instance online?

Do you have a good backup for the master database?

Let's learn how to restore the master database the easy way.

04:16

This isn't the traditional method we will use to recover the model if it becomes corrupted.

However, the more tools we have at our disposal the better prepared we will be during a crisis.

05:50

In this lesson we are going to hack our way into SQL Server using our administrator rights at the OS level.

It's a tip that comes in handy on the rare occasion you need it.

02:05

Repairing tempdb corruption is often straightforward.

In this lecture we will walk through how to recover tempdb in a crash.

03:51

Rebuilding the master database means starting over.

This isn't a fun task but we will walk through the entire process step by step.

Article

Let's wrap up what we've learned.

Quiz
10 questions
Section 5: Conclusion
Conclusion and Thank You
Article

Students Who Viewed This Course Also Viewed

  • Loading
  • Loading
  • Loading

Instructor Biography

Mike West, SQL Server Evangelist

I've been a production SQL Server DBA most of my career.

I've worked with databases for over two decades. I've worked for or consulted with over 50 different companies as a full time employee or consultant. Fortune 500 as well as several small to mid-size companies. Some include: Georgia Pacific, SunTrust, Reed Construction Data, Building Systems Design, NetCertainty, The Home Shopping Network, SwingVote, Atlanta Gas and Light and Northrup Grumman.

Experience, education and passion

I learn something almost every day. I work with insanely smart people. I'm a voracious learner of all things SQL Server and I'm passionate about sharing what I've learned. My area of concentration is performance tuning. SQL Server is like an exotic sports car, it will run just fine in anyone's hands but put it in the hands of skilled tuner and it will perform like a race car.

Certifications

Certifications are like college degrees, they are a great starting points to begin learning. I'm a Microsoft Certified Database Administrator (MCDBA), Microsoft Certified System Engineer (MCSE) and Microsoft Certified Trainer (MCT).

Personal

Born in Ohio, raised and educated in Pennsylvania, I currently reside in Atlanta with my wife and two children.

Ready to start learning?
Start Learning Now