How to: Verify path for SQL Server Error Log Files

  • [アーティクル]

Hello Friends,

March has been a busy month, and it is not over yet. Taking some time off to blog; as small find “worth sharing”.

While managing a SQL Server instance, there are so many different locations to look for MSSQL files, like SQL Server Error Logs. While we can easily locate path to SQL Server Error Logs using SSMS (management studio), here’s is an alternate method (Just-In-Case the SQL Management Studio GUI is not avaialble)

SQL SERVER 2008/R2/2005

Using SQL Server Configuration Manager:

SQL Server Configuration Manager is a tool to manage the services associated with SQL Server. The features however are more:

1. On the Start menu, point to All Programs, point to Microsoft SQL Server 2005, point to Configuration Tools, and then click SQL Server Configuration Manager.

2. In SQL Server Configuration Manager, expand Services, and then click SQL Server

3. In the details pane, right-click the name of the instance you want to start automatically, and then click Properties

4. Click the Advanced tab, and Verify Startup Parameters.

· -e The fully qualified path for the error log file (Refer below screen)SQL_Config_Img 

Other parameters

· -d The fully qualified path master database file

· -l The fully qualified path master database log file

 

As always, for more details on SQL Server Configuration Manager, Refer BOL >> https://technet.microsoft.com/en-us/library/ms174212(SQL.90).aspx

SQL SERVER 2000

Now as we don’t have “SQL Server Configuration Manager” for SQL Server 2000, I need to look for an alternate approach to locate SQL Server Error logs

Using SQL Server Enterprise Manager

1. On the Start menu, point to All Programs, point to Microsoft SQL Server and click Enterprise Manager.

2. Expand Microsoft SQL Servers –> SQL Server Group –> <SQL Server Instance>

3. Right-click on the SQL Server Instance, and then click Properties

4. Click the Startup Parameters tab, and verify Startup Parameters.

· -e The fully qualified path for the error log file (Refer below screen)

image

Now, assuming that Enterprise Manager is not working. Here’s how you still determine the SQL Error Log location….

Using SQL Server Registry Keys

Click Start, click Run, type Regedit, and then click OK.

Locate the following registry subkey:

  • For - Default Instance
    HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\MSSQLServer\Parameters
  • For - Named Instance
    HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft SQL Server\<Instance_Name>\MSSQLServer\Parameters

Go Try This !

Disclaimer: I work at Microsoft. Everything here, though, is my personal opinion and is not read or approved by Microsoft before it is posted. No warranties or other guarantees will be offered as to the quality of the opinions or anything else offered here.

Comments

  1. Connect to the SQL Server, and run the following query from a query window: "sp_readerrorlog".
  2. Search for the string: "ERRORLOG". This should give you the path for the ERRORLOG files.

  • Anonymous
    May 14, 2009
    Thanks Suhas, Yes that's useful suggestion to get the location of SQL ERRORLOG using Management Studio. However, In my specific scenerio, I am trying to explain a approcah to verify the ERRORLOGS when you are UNABLE to connect to SQL Server Management Studio. As always, appreciate your feedback Suhas.

  • Anonymous
    June 01, 2009
    Great stuff!  Exactly what I was looking for.

  • Anonymous
    July 02, 2009
    Here is the way to achieve the same using SQL 2000 EM.

  1. Open SQL 2000 enterise Manager, right click on SQL Server name and select properties.
  2. In GENERAL table, click on STARTUP PARAMETERS and this would provide you option to remove/add error log path as well as other parameters too.
  3. For changes to take effect, restart SQL Service. Hope this helps.