Mssql журнал ошибок

Пересказ статьи Rajendra Gupta. How to manage SQL Server logs effectively

В статье дается обзор журналов SQL Server для управления и устранения неполадок на сервере.

Введение

Журналы являются лучшим средством администратора баз данных при решении любых проблем. Эти проблемы могут быть связаны с конфигурацией сервера, запуском, восстановлением, производительностью, флагами трассировки, тупиковыми ситуациями, вводом-выводом или задержками. Предположим, например, что ваш экземпляр SQL Server перезапускается по непонятным причинам, и после перезапуска службы SQL работают, однако ваше приложение не имеет доступа к базе данных. Таким образом, для исследования проблемы вам нужно заглянуть в последний журнал SQL Server, чтобы проконтролировать процесс восстановления базы данных и узнать оценку времени его завершения.

Администратор базы данных может также сконфигурировать SQL Server для выполнения дополнительных записей в журналы ошибок. Например, мы можем включить флаг трассировки для захвата информации о тупиковых ситуациях. DBA должен регулярно просматривать эти журналы в поисках потенциальных проблем. Вы можете обнаружить в журналах такую информацию, как сбой резервного копирования, ошибки входа, ошибки ввода-вывода. Эти журналы ошибок являются отличным средством для обнаружения существующих и потенциальных проблем в экземплярах SQL Server.

Журналы SQL Server известны как SQL Server Error logs. Журналы ошибок содержат информационные сообщения, предупреждения и сообщения о критичных ошибках. Вы можете просматривать некоторые из этих журналов также в просмотрщике событий Windows. Однако рекомендуется использовать журналы SQL Server для получения подробной информации.

Журналы SQL Server и их местонахождение

Если вы подключены к экземпляру SQL Server в SSMS, перейдите к Management -> SQL Server Logs. Как показано ниже, имеется текущий журнал и шесть архивных журналов (Archive#1 — Archive #6).

Метод 1: Использование расширенной процедуры xp_readerrorlog

Текущие журналы являются самыми последними файлами журнала ошибок, и вы можете использовать их для просмотра недавней деятельности с момента запуска SQL Server или ручного перезапуска файла журнала. Журнал ошибок SQL Server является текстовым файлом, хранящимся в каталоге журналов экземпляра SQL Server. Вы можете использовать расширенную процедуру xp_readerrorlog для нахождения текущего местоположения журнала ошибок.

USE master

GO

xp_readerrorlog 0, 1, N'Logging SQL Server messages', NULL, NULL,NULL

GO

Этот запрос имеет следующие параметры:

1. Файл журнала ошибок: значение 0 для текущего, 1 для Archive#1, 2 для Archive #2.
2. Тип файла журнала: значение 0 для журнала ошибок SQL Server, 1 для агента SQL Server.
3. Строка поиска 1
4. Строка поиска 2
5. Время от
6. Время до
7. Сортировка результатов — по возрастанию (N’ASC) или по убыванию (N’Desc)

Для моего демонстрационного экземпляра файл журнала ошибок находится в папке C:Program FilesMicrosoft SQL ServerMSSQL14.MSSQLSERVERMSSQLLogERRORLOG.

Метод 2: Использование функции SERVERPROPERTY()

Мы можем использовать в запросе функцию SERVERPROPERTY, и также определить местонахождение SQL Server ERRORLOG.

SELECT SERVERPROPERTY('ErrorLogFileName')  AS 'Error log location'

Метод 3: использование менеджера конфигурации SQL Server

Откройте SQL Server Configuration Manager и посмотрите параметры запуска. Местоположение файлов журнала указывается с помощью переключателя -e.

Вы можете развернуть каталог журналов и просмотреть текущий или архивные файлы журнала. Эти журналы ошибок можно открыть в текстовом редакторе, таком как Notepad или Visual Studio Code.

Конфигурирование числа файлов журнала SQL Server и их размеров

По умолчанию SQL Server поддерживает текущий и 6 архивных файлов журнала. Чтобы уточнить значение, выполните щелчок правой кнопкой на папке SQL Server Logs в SSMS и выберите Configure.

SQL Server записывает всю информацию в текущий файл журнала, независимо от размера файла журнала. В загруженной системе или в экземпляре с большим количеством ошибок вам может быть сложно просмотреть файл журнала в SSMS. SQL Server создает новый файл журнала и архивирует текущий файл в следующих случях.

1. При перезапуске службы SQL.
2. При перезагрузке журнала ошибок вручную.

Однако если вы часто перезапускаете серверы по неизвестным причинам, то можете потерять все исторические данные в архивных журналах, поскольку их поддерживается только шесть. Поскольку ошибки содержат ценную информацию, которая может помочь в решении проблем, вы можете не захотеть потерять эти важные данные. Тогда, возможно, вы захотите сохранять файлы журнала производственной системы в течение недели или даже месяца.

SQL Server позволяет сконфигурировать от 6 до 99 файлов журнала ошибок. Вы не можете указать значение меньше шести, поскольку в любом случае будет поддерживаться шесть архивных журналов ошибок.

Для изменения значения по умолчанию числа файлов журнала ошибок поставьте галочку в поле с названием “Limit the number of error log files before they are recycled”. Например, следующий скриншот показывает максимальное число файлов журнала ошибок, равное 30.

Это эквивалентно выполнению скрипта T-SQL, который использует расширенную хранимую процедуру и обновляет значение регистра.

USE [master]

GO

EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'SoftwareMicrosoftMSSQLServerMSSQLServer', N'NumErrorLogs', REG_DWORD, 30

GO

Замечание. Следует перезапустить службу SQL, чтобы изменения вступили в силу.

Как утверждалось ранее, по умолчанию размер журнала ошибок не ограничен. Например, если вы не запускаете SQL Server в течение длительного периода и вручную не перегружаете файлы журнала, этот файл вырастет до громадных размеров. Поэтому в конфигурации журнала ошибок показано значение 0, соответствующее неограниченному размеру журнала.

Вы можете задать размер в Кб для ограничения размера журнала ошибок в соответствии с вашими требованиями. Например, здесь мы ограничиваем размер файла журнала в 1Гб.

Эквивалентный скрипт T-SQL обновляет ErrorLogSizeInKb в регистре SQL Server.

USE [master]

GO

EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'SoftwareMicrosoftMSSQLServerMSSQLServer', N'ErrorLogSizeInKb', REG_DWORD, 1048576

GO

Перезагрузка журналов ошибок вручную

SQL Server позволяет вручную перегружать журналы ошибок для эффективного управления ими. Например, предположим, что вы увеличили число файлов журнала ошибок до 30. Тогда мы можем создать задание для агента SQL Server, который перегружает журналы ошибок в полночь. Тем самым мы имеем файл журнала ошибок на каждый день, если SQL Server не будет перезапущен в этом промежутке. Для перезагрузки вручную выполните системную хранимую процедуру sp_cycle_errorlog. Эту процедуру может выполнить пользователь с фиксированной серверной ролью sysadmin.

EXEC sp_cycle_errorlog

GO

Файл журнала SQL Server Agent

Агент SQL Server также имеет отдельный журнал ошибок, подобный журналам SQL Server. Вы можете обнаружить его в папке SQL Server Agent – > Error logs.

Щелкните правой кнопкой на папке Error log и выберите команду Configure. Это даст местоположение журнала ошибок агента и уровень журнала агента.

Файл журнала агента имеет расширение *.OUT и хранится в папке log при конфигурации по умолчанию. Например, в моей системе файл журнала находится здесь: C:Program FilesMicrosoft SQL ServerMSSQL14.MSSQLSERVERMSSQLLogSQLAGENT.OUT.

По умолчанию в файл журнала записываются ошибки и предупреждения; однако мы можем включить информационные сообщения:

• Предупреждения: Эти сообщения предоставляют информацию о потенциальных проблемах. Например, “Job X was deleted while it was running” (задание Х было удалено во время выполнения).
• Сообщение об ошибках: оно дает информацию, которая требует немедленного вмешательства администратора баз данных, например, невозможность почтового сеанса.

Чтобы добавить информационное сообщение, поставьте галочку в поле Information.

SQL Server использует до 9 файлов журнала агента SQL Server. Имя текущего файла SQLAGENT.OUT. Файл с расширением .1 указывает на первый архивный журнал ошибок агента. Аналогично расширение .9 указывает на 9-й (самый старый) архив журнала ошибок.

Файлы журнала агента SQL Server перегружаются всякий раз, когда перезапускается SQL Server Agent. Для того, чтобы сделать это вручную, выполните щелчок правой кнопкой на папке Error Logs folder и выберите Recycle.

Или используйте хранимую процедуру sp_cycle_agent_errorlog для перезагрузки файлов журнала агента SQL Server вручную.

USE msdb ;  

GO  

EXEC dbo.sp_cycle_agent_errorlog ;  

GO

Хранимая процедура архивирует текущий журнал ошибок агента, используя следующий процесс:

• Создается новый текущий журнал ошибок агента.
• Текущий журнал ошибок SQLAgent.out преобразуется в SQLAgent.1.
• SQLAgent.1 преобразуется в SQLAgent.2

Заключение

Файл журнала ошибок SQL Server содержит информацию, предупреждения и критические сообщения экземпляра. Это полезно для решения проблем, аудита входа (успешно, отказ). Администратор базы данных может сконфигурировать требуемое число архивных журналов ошибок и каталогов для хранения этих файлов.
Вы должны регулярно просматривать записи в журнале в качестве подготовки ежедневных или еженедельных отчетов о состоянии сервера.

title	description	author	ms.author	ms.date	ms.service	ms.subservice	ms.topic	helpviewer_keywords	monikerRange
Viewing the SQL Server Error Log	Get help detecting problems in SQL Server by viewing the current error log, or backups of previous logs, to check whether processes have finished successfully.	markingmyname	maghan	03/14/2017	sql	tools-other	conceptual	cycling SQL Server error log viewing SQL Server error log errors [SQL Server], logs SQL Server error log displaying SQL Server error log logs [SQL Server], SQL Server error logs	>=sql-server-2016

[!INCLUDE SQL Server Windows Only]
View the [!INCLUDEssNoVersion] 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 [!INCLUDEssNoVersion] has been stopped and restarted), kernel messages, or other server-level error messages.

View the [!INCLUDEssNoVersion] error log by using [!INCLUDEssManStudioFull] or any text editor. For more information about how to view the error log, see Open Log File Viewer. By default, the error log is located at Program FilesMicrosoft SQL ServerMSSQL.nMSSQLLOGERRORLOG and ERRORLOG.n files.

A new error log is created each time an instance of [!INCLUDEssNoVersion] is started, although the sp_cycle_errorlog system stored procedure can be used to cycle the error log files without having to restart the instance of [!INCLUDEssNoVersion]. Typically, [!INCLUDEssNoVersion] 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.

Be aware that you can also view the [!INCLUDEssNoVersion] error log on instances of [!INCLUDEssNoVersion] that are offline or cannot start. For more information, see View Offline Log Files.

Ingenious Guide to View Log File of SQL Server

Microsoft SQL Server application is one of the biggest waves in the relational database management system and handles huge databases in a well-structured manner. However, when it comes to viewing SQL Server log files, some users face trouble. We are sure that you must be aware of how good the MS SQL Server database is for both beginners as well as expert users.

But, Digital Crimes Can Takes Place In SQL Server Too!!!!

Nowadays, Ex-employees or hackers intentionally modifies the values of databases in order to damage the organization assets. And it becomes difficult to analyze or examine who is the culprit manually. As a result, the Organizations run into  big trouble.

Hold On!! There Is A Good News

In SQL Server, there is a transaction Log file that keep records of all transactions & modifications in database executed on a database in a Microsoft SQL Server. By reading the Log file, one can easily check who deleted data from table in SQL Server database. Plus, it is used by forensic investigator to examine SQL Server Transaction Log and view & check every log detail in a detailed manner. In short, with SQL Log file, it becomes easy to find out which query performed on which table at what time.

Here, we are going to answer how to view log file of SQL Server by using various workarounds. Just go through this article once and understand how to open or read transaction log file in Microsoft SQL Server 2017 / 2016 / 2014 / 2012 / 2008 / 2008 R2 / 2005.

Moreover, if user want to restore the deleted query from a log file, then they can go through this blog – How to Recover Data from Log file in SQL Server – A Complete Guide .

Now, first of all, users need to find out the location of the log files. Then only they can proceed with a desired solution. So, to understand where your log files are, simply follow the below steps:

• Open SSMS & Connect to SQL Instance.
• Go to Management >> SQL Server Logs.
• Now, View the Log & Archive Logs simply.

Methods Use For How to View Transaction Log File of SQL Server

In the following section, you will understand how to open, check and read transaction file to retrieve information about the data which had been altered.  Before we move on towards the solution, let’s look at the ways to find the location of SQL logs easily. So, let’s get started!!

As users saw the location of the log files, let’s move ahead. From the start of SQL Server or manual log file recycling, users can use these logs to view recent activities.

1st Method – xp_readerrorlog() function

Users can use the extended xp_readerrorlog process to find the current location of the log file.

USE master
GO
xp_readerrorlog 0, 1, N'Logging SQL Server messages', NULL, NULL,NULL
GO

Now, we have some predefined guidelines for this procedure as mentioned below:

• End-time
• from time
• Search string1
• Search string 2
• Sort results – Ascending (N’ASC) or descending (N’Desc)
• Error log file: value 0 for the current, 1 for Archive#1, 2 for Archive #2
• Logfile type: Value 0 for SQL Server error log, 1 for SQL Server Agent log

Just as an example: The log file location is C:Program FilesMicrosoft SQL ServerMSSQL14.MSSQLSERVERMSSQLLogERRORLOG. Now users can see these files easily.

2nd Method – SERVERPROPERTY() function

Search the SERVERPROPERTY query to find the location of your SQL Server Log files.

SELECT SERVERPROPERTY('ErrorLogFileName')  AS 'Error log location'

#Approach 1: Use Log File Viewer in SQL Server Management Studio

Basically, this method exclusively used to open and view the information about following logs in SSMS:

• Audit Collection
• Database Mail
• Job History
• Data Collection
• SQL Server
• SQL Server Agent
• Windows Events

Its prime function of Log File Viewer is to provide the report of activities taken place in SQL Server Management Studio. In fact, one can open the Log File Viewer wizard in different ways on the basis of  information that you want to check. Now, go through the instructions to view log details in SQL Server.

How to View Log File of SQL Server Via. Log File Viewer

Step-1. Open Microsoft SQL Server Management Studio application. Here, we are using SQL Server 2014 environment for reading SQL Server Error Log.

Step-2. Connect to Server windows pops-up. Here, you need to select the Server Name and Type of Authentication. Afterward, click on Connect.

Step-3. In Object Explorer, go to Management as shown in the screenshot to examine or read log file of SQL Server 2014.

Step-4. Now, move to SQL Server Logs option.

Step-5. Now, Right-click on SQL Server Logs and select View >> SQL Server Log sequentially.

Step-6. All the Log summary displayed on Log File Viewer window. Here, you can select other logs such as SQL Server Agent , Database Mail from the left panel to check its information too.

#Approach 2: View Log File of SQL Server Via. Undocumented fn_dblog()

Originally, the function fn_dblog() is used to extract data from Transaction file of SQL Server for forensic purposes to analyze every log event performed on the table. So, let’s check out how to read transaction log file in Microsoft SQL Server 2017 / 2016 / 2014 / 2012 / 2008 / 2008 R2 / 2005 editions.

Steps to View Log File in SQL Server Using Fn_dblog()

Step 1: We have a table named as ‘Employee’. So, first view the values of the table using the following T-SQL.
Select * from employee.

Step 2: Afterward, alter the table data using update command. For this, execute the query;
Update employee set department ='IT' where emp_name = 'jeevan'

Step 3: Again, view the table values using the Select Query. Now, you can see a modified table.

Step 4: Run the fn_dblog function according to the need. Here, we execute the query to check out the time when update operation was executed.

Select [Begin Time], [Transaction Name] from fn_dblog(null , null) where [Transaction Name] = ‘Update’

Step 5: In a situation, when you want to analyze all the logs such as Delete etc. , then run the following T-SQL query.

Select [Begin Time], [Transaction Name] from fn_dblog(null, null)

However, there are some consequences attached with fn_dblog(). Actually, this function only provide the time of the query when it was committed instead of which data entry gets affected. Due to which, it becomes cumbersome to find out which table data get altered.

This problem is overcome with the third technique where user can view the log file of SQL Server without any hassle. Apart from this, both the described technique can run in SQL Server Management Studio only. You cannot read a Transaction Log File in offline environment with Log File Viewer and Fn_dblog().

#Approach 3: Use  Smart Solution to Analyze Transaction File Easily

To get exact information from SQL Log File, take the help of SysTools SQL Log Reader Software. With the help of this software, user can scan and analyze T-log file in human readable format. However, the tool works in Online as well as Offline environment. User can get the information like Transaction , Login Name , Time , Table Name , Query . It is a best software solution that answers the question – how to read SQL Server Transaction Log file.

Download Free SQL Log Analyzer

Related : How to Fix Log File Corruption – Step-By-Step Guide

In fact, after viewing the log file of SQL Server, user can export the query in Live SQL Server database environment , SQL Compatible Scripts , and in CSV format. Moreover , the software can read Transaction log file of every SQL Server edition.

Conclusion

That’s all about how to View Log file of SQL Server. Now, go through the above-mentioned methods and opt for the best that is suitable for you and examine the SQL Server Transaction Log file without any hassles at all. Make sure that you are confident while executing the manual method or simply go for the automated solution.

Also Read: SQL Server Transaction Log Forensics

Frequently Asked Questions

Most applications have a log of some sort that tracks activity. SQL Server is no different. It has a log file known as the error log for tracking what’s happening on an instance of SQL Server. Server. Each instance of SQL Server has its own set of error log files. In this article, I will discuss what the error log is, how SQL Server manages it,  and where you can find it. I’ll also show you how to configure the SQL Server error log. 

What is the error log?

The error log is a file that tracks what kind of activity is happening on an instance. The log is just a journal of events that have occurred on a SQL Server instance, in chronological order. The log doesn’t track everything, but it does track significant events and errors that occur while SQL Server is running. 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. A DBA can also configure SQL Server to write additional logging, for example, logins and logouts. 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.

Where can the error log files be found?

By default, the error log files are stored in the following location: Program FilesMicrosoft SQL ServerMSSQL<n>.<instance name>MSSQLLOGERRORLOG, where <n> is an archived version number, and <instance name> is the name of the instance. 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.

The first method is to use SQL Server Configuration Manager. To find the location of the log using this method, first, open up SQL Server Configuration Manager. 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.

Figure 1: Location of error log file directory in Configuration Manager

A second method to find the location of the error log files is to use SSMS to browse one of the error log files. To do this, you must connect to the instance of SQL Server in which you want to find the error log location with SSMS. Expand the Management item and the SQL Server Logs item. 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. When using the search option, use the string Logging SQL Server messages in file as the search criteria. The image in Figure 2 shows these steps. The log location can be found in the line that is highlighted in the log.

Figure 2: Using SSMS to find the location of error log files

Types of error log files and their naming conventions

Each SQL Server instance has two different types of error log files. There is an error log file for the currently running instance and then a number of archived error log files.

All error log files names start with ERRORLOG. The error log for the running instance is called just ERRORLOG, whereas all other error log files names start with ERRORLOG but have a number for the file extension. The archived error log files are ERRORLOG.1, ERRORLOG.2, ERROLOG.3, … up to the number to the configured number of archived error log files. Where ERRORLOG.1 is most recently archived error log file, ERRORLOG.2 is the second most recently archived error log file, ERRORLOG.3 is the third most recently archived error log file, etc. If an instance is configured with the default number of error log files, then the last archived error log file is named ERRORLOG.6.

When an instance is restarted, the oldest archived error log file (ERRORLOG.6 if the default number is used) is deleted, then each of the remaining error log files is renamed. ERRORLOG.5 is renamed to ERRORLOG.6, ERROLOG.4 is renamed to ERRORLOG.5, etc. until ERRORLOG.1 is renamed to ERRORLOG.2. The last current error log file (ERRORLOG) is renamed to ERRORLOG.1, and the error log file for the newly started instance is created with the name ERRORLOG.

Changing the number and size of the error log

By default, an instance of SQL Server retains six archived error log files, and the size of each log file is unlimited. An unlimited size means it will grow as big as it needs to be, or until it runs out of disk space. You may find that these default setting are fine, but they can also be overridden.

How many archived error log files do you need and what is an appropriate maximum size? Like most SQL Server questions, the answer is “it depends.” Here, I will demonstrate how the number and size of the error logs might help or hinder your use of the error log files and share my personal opinion of how many log files I like to have.

First, let me explore the number of archived log files. I like to retain at least two months’ worth of error log files. By having multiple months of error log information, I can track trends from one month to the next. Keep in mind that depending on what is logged and the activity of an instance, two months’ worth of error log files might take up a lot of disk space.

There is no easy way to keep exactly two months’ worth of error log files since a new error log is created and the oldest archived one is deleted every time an instance is restarted. The number of months of error log files depends on how often SQL Server is restarted and the number of archived error log files that are kept. Suppose an instance is recycled frequently over a short period of time due to some maintenance activity. Error log information will be cycled off based on the configured value for the number of archived error log files that should be retained.

There a few different methods to change the error log file configuration, I’ll mention two of them. The first method is to use SSMS. To use this method, first connect to the instance, expand the Management folder, right click on the SQL Server Log folder and then select the Configure item from the menu displayed, as shown in Figure 3.

Figure 3: Bringing up the error log configuration screen

When clicking on Configure, the window shown in Figure 4 is displayed.

Figure 4; Configuration Options for the Error Log

By reviewing Figure 4, you can see that my instance is currently configured to retain 6 archived error log files and the maximum size of the error log file is set to be 0, which means unlimited.

To change the number of archived error log files to retrain, first click on the checkbox labeled Limit the number of error log files before they are recycled. Doing this will enable the Maximum number of error log files option, so the number of error logs to retain can be changed. Note the number of error log files to retrain needs to be between 6 and 99.

To set a maximum limit of each error log file, enter the desired size into the Maximum size of the error log file in KB. When the error log configuration is set to limit the size of the error log file, a new error log file will be created automatically after the current log file reaches the maximum size. I personally don’t like to limit the size of the log file, but keep in mind, having an unlimited size setting means each log file will be different sizes, whereas limiting the size would make every error log file the same size.

Issues with multiple restarts

When SQL Server is restarted, the oldest archived error log file is deleted, and a new one is created. This cycling of the error log every time SQL Server restarts normally isn’t a problem, unless it deletes an archived error log file automatically that contains information that is needed for troubleshooting or trend reporting.

Since the log file contains messages and errors related to an instance, it is crucial not to lose this information until you no longer need it. The problem with having the default number of archived error log files is you might lose valuable troubleshooting information if an instance is restarted more than six times over a short period of time. This can happen for a number of different reasons. I have seen nighttime operations staff and DBAs restart an instance multiple times in an attempt to get an instance that has crashed back up and running. Suppose an instance of SQL Server is restarted multiple times without review of the error log files. The restarts might inadvertently cycle off the one critical error log file containing the error messages related to the crash. For this reason, it is important to configure SQL Server to retain enough archived error log files so they are not automatically deleted until they are no longer needed.

Keep in mind that cycling off a critical error log file is a rare situation. However, if an error log file that has valuable information about a crash is cycled off, it does hamper your troubleshooting options. Therefore, it is wise to have someone review the error log, or at least copy the error log file to a backup location after a crash to make sure you have all the log files to help with troubleshooting. At least by copying off the error log file after a crash, you can guarantee it will not be cycled off if lots of restarts are performed.

Cycling the ERRORLOG

A new error log is created every time SQL Server is restarted. Sometimes SQL Server stays up for weeks or even months before an instance is restarted. When an instance has been running for a long time, the error log will be quite large. It’s hard to review and find errors in large log files, especially when they contain error messages that could cover long periods of time.

To help keep the error log files from spanning months’ worth of messages without recycling, you can cycle the error log with a command. Cycling the error log will close the current log and create a new one. The code in Listing 1 uses the system stored procedure sp_cycle_errorlog to cycle the error log file.

Listing 1: Cycling the errorlog.

To execute the sp_cycle_errorlog system stored procedure, you need to be a member of the sysadmin fixed server role. Stored procedure sp_cycle_errorlog takes no parameters and returns a 0 if the error log is successfully recycled and a 1 if the cycling of the log has failed.

Error log file

The error log file contains messages and errors associated with a SQL Server instance. It is a valuable tool for troubleshooting problems with SQL Server as well as auditing what goes on within an instance of SQL Server. A DBA can configure SQL Server to store the error logs wherever they’d like and adjust the configuration to keep as many archived error log files as needed. As a DBA you should review this log every time SQL Server crashes and as part of your daily and weekly system checks. This review will ensure that SQL Server is functioning normally, with no unexpected errors showing up in the log.