Check SQL logs with Log File Viewer, the sp_readerrorlog procedure or the SQL Server Agent error log. Error logs record events and not row changes. Row changes need log analyzer software that opens the transaction log and names the login.
Your application fails at midnight and the only clue sits in a log. SQL Server has several logs and finding the right one is the first problem.
Most articles stop at the error log in SSMS. Few explain the T-SQL option, the Agent log or the transaction log that shows data changes.
This guide explains four ways to check SQL logs in SQL Server and which log answers which question.
Microsoft documents the error log settings in its configure SQL Server error logs article.
Which SQL Log Answers Your Question?
SQL Server saves events in separate logs:
| Log | Records | Where to Check |
|---|---|---|
| SQL Server error log | Troubleshooting events defined by users and the system | Log File Viewer or sp_readerrorlog |
| SQL Server Agent error log | Warnings and errors by default | Error Logs folder under SQL Server Agent |
| Transaction log | Every transaction and database modification | Log analyzer software |
Every SQL Server start creates a new error log and the previous six logs remain by default, according to Microsoft. SQL Server Agent retains up to nine logs.
Which Permissions do You Need?
Four tasks need four different permissions:
| Task | Permission |
|---|---|
| Open an online instance in Log File Viewer | Membership in the securityadmin role |
Execute sp_readerrorlog | Membership in securityadmin or VIEW SERVER STATE on the server |
Execute sp_cycle_errorlog | Membership in the sysadmin role |
| Open the SQL Server Agent node | Permission to use SQL Server Agent |
On SQL Server 2022 and later VIEW ANY ERROR LOG also works for sp_readerrorlog.
4 Ways to Check SQL Logs in SQL Server
Three methods open event logs and the fourth opens the transaction log:
| Method | Best For | Main Limit |
|---|---|---|
| Log File Viewer | Browsing the error log | No wildcard search |
| sp_readerrorlog | Filtering by text in a query | Four parameters only |
| SQL Server Agent Log | Warnings and errors of Agent | Needs permission for the Agent node |
| Log Analyzer Software | Data changes by table | Shows only entries still in the file |
Method #1: Check SQL Logs in Log File Viewer (Free)
Log File Viewer shows SQL logs in a grid with the date, source and message of every event. Five steps open it:
- Start SSMS and connect to the instance.
- Expand Management in Object Explorer.
- Right click SQL Server Logs, point to View and click SQL Server Log.
- Click Filter to limit the entries by Connection, Date or other general criteria.
- Click Export to save the grid to a text file.
Note: Microsoft states that searching with wildcard characters is not supported. Load Log opens a log file that you specify and Refresh reloads the selected logs with the filters applied.
Method #2: Query SQL Logs With a T-SQL Procedure (Free)
sp_readerrorlog returns the error log through a query and filters it by text. Microsoft documents four parameters:
- Log Number. 0 is the current log and 1 is
ERRORLOG.1. - Log Type. 1 is SQL Server and 2 is SQL Server Agent.
- Search Strings. The last two parameters filter the lines.
Three steps show the common cases:
- Open the current SQL Server error log:
EXECUTE sp_readerrorlog;- Open the previous SQL Server Agent error log:
EXECUTE sp_readerrorlog 1, 2;- Find the messages that mention a database start:
EXECUTE sp_readerrorlog 0, 1, 'database', 'start';Note: The second search string narrows the first one. Log numbers that do not exist return nothing.
Method #3: Check the SQL Server Agent Error Log (Free)
Warnings and errors go to the Agent error log by default. Execution trace messages are off because they can fill the log. Four steps open it:
- Expand the server in Object Explorer.
- Expand SQL Server Agent.
- Expand the Error Logs folder.
- Right click the log and select View Agent Log.
Cycle the Agent log without stopping the service with this statement:
EXEC dbo.sp_cycle_agent_errorlog;Note: Archived logs have a number from .1 for the newest to .9 for the oldest. EXECUTE sp_cycle_errorlog; cycles the main error log without a restart. Only sysadmin members can execute it.
Method #4: Check Transaction Log Entries With EmailDoctor (Shows Inserts, Updates and Deletes)
Error logs record events and not row changes. EmailDoctor SQL Server Log Analyzer lists the logged inserts, updates and deletes of a database. Sorting works by operation, login name, time, table and transaction name.
Check the Entries
Eight steps open the logged transactions:
- Launch the log analyzer and click Open to add the database file.
- Choose Online DB Option. Add the server and the login type. Then select the database.
- Click OK and wait for the log scan.
- Wait for the scan to end, review the totals and click OK.
- Click any table name on the left to see its entries.
- Click the Transaction header to group the entries by operation.
- Click Login Name to see who changed the rows or Time to order the entries by time.
- Click Table Name or Transaction Name to sort by those columns.
Note: How to Find Table Changes in SQL Server has the export steps. How to View SQL Server Transaction Log File opens an LDF file without a server connection.
Which Problems Stop You From Checking SQL Logs?
Five problems have direct fixes:
| Problem | Reason | Fix |
|---|---|---|
| Management folder is missing | The login lacks permission to see it | Ask the administrator for access |
| SQL Server Agent node is missing | Object Explorer shows the node only with permission | Ask the administrator for access |
sp_readerrorlog returns nothing | The log number does not exist or the strings match no line | Use a lower log number and shorter strings |
| The error log is long | A new log starts only at a server start | Execute sp_cycle_errorlog as a sysadmin |
| Wildcard search finds nothing | Log File Viewer does not support wildcards | Search plain text or filter with sp_readerrorlog |
Frequently Asked Questions
Where is the SQL Server Error Log Stored?
Execute SELECT SERVERPROPERTY('ErrorLogFileName'); to see the file path of the current log.
How Many Error Logs Does SQL Server Retain?
Six archived logs plus the current one by default. Select Limit the number of the error log files before they are recycled in the error log settings to change the count.
Can I Start a New Error Log Without a Restart?
Yes. Execute sp_cycle_errorlog and a new log starts. Only sysadmin members can execute it.
Which Permission Does Querying SQL Logs Need?
Membership in securityadmin or VIEW SERVER STATE on the server for sp_readerrorlog. VIEW ANY ERROR LOG also works on SQL Server 2022 and later.
How Many SQL Server Agent Logs Does SQL Server Retain?
Up to nine. .1 marks the newest archived log and .9 the oldest.
Does Log File Viewer Support Wildcard Search?
No. Microsoft states that searching with wildcard characters is not supported. Filter with sp_readerrorlog search strings instead.
Does the Agent Error Log Record Execution Traces?
No. Execution trace messages are not written by default because they can fill the log.
Can SQL Logs Show Who Changed a Row?
Error logs record events and not row changes. Use SQL Server Audit or log analyzer software and sort the entries by Login Name.
Author’s Verdict
Check SQL logs in Log File Viewer for browsing and with sp_readerrorlog for text filters. Questions about Agent jobs belong to the Agent log.
Row changes sit in the transaction log. Analyzer software opens it and an audit that you enabled earlier records it.
Use the lowest permission that works and cycle the logs before they grow.
- SQL logs answer different questions.
- Log File Viewer and
sp_readerrorlogopen the error log and the Error Logs folder opens the Agent log. - Log analyzer software opens transaction entries and names the login.
Next Read: Fix SQL Server Instance Not Running
Also Read: How to View SQL Server Transaction Log File | How to Find Table Changes in SQL Server
