We’ve all been there. Someone walks up and asks, “Is SQL Server having issues?” What’s your first move?
For me, it starts with the SQL Server Error log. I zero in on the timeframe the requester mentioned and begin my investigation.
Quick Review via SSMS
For a fast, no frills check:
- Connect to the server using SSMS.
- Navigate to the appropriate error log.
- Scroll through the log records to spot anything unusual.
The manual review can be surprisingly effective for simple reviews, but sometimes you will need to dig deeper.
Filtering the Error Log
Let’s say a user reports that the database stops responding at 7:00 PM every day. You check the error log and notice I/O related messages at that exact time. That’s a clue worth chasing further.
To filter the log for relevant entries:
- Click Filter at the top of the Log File Viewer.
- Enter
I/O in the Message contains text field.
- Check Apply filter.
- Click OK.
Now you’re looking at a focused list of entries that mention I/O, much easier to analyze.
Querying the Error Log with T-SQL
What if you wanted to go beyond the GUI and query the error log like a regular database table? Enter the mighty
sp_readerrorlog system stored procedure.
Here’s how to replicate the filtered view using T-SQL:
EXEC sp_readerrorlog 0, 1, 'I/O';
- zero = current error log
- 1 = SQL Server Error Log (use
2 for SQL Server Agent Log)
- 'I/O' = string value to filter
This opens up a world of possibilities. You can store the results in a table, run trend analysis, or even automate alerts based on recurring patterns.
Bonus Tip for PowerShell Users
If you’re in the PowerShell camp, the dbatools.io module has your back. Use the
Get-DbaErrorLog command to achieve the same results:
Get-DbaErrorlog -SqlInstance 'localhost' -LogNumber 0 -Text 'BACKUP'
It’s a powerful way to integrate log analysis into your automation workflows.
Whether you’re using SSMS, T-SQL, or PowerShell, the SQL Server Error Log is your first line of defense in diagnosing issues. Once you start parsing it like a pro, you’ll uncover patterns and insights that GUI tools might miss.
Are your wheels turning yet?
The post Troubleshooting SQL Server – Starting with the Error Log appeared first on GarryBargsley.com.