These logs are important because they enable and provide a detailed record of process events that can be used for troubleshooting, performance tuning, and auditing purposes.
Error logs file are generated by SQL Server whenever an error or warning occurs, and they can be viewed using SQL Server Management Studio or by querying the system tables in SQL Server. The logs contain information such as the time the error occurred, the severity of the error, the source of the error, and a description of the error.
Some of the reasons why error logs are important include:
Troubleshooting:
Error logs can be used to troubleshoot issues with SQL Server. By examining the logs, administrators can identify the source of errors and take corrective action.
Performance tuning:
Error logs can also be used to optimize SQL Server performance. By monitoring the logs, administrators can identify performance issues and take steps to improve performance.
Auditing:
Error logs can be used for auditing purposes. By reviewing the sys' logs, administrators can track user activity and identify any unauthorized user access to data on the system.
The location and format of error logs in SQL Server can vary depending on the version and edition of SQL Server being used. Here are the general locations of the error logs by version of SQL Server:
SQL Server 2005 and earlier: Error logs are stored in the "Log" folder under the SQL Server instance's installation directory.
SQL Server 2008 and later: Error logs are stored in the "Log" folder under the SQL Server instance's installation directory, but the default location for the error logs can be changed during installation.
SQL Server 2012 and later: The default location for the error logs is "C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Log" for the default instance, where "MSSQL11.MSSQLSERVER" refers to the version of SQL Server being used.
SQL Server 2016 and later: The default location for the error logs is "C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\Log" for the default instance, where "MSSQL14.MSSQLSERVER" refers to the version of SQL Server being used.
Using SQL Server Management Studio (SSMS): Open SSMS and connect to the SQL Server instance. Expand the "Management" node, right-click on the "SQL Server Logs" node, and select "Configure". In the "Configure SQL Server Error Logs" window, you can see the current location of the error logs and modify the log settings if needed.
Using T-SQL: Open a new query window in SSMS and execute the following query to retrieve the location of the current SQL Server Error log file:
EXEC sp_readerrorlog 0, 1, N'Logging SQL Server messages in file' This will return a result set containing the current path of the SQL Server Error log file.
Using Windows Explorer: Navigate to the default log directory for the SQL Server instance. By default, the location of the SQL Server Error logs is "%ProgramFiles%\Microsoft SQL Server\MSSQL{version_number}.{instance_name}\MSSQL\Log". Replace {version_number} with the version of SQL Server (e.g., "MSSQL12.MSSQLSERVER" for SQL Server 2014) and {instance_name} with the name of the SQL Server instance (e.g., "MSSQLSERVER" for the default instance).
Using the registry: Open the Windows Registry Editor and navigate to the following key:
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\MSSQLServer\Parameters Look for the "SQLArg" value, which contains the startup parameters for the SQL Server instance. The location of the error log file is specified with the "-e" parameter.
Looking at the SQL Server Instance Properties
In SQL Server Management Studio (SSMS), you can view and manage SQL Server error logs using the Object Explorer. Here are the general steps to find the error logs in SSMS by version:
SQL Server 2005 and earlier: In Object Explorer, expand the SQL Server instance, and then expand Management. Right-click SQL Server Logs, and then select View SQL Server Log.
SQL Server 2008 and later: In Object Explorer, expand the SQL Server instance, and then expand Management. Right-click SQL Server Logs, and then select View -> SQL Server Log.
SQL Server 2012 and later: In Object Explorer, expand the SQL Server instance, and then expand Management. Right-click SQL Server Logs, and then select View -> SQL Server Log. Alternatively, you can use the keyboard shortcut "Ctrl+Shift+L" to open the SQL Server Log Viewer.
SQL Server 2016 and later: In Object Explorer, expand the SQL Server instance, and then expand Management. Right-click SQL Server Logs, and then select View -> SQL Server Log. Alternatively, you can use the keyboard shortcut "Ctrl+Shift+L" to open the SQL Server Log Viewer.
Log Properties
SQL Server Error Log Properties are settings that can be configured to control the behavior of error logs in SQL Server. These settings affect how often the logs are logged and cycled, how many logs are retained, and the maximum size of each log file. Here's a brief overview of each setting and how it can affect the server and the transaction log logs:
Maximum number of error log files:
This setting determines the maximum number of error logs that can be retained before the oldest log is overwritten. The default value is 6, meaning that the current error log and the previous 5 logs are retained. If this value is set too low, important error information may be lost. If it is set too high, it may consume more disk space than necessary.
Maximum size (MB) of each error log file:
This setting determines the maximum size of each error log file. When the log file reaches the maximum size, a new log file is created. The default value is 20 MB. If this value is set too low, log files may be created and rotated too frequently, which can lead to performance issues. If it is set too high, log files may consume more disk space than necessary.
Maximum size (MB) of all error log files:
This setting determines the maximum size of all error log files combined. When the total size of all log files reaches the maximum size, the oldest log file is deleted and a new log file is created. The default value is 2,147,483,647 MB (or 2 terabytes), which is essentially unlimited. If this value is set too low, log files may be deleted before important error information can be reviewed. If it is set too high, log files may consume more disk space than necessary.
Recycle error logs:
This setting determines how often error logs are cycled. By default, error logs are cycled when the SQL Server service is restarted. However, this setting can be configured to cycle logs on a regular basis (e.g., daily, weekly, or monthly). If logs are not cycled often enough, log files may consume more disk space than necessary. If they are cycled too often, important error information may be lost.
Why are some errors also saved the Windows Event Log
Centralized logging: The Windows Event Log is a centralized logging mechanism that allows administrators to view system events from multiple sources in one location. By logging SQL Server errors to the Windows Event Log, administrators can see important events related to SQL Server along with other system events in the same place.
Compliance: Some compliance standards, such as PCI DSS, require that logs be written to the Windows Event Log in addition to the application-specific log files. This ensures that events are captured and retained in a secure, centralized location.
Critical errors: In some cases, SQL Server may encounter critical errors that prevent it from writing to its own error log. In these cases, SQL Server will write the error to the Windows Event Log so that it can be captured and reviewed by administrators.
Windows monitoring tools: Windows monitoring tools, such as Microsoft System Center Operations Manager (SCOM), can be configured to monitor the Windows Event Log for specific events, including SQL Server errors. By logging SQL Server errors to the Windows Event Log, these tools can provide real-time alerts and notifications when critical events occur.
Reviewing Logs and The DBA
There are several ways to proactively automate monitoring error logs in SQL Server. Here are some examples:
SQL Server Agent Alerts: SQL Server Agent can be configured to generate alerts for specific errors or error severity levels. These alerts can be configured to trigger an email notification or run a specific job when the alert is triggered. This allows DBAs to proactively monitor and respond to errors as they occur.
Third-party monitoring tools: There are several third-party monitoring tools available that can automate the monitoring of SQL Server error logs. These tools can be configured to monitor specific errors, error severity levels, or even patterns in the error log entries. They can also be set up to generate alerts or notifications when specific conditions are met.
PowerShell scripts: PowerShell scripts can be used to automate the monitoring of SQL Server error logs. The scripts can be scheduled to run at specific intervals and can be configured to check for specific errors or error severity levels. The scripts can also be set up to generate alerts or notifications when specific conditions are met.
Custom T-SQL scripts: Custom T-SQL scripts can be written to monitor the SQL Server error log and generate alerts or notifications when specific conditions are met. These scripts can be scheduled to run at specific intervals using SQL Server Agent, or they can be run manually as needed.
Additional Resources
SQL Server Management and Maintenance (if you just want me to manage your Logs)
Video (2008) but the concepts are still good