Technical: Microsoft – SQL Server – Error Log – Mining

Technical: Microsoft – SQL Server – Error Log – Mining

Introduction

SQL Server Error Log contains a bevy of nuggets pertaining to configuration and laborious workings of the SQL Server Database Engine.

Processing

We can use master.dbo.xp_readerrorlog to comb through these log files.

Category

Here are a couple of insight into what is available.

Need Subsystem Filter
Review failed backups Backup BackupIORequest
System Info System Manufacturer & Model System Manufacturer
Authentication Mode (Trusted / Mixed) Authentication Mode Authentication Mode
Lock Memory Privilege Memory Lock Memory Privilege
Processor Processor CPU
 Network Port Network Port TCP-IP Listening
Boot strap information Registry Startup Information registry startup parameters
SQL Server Trace SQL Trace SQL Trace
SQL Server Library Modules Library Modules Attempting to load library

Database – Backup

Backup info…

Syntax:

exec master.dbo.xp_readerrorlog 0,1, 'BackupIORequest'

Sample:

To review failed Backup for 01/26/2014 and in this case the SQL Instance has been restarted and thus we have rolled-over the error log file, we will issue a query such as

exec master.dbo.xp_readerrorlog 1,1, 'BackupIORequest', '2014_01_26'

Arguments:

  • 1 is the most recent rolled over error file
  • 1 – SQL Server Subsystem, 2 – SQL Server Agent
  • 2014-01-26 – 2nd filter, not compulsory but helpful here to focus us on a specific date

errorLogDataMiningCleanedup

You can also try:


exec master.dbo.xp_readerrorlog 1,1, 'BackupIORequest', null
    , '1/26/2014', '1/27/2014'
Argument Subsystem Sample
File ID File Number (in reverse chronological order)  1
 Subsystem 1 – SQL Server Engine
2 – SQL Server Agent
 1
Filter Argument 1  BackupIORequest
Filter Argument 2
Date Start  1/26/2014
Date End  1/27/2014
 Ordering  Ascending / Descending ASC
DESC

System Manufacturer & Machine Model Number

Who is your system’s manufacturer and machine’s model number?

Syntax:

exec master.dbo.xp_readerrorlog 0,1, 'System Manufacturer'

SystemManufacturer

Authentication Mode

SQL Server supports two authentication mode; Windows authentication (Active Directory) and native authentication.

Windows Authentication is always enabled; you can choose to support native or not.

Syntax:

exec master.dbo.xp_readerrorlog 0,1, 'Authentication mode'

AuthenticationMode

Lock Memory Privilege

Are you properly configured for locking SQL Server Memory / pages in memory?

Syntax:

exec master.dbo.xp_readerrorlog 0,1, 'lock memory privilege'

lockMemoryPage

Please keep in mind that the ability to lock pages in memory is configured via the OS Group or Local Policy.

CPU

Number and configuration of CPU.

Syntax:

exec master.dbo.xp_readerrorlog 0,1, 'CPU'

CPU

In our case, the number of CPUs is 4 and we have a single NUMA slot.

These days with hypervisors and hyper-threading one has to be careful reading too heavily into the number of CPUs.

CPU-Z from CPUID is possibly a better tool for reviewing CPU metrics; especially in terms of reviewing how energy consumption settings is impacting the system’s CPU performance.

Networking / TCP-IP Port

TCP/IP Network Listening port.

Syntax:

exec master.dbo.xp_readerrorlog 0,1, 'listening'

TCPIPListening

Registry Startup Parameters

A couple of MS SQL Server’s start-up data is kept and availed from memory.

Syntax:

exec master.dbo.xp_readerrorlog 0,1, 'registry startup parameters'
 

RegistryStartupParameters

Included are the location and names of the master database files, both the data and log files.  And, the error log file’s location and name.

SQLTrace

Review SQL Trace invocation and termination.

Syntax:

exec master.dbo.xp_readerrorlog 0,1, 'SQL Trace', null
 

SQLTrace

The system’s default trace was started by spid 7.  And, it is recognizable by its Trace ID = 1.

User’s Trace Sessions start off at Trace ID = 2 and the person that initiated them are noted.

SQLTrace

Review memory pressure.

Syntax:

exec master.dbo.xp_readerrorlog 0,1, 'memory', 'paged out'
 

MemoryPagedout

 

Attempting to load library

Review modules loaded into memory.

Syntax:

exec master.dbo.xp_readerrorlog 0,1, 'memory', 'Attempting to load library'
 

AttemptingToLoadLibrary

There are three modules; xpstar.dll, xpsqlbot.dll, and xplog70.dll

 

DBCC Trace Flag On

Review Trace Flags initiation activities.

Syntax:

exec master.dbo.xp_readerrorlog 0,1, 'dbcc traceon'
 

 

dbccTraceOn

In the screen shot above, we can see that the two trace flags relevant to deadlock capturing are effected.

 

 

Listening To

Listening to the lithe lady I saw at the famed San Francisco Fillmore a few years back.  It is magical how music can bring strangers together into crowded auditoriums and have them seamlessly share each other.

Lee Ann Womack – Twenty Years And Two Husband  
http://www.youtube.com/watch?v=h0vQ5qt3JbI

References

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s