The log is just a journal of events that have occurred on a SQL Server instance, in chronological order. These errors could be informational only, warnings, as well as actual instance and application errors. You can find things like start up and shut down information, backup and restore commands, as well as custom application messages in the error log file. The error log is a great place to look for problems or potential problems associated with an instance of SQL Server.
The error log is not a single file but a series of files. Each time SQL Server starts up, a new error log file is created. A running instance of SQL Server writes to current log the one created at startup and by default has six archived error log files. If you need to keep more than six archived files, you can override the default to keep as many as you need more on this later.
If an instance of SQL Server crashes or will not start for some reason, the error log is the place to go for troubleshooting these problems. As a DBA, you should periodically review the error log to look for potential problems. By reviewing the log, you might find some unusual things going on that might otherwise go unnoticed, like a backup job has failed or someone trying to hack the SA password.
This is only the default location. Some instances might be set up to write their error log files to a different location. If the error log files are not in the default location, then there are a number of different ways to find them. I will show you two of those methods. Next double click on the instance of SQL Server that you want to locate the error log file location. Then click on the Advanced tab. The location of the error log file directory is identified in the Dump Directory item.
To see the full name of the error log file directory, click on the little down error to the right of the Dump Directory item, as shown below in Figure 1. A second method to find the location of the error log files is to use SSMS to browse one of the error log files. Then double-click on the Current error log file. When you do this, the Log File Viewer will be displayed. To find the error log file location you can either browse thru the log file until you find it or use the Search… option to find it.
The log files are stored in the log folder of the instance. There are three problems with the default behavior of SQL Server. The first problem is that you have no control over the size of the log files. One file can be very large while another file can be very small. It would be easier to manage these files and work with them if they had a more controlled and predictable size.
The second problem is that you have no control over the recycling process. One file can contain two months of messages, while another file can contain only a few hours of messages. The third problem with the default behavior of SQL Server is that there are only 7 log files, and if recycling happens too often, then you might not have enough history.
In order to solve the first two problems mentioned above, all we need to do is to control the recycling process. The sys. Each time you call this stored procedure, SQL Server performs the recycling process as mentioned above. This way, each log file will contain messages for a single day. In order to solve the third problem not enough history , all we need to do is to increase the number of log files.
You can determine how many archive log files to keep in addition to the current log file. Suppose we want to search SQL Server error logs for a specific duration. Suppose we want to search for keyword recovery between 7th November midnight and 9 AM. We can specify the start and end date parameter in the following format:. In the output, you can see the result is sorted in the ascending order from the start date to the end date. We can specify parameter desc so that results are sorted in descending order from an end date to start date:.
EXEC sys. N 'Recovery' ,. The current error log has no extension. For more information, see View Offline Log Files. Feedback will be sent to Microsoft: By pressing the submit button, your feedback will be used to improve Microsoft products and services.
Privacy policy.
0コメント