This blog gives you a big picture of SQL Server Error Log file and where it is located in the system. So, let’s have a deeper look of it.
The SQL Server Error Log sounds like Transaction Log, which keeps a record of database transactions and ensures database integrity. Right?
Unfortunately, Error Log file is not like that.
SQL Error Log files are very much different from SQL Server LDF. It majorly comprises User-defined events and used to troubleshoot system problems. In this technical guide, we are going to provide complete information about Microsoft SQL Server Error Log File.
It comprises information regarding the Audit Collection, Database Mail, Windows Events, SQL Server Agent, SQL Server, Job History, Data Collection. Moreover, it shows a summary report that use to analyze which activities took place in SSMS. So, it can easily troubleshoot by the user.
Where is MS SQL Server Error Log Stored?
You can easily find the SQL Error Log location for your dedicated SQL Server version. All you need to use the following path to access it.
It is a general path that provides you SQL Server 2017 / 2016 / 2014 / 2012 / 2008 / 2008 R2 / 2005 error log file location. For example, it is the SQL Server 2016 Error Log file location in Windows 10.
How to Change Error Log File Location in MS SQL Server 2017/ 2016/ 2014/ 2012
Sometime SQL users need to update the path of an SQL Error log file. The overall procedure is classified into two stages.
Stage 1:Start the SQL Server Configuration Manager
One can open the Configuration Manager with different techniques. All of them are mentioned below.
Method #1 – To open Configuration Manager, you can type the SQL Server Configuration Manager in the search box of Start and open it as shown in the screenshot.
Method #2 – Else, you can use the following path to open SQL Server Configuration Manager.
SQL Server Version
SQL Server Configuration Manager Exe Location
SQL Server 2017
SQL Server 2016
SQL Server 2014
SQL Server 2012
SQL Server 2008
Here, we opened the SQL Server Configuration Configuration Manager with the above – mentioned location in SQL Server 2017.
Method #3 – You can also open the Run dialog box and type ‘compmgmt.msc’ in the box.
From the following screen, expand the Services and Applications section and get the SQL Server Configuration Manager.
Stage 2: Change the SQL Server Error Log Path
Step 1: Under the SQL Server Configuration Manager, go to the SQL Server Services option.
Step 2: Perform right-click on SQL Server(SQLEXPRESS) as shown in the screenshot and choose the Properties option.
Step 3: From the SQL Server(SQLEXPRESS) Properties, click on the Startup Parameters tab. Afterward, under the Existing parameters section, choose the log file whose location you want to change.
Step 4: The starting prefix (like e,d) and the current Error Log file Name will not change. Rest of the folder can be changed. Mention the new path under the Specify a startup parameter box and update it.
Step 5: The changes to SQL error logs location are saved but they are not active. For this. You need to start the SQL Server Services.
to Open SQL Server Error Log files
There are two ways through which one can view the Error Log. Now, whenever, the SQL Server could not open Error Log file, then use any of the below-mentioned technique.
#1: Use T-SQL to Read SQL Error Log
Connect with the SSMS and type ‘sp_readererrorlog’ in the panel. It is a SQL Server query to read Error Log.
Approach #2: Use Object Explorer Option
Without the help of T-SQL, you can also view the error log. Moreover, if you want to examine the SQL Transaction Log file, then you can try SysTools LDF File ReaderSoftware. Let’s check out how to read SQL Server Error Log.
Open Microsoft SQL Server Management Studio on your system.
Step 2: Time to connect with Object Explorer. Open Connect to Server Window and select the Server name. Afterward, choose the Authentication Type and click on Connect.
Step 3: Go to the Management section from the Object Explorer in order to read SQL Error Log.
Step 4: Expand the Management Section and go to the SQL Server Logs.
Step 5: Right-click on SQL Server Logs option. Go to the View option and select SQL Server Log from the menu.
Step 6: The Log File Summary will appear on the screen. From the same window, you can opt for different Log – SQL Server Agent, Database Mail.
That’s all about Microsoft SQL Server Error Log.
Frequently Asked Questions –
How to find SQL Server Error Log path ?
The location of Error Log – C:\Program Files\Microsoft SQL Server\MSSQLn.SQLEXPRESS\MSSQL\Log , where n is equivalent to number basis on SQL Server version.
How to perform SQL Server error log monitoring ?
Open ErrorLog using Log File Viewer in Management Studio and analyze all the events happened in the system.
What is the difference between SQL Log & SQL ErrorLog File?
The SQL Log or Transaction Log file keeps record of every transaction performed on the database. Whereas, the Error Log used to keep track of events happened in SSMS related to ports, network and much more.
About The Author
Our content media team consists of geeks and techno researchers, who are dedicated & committed to deliver the best industry knowledge and updates. Stay tuned to our updates and know many - WHAT ? HOW ? WHY ? WHEN ? etc.