View the SQL Server error log to ensure that processes have completed successfully (for example, backup and restore operations, batch commands, or other scripts and processes). This can be helpful to detect any current or potential problem areas, including automatic recovery messages (particularly if an instance of SQL Server has been stopped and restarted), kernel messages, or other server-level error messages.
1. You can connect to SQL Server using SQL Server Management Studio by providing correct name, and execute the below T-SQL command which use the sp_readerrorlog to find the location of SQL Server Log file.
Here is the output:
Highlighted area “Logging SQL Server messages in file ‘C:\Program Files\Microsoft SQL Server\MSSQL11.SQLEXPRESS\MSSQL\Log\ERRORLOG’” is current ERRORLOG file. Typically, SQL Server retains backups of the previous six logs and gives the most recent log backup the extension .1, the second most recent the extension .2, and so on. The current error log has no extension.
2.You can also find the location of errorlog via using Event Viewer. In Event Viewer,expand Windows Logs and then select Application on the left side panel.In the right panel you need to filter for events with Event ID 17111 as shown in the below snippet.
To set a filter right click on Application and select Filter Current Log.To view the location of SQL Server Error Log file double click an event and you can see the event properties as shown below.