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

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'

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'

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'

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'

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'

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'
 

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
 

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'
 

 

Attempting to load library

Review modules loaded into memory.

Syntax:

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

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

• xplog70.dll – Implications of removing the xp_cmdshell stored procedure from SQL Server 2000
  http://support.microsoft.com/kb/891984

 

DBCC Trace Flag On

Review Trace Flags initiation activities.

Syntax:

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

 

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  
Link

References

• Querying SQL Server Error Log ( Lucas Kartawidjaja )
  http://www.lucasnotes.com/2012/10/querying-sql-server-error-log.html
  
• The parameters of xp_readerrorlog ( Amish Shah )
  http://blogs.msdn.com/b/askjay/archive/2010/09/19/the-parameters-of-xp-readerrorlog.aspx
• Using xp_ReadErrorLog in SQL Server 2005 ( by Dan McClain)
  http://www.sqlteam.com/article/using-xp_readerrorlog-in-sql-server-2005