SQL Server – Error Log – Mining


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


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


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…


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


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'


  • 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
Filter Argument 1  BackupIORequest
Filter Argument 2
Date Start  1/26/2014
Date End  1/27/2014
 Ordering  Ascending / Descending ASC

System Manufacturer & Machine Model Number

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


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.


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


Lock Memory Privilege

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


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.


Number and configuration of CPU.


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.


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.


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.


Review SQL Trace invocation and termination.


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.


Review memory pressure.


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



Attempting to load library

Review modules loaded into memory.


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


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


DBCC Trace Flag On

Review Trace Flags initiation activities.


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  


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 )

Google+ photo

You are commenting using your Google+ 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 )

Connecting to %s