How To Check SQL Server Error Logs and Operations System Logs
SQL Server Logs and Operating System Logs(Event Logs) are logs that SQL Server Database Administrators checks on a daily basis, or use to find the cause of the error when they encounter an error. In this article, we will learn how to check these logs.
Through SSMS, you can access both logs from the View tab by right-clicking SQL Server Logs under Management as follows.
You can also access the operating system logs from the Event Viewer on Windows. To access the event viewer, go to Run->Search and write “Event Viewer”.
When we open the Event Viewer, a screen appears as below. You can find logs from the tabs under Windows Logs.
If you want to filter SQL Server Error Log, I suggest you to read the below article.
Author: dbtut
We are a team with over 10 years of database management and BI experience. Our Expertises: Oracle, SQL Server, PostgreSQL, MySQL, MongoDB, Elasticsearch, Kibana, Grafana.
Ingenious Guide to View Log File of SQL Server
Microsoft SQL Server application is one of the biggest waves in the relational database management system and handles huge database in a well-structured manner.
Nowadays, Ex-employees or hackers intentionally modifies the values of databases in order to damage the organization assets. And it becomes difficult to analyze or examine who is the culprit manually. As a result, the Organizations run into big trouble.
In SQL Server, there is a transaction Log file that keep records of all transactions & modifications in database executed on a database in a Microsoft SQL Server. By reading the Log file, one can easily check who deleted data from table in SQL Server database. Plus, it is used by forensic investigator to examine SQL Server Transaction Log and view & check every log detail in a detailed manner. In short, with SQL Log file, it becomes easy to find out which query performed on which table at what time.
Here, we are going to answer how to view log file of SQL Server by using various workarounds. Just go through this article once and understand how to open or read transaction log file in Microsoft SQL Server 2017 / 2016 / 2014 / 2012 / 2008 / 2008 R2 / 2005.
Moreover, if user want to restore the deleted query from a log file, then they can go through this blog – How to Recover Data from Log file in SQL Server – A Complete Guide .
Methods Use For How to View Transaction Log File of SQL Server
In the following section, you will understand how to open, check and read transaction file to retrieve information about the data which had been altered. So, let’s get started!!
Workarounds to Read SQL Log File
- Check SQL Server Logs Using SSMS
- View SQL Transaction Log File Via. Fn_dblog()
- Use SQL Log Analyzer to examine SQL Server Log file
#Approach 1: Use Log File Viewer in SQL Server Management Studio
Basically, this method exclusively used to open and view the information about following logs in SSMS:
- Audit Collection
- Database Mail
- Job History
- Data Collection
- SQL Server
- SQL Server Agent
- Windows Events
Its prime function of Log File Viewer is to provide the report of activities taken place in SQL Server Management Studio. In fact, one can open the Log File Viewer wizard in different ways on the basis of information that you want to check. Now, go through the instructions to view log details in SQL Server.
How to View Log File of SQL Server Via. Log File Viewer
Step 1: Open Microsoft SQL Server Management Studio application. Here, we are using SQL Server 2014 environment for reading SQL Server Error Log.
Step 2: Connect to Server windows pops-up. Here, you need to select the Server Name and Type of Authentication. Afterward, click on Connect.
Step 3: In Object Explorer, go to Management as shown in the screenshot to examine or read log file of SQL Server 2014.
Step 4: Now, move to SQL Server Logs option.
Step 5: Now, Right-click on SQL Server Logs and select View >> SQL Server Log sequentially.
Step 6: All the Log summary displayed on Log File Viewer window. Here, you can select other logs such as SQL Server Agent , Database Mail from the left panel to check its information too.
#Approach 2: View Log File of SQL Server Via. Undocumented fn_dblog()
Originally, the function fn_dblog() is used to extract data from Transaction file of SQL Server for forensic purposes to analyze every log event performed on the table. So, let’s check out how to read transaction log file in Microsoft SQL Server 2017 / 2016 / 2014 / 2012 / 2008 / 2008 R2 / 2005 editions.
Steps to View Log File in SQL Server Using Fn_dblog()
Step 1: We have a table named as ‘Employee’. So, first view the values of the table using the following T-SQL.
Select * from employee.
Step 2: Afterward, alter the table data using update command. For this, execute the query;
Update employee set department =’IT’ where emp_name = ‘jeevan’
Step 3: Again, view the table values using the Select Query. Now, you can see a modified table.
Step 4: Run the fn_dblog function according to the need. Here, we execute the query to check out the time when update operation was executed.
Select [Begin Time], [Transaction Name] from fn_dblog(null , null) where [Transaction Name] = ‘Update’
Step 5: In a situation, when you want to analyze all the logs such as Delete etc. , then run the following T-SQL query.
Select [Begin Time], [Transaction Name] from fn_dblog(null, null)
However, there are some consequences attached with fn_dblog(). Actually, this function only provide the time of the query when it was committed instead of which data entry gets affected. Due to which, it becomes cumbersome to find out which table data get altered. This problem is overcome with the third technique where user can view the log file of SQL Server without any hassle. Apart from this, both the described technique can run in SQL Server Management Studio only. You cannot read a Transaction Log File in offline environment with Log File Viewer and Fn_dblog().
#Approach 3: Use Smart Solution to Analyze Transaction File Easily
To get exact information from SQL Log File, take the help of SysTools SQL Log Reader Software. With the help of this software, user can scan and analyze T-log file in human readable format. However, the tool works in Online as well as Offline environment. User can get the information like Transaction , Login Name , Time , Table Name , Query . It is a best software solution that answers the question – how to read SQL Server Transaction Log file.
Related : How to Fix Log File Corruption – Step-By-Step Guide
In fact, after viewing the log file of SQL Server, user can export the query in Live SQL Server database environment , SQL Compatible Scripts , and in CSV format. Moreover , the software can read Transaction log file of every SQL Server edition.
That’s all about on how to View Log file of SQL Server. Now, go through the methods and opt the best that suitable for you and examine SQL Server Transaction Log file.
Frequently Asked Questions:-
Try SQL Log Analyzer tool to easily scan and read the Transaction .ldf file records.
Use Fn_dblog() function to read the details of transaction in SQL Server.
With the help of SQL Log Viewer, one can read .ldf file and view Transaction, Transaction time, Table name and Query of Microsoft SQL Server 2017, 2016, 2014, 2012, 2008 and SQL Server 2005
Yes, with the help of mentioned workaround, one can easily examine SQL LDF file .
Exclusive Offers & Deals, Grab it Now!
An entrepreneur, technical analyst, a writer with innovative and authentic thoughts when it comes to technology. Renders brilliant solutions to deal with issues users face while working with technology. Having amazing knowledge in technical arenas in numerous field.
Subscribe to our newsletter to get the latest offers
SysTools Software Pvt. Ltd.
P.O. Box 36, Springville, Utah — 84663
Call Us
USA: +1 888 900 4529
UK: +44 800 088 5522
See All Offices
Delhi Office
SysTools Software Pvt. Ltd.
528, City Centre, Sector-12, Dwarka, New Delhi — 110075, India
Pune Office
SysTools Software Pvt. Ltd.
502 — P4, Pentagon, Magarpatta Cyber City, Pune — 411028, India
Mumbai Office
SysTools Software Pvt. Ltd.
Techno IT park (Near Eskay Resorts & Times Square Restaurant, Link Road, Borivali West Mumbai — 400091, India
Banglore Office
SysTools Software Pvt. Ltd.
Queens Road, Bangalore, India
© Copyright 2007-2023 by SysTools.
SysTools ® is a Registered Trademark of SysTools Software Pvt. Ltd.
Your Choices Regarding Cookies on this Site
Cookies are important to the proper functioning of a site. To improve your experience, we use cookies to remember log-in details and provide secure log-in, collect statistics to optimize site functionality, and deliver content tailored to your interests. Click Agree and Proceed to accept cookies and go directly to the site or click on More Information to see detailed descriptions of the types of cookies and choose whether to accept certain cookies while on the site.
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:
RESULT:
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.
[ALSO READ] How to change Sql Server ErrorLog files location
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
RESULT:
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:
RESULT:
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:
Alternatively, you can go to the Sql Server Configuration Manager exe location as listed in the below table and then double click on it.
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).
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.
RESULT:
This extended stored procedure takes seven parameters and below is the description for each of these parameters:
Немножко всего .. из жизни администратора ms sql server
Добрый день. Посоветуйте схему корректного Бэкапирования журналов событий, ошибок MS SQL 2014 (предположительно копировать файлы ERRORLOG — 6 шт. и SQLAGENT.OUT — 10 шт.) без потерь при обнулении файлов (при рестарте) и без лишнего наложения истории событий. Выбрать также правильный интервал бэкапирования.
Зачем их копировать ? сделайте их количество порядка 20-30 в настройках и они будут сохранятся. Ну а если надо все таки копировать, полно win средства для копирования файлов по расписанию. либо bat файл сделайте и запускайте по расписанию. Это уже задача не SQl server/