Module 20 troubleshooting common sql server 2008 r2 administrative issues
Download
1 / 23

Module 20 Troubleshooting Common SQL Server 2008 R2 Administrative Issues - PowerPoint PPT Presentation


  • 132 Views
  • Uploaded on

Module 20 Troubleshooting Common SQL Server 2008 R2 Administrative Issues. Module Overview. SQL Server Troubleshooting Methodology Resolving Service-related Issues Resolving Concurrency Issues Resolving Login and Connectivity Issues. Lesson 1: SQL Server Troubleshooting Methodology.

loader
I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
capcha
Download Presentation

PowerPoint Slideshow about 'Module 20 Troubleshooting Common SQL Server 2008 R2 Administrative Issues' - isabel


An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript
Module 20 troubleshooting common sql server 2008 r2 administrative issues

Module 20

Troubleshooting Common SQL Server 2008 R2 Administrative Issues


Module overview
Module Overview

  • SQL Server Troubleshooting Methodology

  • Resolving Service-related Issues

  • Resolving Concurrency Issues

  • Resolving Login and Connectivity Issues


Lesson 1 sql server troubleshooting methodology
Lesson 1: SQL Server Troubleshooting Methodology

  • Discussion: Characteristics of Good Trouble-shooters

  • Applying a Troubleshooting Methodology


Discussion characteristics of good trouble shooters
Discussion: Characteristics of Good Trouble-shooters

  • What characteristics have you observed in people that you consider to be good trouble-shooters?

  • What characteristics do you notice in people that are poor trouble-shooters?


Applying a troubleshooting methodology
Applying a Troubleshooting Methodology

  • Investigate

    • Clearly define the issue as perceived by the user

    • What works? What doesn't work?

    • Did it ever work? When did it last work? What else changed?

    • How would you know if it is resolved?

  • Analyze

    • Brainstorm all potential causes

    • Which potential causes are likely? How could they be tested for and eliminated?

  • Implement

    • Eliminate potential causes in descending order of likelihood

  • Validate

    • Ensure that the issue really is resolved


Lesson 2 resolving service related issues
Lesson 2: Resolving Service-related Issues

  • Troubleshooting Service-related Issues

  • SQL Server Error Log

  • Windows Event Logs

  • Demonstration 2A: Troubleshooting Service-related Issues


Troubleshooting service related issues
Troubleshooting Service-related Issues

Typical issue: One or more SQL Server services will not start or cannot be accessed.

  • Check Windows and SQL error logs

  • If SQL Server can be started but not accessed:

    • Check for network related issues

    • Try to access SQL Server via the DAC

  • If SQL Server will not start:

    • Check the Windows system log

    • Check master and model databases for corruption

    • Check that the paths to tempdb files are accessible

    • Try to start the service from the command prompt


Sql server error log
SQL Server Error Log

  • Log File Viewer cannot be used when the instance is not started

    • Use editor to open the current log file and review it

    • Also review previous logs as the problem might have occurred before


Windows event logs
Windows Event Logs

  • System Log to review Windows related information

  • Application Log to find application related messages


Demonstration 2a troubleshooting service related issues
Demonstration 2A: Troubleshooting Service-related Issues

  • In this demonstration you will see how to troubleshoot a service-related issue.


Lesson 3 resolving concurrency issues
Lesson 3: Resolving Concurrency Issues

  • Core Concurrency Concepts

  • Troubleshooting Blocking

  • Demonstration 3A: Troubleshooting Blocking

  • Troubleshooting Deadlocks

  • Demonstration 3B: Troubleshooting Deadlocks


Core concurrency concepts
Core Concurrency Concepts

  • SQL Server has to ensure ACID properties of transactions

  • Locking is the mechanism used to synchronize access by multiple users to the same data at the same time

  • Isolation levels define the type and length of locks used

  • Two main types of lock:

    • Shared locks – Allow others to read but not write

    • Exclusive locks – Stop others from reading or writing

  • Locks prevent update conflicts

    • Locking ensures that transactions are serialized

    • Locking is automatic

    • Locks enable safe concurrent access to data


Troubleshooting blocking
Troubleshooting Blocking

Typical issue: Applications don't seem to be doing anything but also aren't returning errors.

  • Processes are blocked when they wait for locked resources

    • Normal behavior, essential for the system

    • Resource is shown as waiting to acquire a lock

    • Only a problem when the wait duration is lengthy

  • Monitor long lasting blocking scenarios

    • SQL Server Activity Monitor

    • Dynamic Management Views

    • SQL Server Data Collector


Demonstration 3a troubleshooting blocking
Demonstration 3A: Troubleshooting Blocking

  • In this demonstration, you will see how to troubleshoot a blocking issue


Troubleshooting deadlocks
Troubleshooting Deadlocks

Typical issue: Deadlock errors (1205 errors) are returned.

  • Deadlocks are circular blocking scenarios

  • SQL Server automatically detects deadlocks

    • Rolls back transactions with error 1205 on deadlock victim

  • Monitor using deadlock occurrences with Profiler and SQL Trace

Granted Lock

Waiting Lock

Resource 1

Task 2

Task 1

Waiting Lock

Granted Lock

Resource 2


Demonstration 3b troubleshooting deadlocks
Demonstration 3B: Troubleshooting Deadlocks

  • In this demonstration, you will see:

    • How to trace a deadlock using SQL Server Profiler

    • How to extract deadlock events from SQL Server Profiler

    • How to open deadlock files using SSMS


Lesson 4 resolving login and connectivity issues
Lesson 4: Resolving Login and Connectivity Issues

  • Troubleshooting Connectivity Issues

  • Troubleshooting Login Failures


Troubleshooting connectivity issues
Troubleshooting Connectivity Issues

Typical issue: Cannot connect to SQL Server.

  • Try to access using Shared Memory on the server

    • If no access via Shared Memory, troubleshoot the login and the service

  • Test the network connectivity

    • Can the server name be resolved?

    • Can the network and the server be reached?

    • Is the Browser Service Running for Named Instances that are not using fixed ports?

    • Is the client configured to use the right protocol and settings?

    • Is a firewall blocking connectivity?


Troubleshooting login failures
Troubleshooting Login Failures

Typical issue: Cannot logon to SQL Server.

  • Windows Logins

    • Is the Domain Controller available?

    • Can SQL Server communicate with the Domain Controller?

  • SQL Server Logins

    • Is SQL Server configured for Mixed Mode?

    • Is the password correct?

    • Is the login locked or is there a requirement to change password?

  • General Considerations

    • Is the login enabled and does it have CONNECT permission?

    • Is the default/requested database available and access permitted?


Lab 20 troubleshooting common issues
Lab 20: Troubleshooting Common Issues

  • Exercises 1 – 5: Troubleshoot and resolve SQL Server administrative issues

Logon information

Estimated time: 45minutes


Lab scenario
Lab Scenario

You need to be able to resolve common issues with SQL Server processes and services at the time they are occurring. There are five exercises that create issues. You should attempt to troubleshoot and resolve as many of these issues as possible.


Lab review
Lab Review

  • What advantage would monitoring for deadlock errors using SQL Trace have over using SQL Server Profiler?

  • What is the first place you should look for information when the SQL Server service will not start?


Module review and takeaways
Module Review and Takeaways

  • Review Questions

  • Best Practices