How to find the location of the Sql Server Error Log File

Sql Server Error Log is very helpful to diagnose and troubleshoot problems. Error log files contain user-defined events and certain system events, which are very helpful for troubleshooting.

In this article, I will explain various alternative approaches to find the location of the Sql Server Error Log file.

Approach 1: Identify Sql Server Error Log file location using SERVERPROPERTY function

If you are able to connect to the Sql Server, then you can use the SERVERPROPERTY function as shown below to find the location of the Sql Server ERRORLOG file:

SELECT SERVERPROPERTY('ErrorLogFileName') 
               AS 'Error log file location'

RESULT:
sql-error-log-file-location-using-serverproperty-function

Below image shows the Sql Server ErrorLog folder with error log files. Here ERRORLOG is the current error log file and remaining six files are the archived SQL Server Error Logs. By default sql server maintains six archived Error log files.

sql-server-error-log-folder

Approach 2: Finding Sql Server Error Log file location using system stored procedure SP_READERRORLOG

This is one more option to find the location of the Sql Server Error Log file, if you are able to connect to the Sql Server. This system stored procedure can be used to view the content of the Error Log file. The information about the Sql Server ErrorLog file location will be present in the first couple of rows in this sp’s result as shown below

EXECUTE SP_READERRORLOG

RESULT:
sql-error-log-file-location-using-sp-readerrorlog

If you don’t want to search the location of the error log file in the result of this SP. Then you can execute the stored procedure SP_READERRORLOG with the optional parameters values as shown below to get the Sql Server Error log file location:

EXEC SP_READERRORLOG 0, 1,N'Logging SQL Server messages in file'

RESULT:
sql-error-log-file-location-using-sp-readerrorlog-with-parameters

This stored procedure takes four parameters and below is the description for each of these parameters:

Parameter 1: The log file which you want to read. Value 0 means current Error log file, 1 means the Archived Error log file ERRORLOG.1, 2 means the Archived Error log file ERRORLOG.2 and so.on.
Parameter 2: This parameter specifies whether you want to read Sql Server Error Log file or Sql Server Agent Log file. This parameter value 1/NULL means Sql Server error log file, 2 means Sql Server Agent log file.
Parameter 3:First string you want to searh in the error log
Parameter 4:Second string you want to search for to further filter the result

Approach 3: Locate Sql Server Log file location using Sql Server Configuration Manager (An option if you are not able to connect to Sql Server)

If you are not able to connect to Sql Server, then this approach will be very hand in identifying the location of the Sql Server Error Log. Go to Sql Server Configuration Manager as shown in the below image:

sql-server-configuration-manager

Alternatively, you can go to the Sql Server Configuration Manager exe location as listed in the below table and then double click on it.

sql-server-configuration-manager-loation

In the Sql Server Configuration Manager as shown in the below image. Select the Sql Server Service and then right click the Sql Server service in the right pan and select the properties option. It will bring-up the Sql Server Service properties dialog, in the dialog go to the Startup Parameters tab. In the Startup Parameter tab, the existing parameter with prefix -e will be the location of the Sql Server Error log (i.e. the path appearing after the prefix parameter -e).

sql-server-configuration-manager-properties-1

Approach 4: Locate Sql Server ErrorLog file loation using XP_READERRRORLOG extended stored procedure

If you are able to connect to Sql Server, then you can use the extended stored procedure XP_READERRRORLOG to find the location of the Sql Server ErrorLog file location as shown below. By the way the system stored procedure SP_READERRORLOG internally calls this extended stored procedure only.

xp_readerrorlog 0, 1, N'Logging SQL Server messages in file', 
                        NULL, NULL, NULL, N'asc' 

RESULT:
xp_readerrorlog

This extended stored procedure takes seven parameters and below is the description for each of these parameters:

Parameter 1: The log file which you want to read. Value 0 means current Error log file, 1 means the Archived Error log file ERRORLOG.1, 2 means the Archived Error log file ERRORLOG.2 and so.on.
Parameter 2: This parameter specifies whether you want to read Sql Server Error Log file or Sql Server Agent Log file. This parameter value 1/NULL means Sql Server error log file, 2 means Sql Server Agent log file.
Parameter 3:First string you want to searh in the error log
Parameter 4:Second string you want to search for to further filter the result
Parameter 5: Here we can specify the time, if we specify it will search the error log entries which are captured from this time.
Parameter 6: Here if we specify the time, then it will search the error log entries which are captured till this time.
Parameter 7: This parameter specify the sort order of the result, for ascending sort order we need to pass parameter value as N’asc’ and for descending sort order we have to pass this parameter value as N’desc’.

[ALSO READ] 100 Frequently used queries in Sql Server

How to get Sql Server Version, Edition, Product Level etc

Many times we come across a scenario where we need to know the Sql Server Version Name (i.e. like Sql Server 2005, Sql Server 2012, Sql Server 2014, Sql Server 2016 etc), Version (i.e. like 11.0.2100.60 it is of the format major.minor.build.revision), Edition (i.e. like Express Edition, Developer Edition, Enterprise Edition etc), Product Level (i.e. like RTM, SP, CTP etc) etc. This article explains how we can get all this information.

Approach 1: Using @@VERSION global variable

We can use the @@VERSION global variable like below to get the Sql Server Version name, Version, Edition etc

SELECT @@VERSION

RESULT:
sql-server-version

Approach 2: Using SERVERPROPERTY function

We can use the SERVERPROPERTY function to get various information like Sql Server Version name, Version, Edition etc

How to get Server Version Name using SERVERPROPERTY function

DECLARE @Version NVARCHAR(128) 
SET @Version = 
    CONVERT(NVARCHAR(128),SERVERPROPERTY ('ProductVersion'))
SELECT
  CASE 
     WHEN @Version like '8%'	THEN 'SQL SERVER 2000'
     WHEN @Version like '9%'	THEN 'SQL SERVER 2005'
     WHEN @Version like '10.0%' THEN 'SQL SERVER 2008'
     WHEN @Version like '10.5%' THEN 'SQL SERVER 2008 R2'
     WHEN @Version like '11%'	THEN 'SQL SERVER 2012'
     WHEN @Version like '12%'	THEN 'SQL SERVER 2014'
     WHEN @Version like '13%'	THEN 'SQL SERVER 2016'     
     ELSE 'Unknown'
  END AS 'Sql Server Version Name',
  SERVERPROPERTY('ProductVersion') AS ProductVersion,
  SERVERPROPERTY('Edition') AS Edition,
  SERVERPROPERTY('ProductLevel') AS ProductLevel

RESULT:
sql-server-version-details-using-serverproperty-function

Version to Sql Server Product mapping

Below table lists out the Sql Server Version to Product mapping

verion-to-product-mapping

[ALSO READ] 100 Frequently used queries in Sql Server

How to get current TIME in Sql Server

Many times we come across a scenario where we need to get the current TIME. There are multiple ways to get this information in Sql Server, here I am listing out few of them. This article also lists out the various formats in which we can get the current TIME

[ALSO READ] How to get Time, Hour, Minute, Second and Millisecond Part from DateTime in Sql Server

Approach 1: Get current TIME Using GETDATE() funtion

GETDATE() function returns the current Date and Time from the system on which the Sql Server is installed/running. We can fetch the TIME part from the DATETIME value returned from the GETDATE() function as below:

SELECT CONVERT(TIME, GETDATE()) 
      AS 'Current TIME using GETDATE()'

RESULT:
current-time-using-getdate-in-sql
[ALSO READ] How to get Current DATE and TIME in Sql Server

Approach 2: Get current TIME Using CURRENT_TIMESTAMP funtion

CURRENT_TIMESTAMP function is the ANSI SQL equivalent of the GETDATE() function. We can fetch the TIME part from the DATETIME value returned from the CURRENT_TIMESTAMP function as below:

SELECT CONVERT (TIME, CURRENT_TIMESTAMP) 
  AS 'Current TIME CURRENT_TIMESTAMP'

RESULT:
current-time-using-current_timestamp-in-sql

From the above result we can see that the TIME returned by the CURRENT_TIMESTAMP function is same as that of the TIME returned by using the GETDATE() function as shown in the previous example.

[ALSO READ] How to get Date Part only from DateTime in Sql Server

Approach 3: Get current TIME using SYSDATETIME() funtion

SYSDATETIME() function can also be used to get the current TIME of the computer on which the instance of SQL Server is running. SYSDATETIME() function provides more fractional seconds precision compared to the GETDATE() function. We can fetch the TIME part from the DATE and TIME value returned from the SYSDATETIME() function as below:

SELECT SYSDATETIME() 'Current TIME using SYSDATETIME()'

RESULT:
current-time-using-sysdatetime-in-sql

From the above result we can see that the precision of the current TIME returned by using the SYSDATETIME function is better than that of the TIME part returned by using the GETDATE() or CURRENT_TIMESTAMP function.

[ALSO READ] How to get Day, Month and Year Part from DateTime in Sql Server

Current TIME in Various Formats

Below are the couple of examples of retrieving current TIME in various formats:

1. Current TIME in the 24-hour format hh:mi:ss

SELECT CONVERT(VARCHAR(8), GETDATE(), 108) 'hh:mi:ss'

RESULT:
current-time-in-hh-mi-ss-format
2. Current TIME in the 24-hour format hh:mi:ss:mmm

SELECT CONVERT(VARCHAR(12),GETDATE(),114) 'hh:mi:ss:mmm'

RESULT:
current-time-in-hh-mi-ss-mmm-format
[ALSO READ] How to get month name from date in Sql Server
3. Current TIME in the 12-hour format hh:mi:ss:mmmAM

SELECT RIGHT(CONVERT(VARCHAR(26), GETDATE(), 109),14) 
          'hh:mi:ss:mmmAM (or PM)'

RESULT:
current-time-in-hh-mi-ss-mmm-am-pm-format
4. Current TIME in the 12-hour format hh:miAM (or PM)

SELECT LTRIM(RIGHT(CONVERT(VARCHAR(20), GETDATE(), 100), 7))

RESULT:
current-time-in-12-hour-hh-mi-am-pm-format
5. Current TIME in the 24-hour format hh:miAM (or PM)

SELECT  CONVERT(VARCHAR(5), GETDATE(), 108) + 
    (CASE WHEN DATEPART(HOUR, GETDATE()) > 12 THEN 'PM'
        ELSE 'AM'
    END) 'hh:miAM (or PM)'

RESULT:
current-time-in-24-hour-hh-mi-am-pm-format
[ALSO READ] How to get Day or Weekday name from date in Sql Server

How to get Current DATE and TIME in Sql Server

Many times we come across a scenario where we need to get the current Date & Time. There are multiple ways to get this information in Sql Server, here I am listing out few of them:

[ALSO READ] How to get Date Part only from DateTime in Sql Server

Approach 1: Using GETDATE() funtion

GETDATE() function returns the current Date and Time from the system on which the Sql Server is installed/running. Basically it derives the value from the operating system of the computer on which the Sql Server instance is running. The value returned from the GETDATE() function is of the type DATETIME.

Below example shows how we can use the GETDATE() function in the SELECT statement to get the current Date and Time.

SELECT GETDATE() 'Current Date and Time using GETDATE()'

RESULT:
current-date-and-time-in-sql-server
[ALSO READ] How to get current TIME in Sql Server

Approach 2: Using CURRENT_TIMESTAMP function

This function is the ANSI SQL equivalent to GETDATE() function. But majority of the developers in Sql Server use the GETDATE() function instead of CURRENT_TIMESTAMP. Both functions return the same result. And return type of the CURRENT_TIMESTAMP function is also of the type DATETIME

Below example shows how we can use the CURRENT_TIMESTAMP function in the SELECT statement to get the current Date and Time.

SELECT CURRENT_TIMESTAMP 
  AS 'Current Date and Time using CURRENT_TIMESTAMP'

RESULT:
current-date-and-time-2-in-sql-server

From the above reult we can see that the Date and Time returned by the CURRENT_TIMESTAMP function is same as that of the GETDATE() function shown in the previous example.

[ALSO READ] Difference between DateTime and DateTime2 DataType

Approach 3: Using SYSDATETIME() funtion

SYSDATETIME() function can also be used to get the current Date and Time of the computer on which the instance of SQL Server is running. SYSDATETIME() function provides more fractional seconds precision compared to the GETDATE() function. Return type of the SYSDATETIME() function is of the type DATETIME2. This function is introduced in Sql Server 2008.

SELECT SYSDATETIME() 'Current Date and Time using SYSDATETIME()'

RESULT:
current-date-and-time-in-sql-server-using-sysdatetime