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.
|
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'
|
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