Журнал ошибок sql server 2012

Пересказ статьи 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 содержит информацию, предупреждения и критические сообщения экземпляра. Это полезно для решения проблем, аудита входа (успешно, отказ). Администратор базы данных может сконфигурировать требуемое число архивных журналов ошибок и каталогов для хранения этих файлов.
Вы должны регулярно просматривать записи в журнале в качестве подготовки ежедневных или еженедельных отчетов о состоянии сервера.

Автор: Alexey Knyazev

Сегодня я расскажу про недокументированные расширенные хранимые процедуры (Extended Stored Procedures) для работы с журналом ошибок SQL Server и SQL Server Agent.
На самом деле при просмотре журнала SQL Server Logs через SSMS (SQL Server Management Studio) идёт обращение именно к этим двум основным процедурам (xp_readerrorlog и xp_enumerrorlogs), хоть и не на прямую, а через системные интерфейсные процедуры.
Особое внимание я уделю описанию входных параметрам этих недокументированных процедур.

И так, что же происходит, когда мы просматриваем журнал ошибок через SSMS?

Во первых мы определяем путь к нашей папке с журналами

Затем выводим информацию о текущем журнале и о шести предыдущих

в качестве входного параметра можно указать

• 1 — Список журналов SQL Server (значение по умолчанию)
• 2 — Список журналов SQL Server Agent

Если обратиться к тексту этой процедуры, то можно увидеть, что это не более, чем «обёртка» для вызова другой процедуры

Теперь если мы нажмём View SQL Server Log

то мы сможем просмотреть все события выбранного журнала:

В этот момент идёт обращение к ещё одной процедуре-обёртке master..sp_readerrorlog.
Ниже текст этой процедуры:

• @p1 — номер журнала (0-6); 0 — текущий
• @p2 — чей журнал; 1 — SQL Server, 2 — SQL Server Agent
• @p3 — фильтр для поиска
• @p4 — второе условие для поиска

Пример:

Но это ещё не всё. Если обратиться к самой расширенной процедуре xp_readerrorlog, то у неё есть ещё несколько параметров:

• @p5 — условие с какой даты выводить результат
• @p6 — условие до какой даты выводить результат из журнала
• @p7 — тип сортировки (asc/desc)
• @p8 — экземпляр SQL Server (@InstanceName), параметр появился в SQL Server 2012

Пример:

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

Запись опубликована в рубрике В помощь администратору с метками error log. Добавьте в закладки постоянную ссылку.

В процессе функционирования SQL Server ведет журнал, в котором регистрирует события, связанные с работой сервера. В документации этот журнал называется Error Log, что не вполне соответствует действительности. Правильнее было бы именовать его Event Log, так как в нем отмечается множество событий различного характера из разных источников, включая системные информационные и системные аварийные события, сообщения аудита регистрации и пользовательские сообщения (если сравнивать с операционной системой, то в Windows NT/2000 события регистрируются в трех журналах — Application, Security и System).

В документации по SQL Server отсутствует систематизированное изложение вопросов, связанных с Error Log, — сведения разбросаны по разным разделам, а часть информации, относящаяся к применению Transact SQL при работе с журналом, и вовсе отсутствует. Данная статья призвана в какой-то мере восполнить этот пробел в документации и помочь администраторам баз данных и разработчикам приложений научиться полностью использовать возможности журнала Error Log.

Что такое Error Log

При каждом запуске SQL Server начинает новый журнал Error Log, который представляет собой текстовый файл, расположенный по умолчанию в каталоге MSSQLLog. Файлы журналов, созданные при предыдущих запусках, не удаляются, а просто переименовываются. Текущий файл журнала имеет имя Error Log, файл предыдущего запуска Error Log.1 и т. д. По умолчанию SQL Server хранит файлы шести предыдущих журналов. Проще всего просмотреть журналы в Enterprise Manager (см. Рисунок 1). Изменить местоположение файлов журнала можно, используя параметр -e при старте сервера. В Enterprise Manager это делается через контекстное меню SQL Server properties: закладка General — Startup Parameters (см. Рисунок 2).

Рисунок 2. Установка параметров запуска SQL Server.

Число сохраняемых журналов устанавливается в Enterprise Manager c помощью контекстного меню SQL Server Logs Configure (см. Рисунок 3). Можно сохранять не более 99 журналов предыдущих запусков.

Рисунок 3. Установка параметров Error Log.

При запуске SQL Server в журнал заносится следующая информация:

• дата и время запуска;
• используемые версии SQL Server и Windows NT/2000 с учетом Service Pack;
• системный приоритет SQL Server и число процессоров в системе;
• информация о подключении системных и пользовательских баз данных;
• используемые сетевые библиотеки;
• сведения о готовности SQL Server к работе с клиентскими соединениями.

Если при запуске SQL Server возникли проблемы, можно с помощью любого текстового редактора прочитать файл Error Log и, возможно, обнаружить причину неудачи. При завершении работы сервер записывает в журнал причину этого события — либо была остановлена служба MSSQLSERVER (Server shut down by request), либо прекращена работа операционной системы (SQL Server terminating because of system shutdown). Если таких сообщений нет (естественно, не в текущем журнале), это означает, что работа компьютера не была завершена должным образом, например по причине аппаратного сбоя. Есть еще одно сообщение, которое может быть последним в Error Log, — в случае если был принудительно открыт новый файл журнала (Attempting to cycle errorlog). Об этом мы поговорим позже.

Необходимо отметить, что по умолчанию все сообщения Error Log дублируются в журнале Windows Application Log. Двойную регистрацию событий можно отменить, запустив SQL Server с параметром -n. При этом нужно обязательно указать параметр -e, иначе события не будут записываться и в журнал Error Log.

Какие события отражаются в Error Log

Есть события, которые в обязательном порядке отражаются в журнале.

К ним относятся:

• копирование/восстановление базы данных;
• нехватка дискового пространства в базе данных;
• выполнение команды KILL.

Можно организовать аудит удачных и неудачных попыток регистрации пользователей. В Enterprise Manager это делается с помощью контекстного меню SQL Server Properties: закладка Security — Audit Level (см. Рисунок 4).

Рисунок 4. Установка аудита регистрации пользователей.

Как записывать собственные сообщения в Error Log

Собственные сообщения можно записать в журнал с помощью оператора RAISERROR или хранимой процедуры xp_logevent.

В RAISERROR для записи в журнал используется параметр WITH LOG :

RAISERROR ('Ошибка при добавлении
данных!',10,1) WITH LOG

Хранимая процедура xp_logevent специально предназначена для записи сообщений в Error Log:

exec master..xp_logevent 60000,
?посылка почты?,ERROR

Первый параметр — код сообщения, он должен быть больше 50 000. Последний параметр может принимать значения INFORMATIONAL, WARNING или ERROR. Необходимо обратить внимание на то, что xp_logevent, в отличие от RAISERROR, не посылает сообщение клиентской программе и не изменяет значения глобальной переменой @@ERROR. Для xp_logevent также нужна настройка прав на выполнение.

Хранимые процедуры для работы Error Log

Хранимая процедура sp_enumerrorlogs служит для получения полного списка журналов с указанием даты окончания записи в них и их объема. Хранимая процедура sp_readerrorlog читает журнал с указанным номером. Если номер не указан или номер 0, читается текущий журнал. Результаты выполнения названных процедур приведены на Рисунке 5.

Обе процедуры возвращают в качестве результата таблицы, структуры которых приведены ниже при описании примера работы с Error Log. Хранимая процедура sp_cycle_errorlog служит для принудительного открытия нового файла журнала. Необходимость открыть новый файл журнала может быть вызвана, например, большим объемом текущего журнала в результате длительной непрерывной работы сервера (журнал большого объема трудно просматривать и анализировать).

Пример практического использования знаний об Error Log

Администратор базы данных должен знать, как часто происходит запуск и остановка SQL Server. Особенно эта информация полезна, если сервер физически расположен не в организации, а у провайдера услуг. При этом желательно знать общее время работы сервера и причины остановки. Сценарий, приведенный в Листинге 1, решает эту задачу, используя информацию из Error Log.

Итог исполнения сценария на тестовом сервере приведен на Рисунке 6. Здесь показано, что первый, второй, пятый и шестой журналы были закрыты по причине остановки Windows, третий журнал — в результате остановки службы SQL Server, а четвертый — принудительно хранимой процедурой sp_cycle_errorlog.

В текущем журнале последним событием является попытка неудачной регистрации пользователя.

Ильдар Даутов — MCT, MCDBA, начальник отдела АКБ «Заречье», dia@zarech.ru.

The SQL Server Error Log is one of the most useful thing to troubleshoot database server issues. System errors get logged here along with a host of other useful information.

Read on to see how to understand, analyze, and manage the SQL Server Error Log…

Why Should You Care About Reading This Log?

So why should you care at all about the error log? While it is true that the Windows Events Logs contains many useful bits of information, the more specific errors get written to the SQL Server error log. Are you troubleshooting issues on your SQL Instance? If so then reading and understanding this log will immensely help.

The SQL Server Error Log:

• Wealth of information about the SQL Instance
• A great source of troubleshooting information
• The answers are often in the logs – you just have to read them

RTFM – The Truth is Out There!

There is a very old retort that has been around since the early Unix days for when n00bs ask questions on message boards: RTFM. Developers can be harsh spoken and system administrators can be thick skinned. Learning a programming language, for example, is a tough task. There become a lot of elementary questions asked and some people get turned off by the curt responses. The truth of the matter is that there is no substitute for reading the documentation.

Professionals who contribute to forums don’t want to do your homework or provide free consulting for such basic things. You really need to put in some work first and then present what you’ve got and ask your question.

There is no substitute for reading logs to understand behavior

Reading logs is not sexy. For non-robots it can be boring and tedious. However the SQL Server error log is a goldmine of useful information to troubleshoot your SQL Server Instance. Are you getting crash dumps? Deadlocks? Connectivity issues? Services crashing? Corruption? All of these can be discerned from analyzing the error log. You would do well to have this skill!

Here’s my favorite xkcd comic showing what happens when you don’t read the documentation:

Anatomy of SQL Server Error Log

Now let us examine the parts which comprise the error log. 

There are a few ways to find and view the error log.

1. SSMS
2. T-SQL
3. File System

SQL Server Management Studio

In SSMS you can find it in the object browser. Here it is:

From here you can double click on the current error log and open it up. You will see something like this:

You can filter the log as such:

The output will look like this:

Go ahead and export to a file. Play around with it and get familiar with reading these logs.

T-SQL

SQL Server 2005 introduced a new undocumented command: sp_readerrorlog. This allows us to query the error log which can be powerful.

A word of caution – undocumented means Microsoft can change or remove it anytime and does not provide support for this command.

It is a wrapper around an extended procedure called xp_readerrorlog. You can know this by querying:

--show definition of procedure
exec sp_helptext sp_readerrorlog

Output:

Here are some examples of querying the error log:

exec sp_readerrorlog; --show the full most recent error log
exec sp_readerrorlog 1; --choose the next youngest log (default is 0 meaning current)
exec sp_readerrorlog 0, 1 --show current error log with log file type (same as the first command)
exec sp_readerrorlog 0, 1, 'error'; --grep current error log for keyword
exec sp_readerrorlog 0, 1, 'error', 'logging'; --an additional search parameter to refine

File System

My preferred method of accessing and analyzing the error log is by way of the file system.

How do we know where the file is? By default, the error log is located at Program FilesMicrosoft SQL ServerMSSQL.nMSSQLLOGERRORLOG and ERRORLOG.n files. Sometimes a DBA will move it and we need to look harder. You can always just search the file system in Windows Explorer for “ERRORLOG”.

Using the SQL Server Configuration Manager:

You see the services running for SQL Server. Right click on the service for your instance and look at the startup parameters. This is the path where your log files are likely to be.

Additionally you can query the log location in T-SQL:

--query error log location
SELECT SERVERPROPERTY('ErrorLogFileName') AS 'Error log file location';

Structure of Error Log

Besides errors you can learn a lot about an instance by reading some of the beginning lines.

Just by reading the first 35 lines I can discern:

• The exact version and build of SQL Server
• The OS version and build
• Time zone
• Authentication mode
• Default locations for log files
• CPU recognized
• Memory allocated
• Collation
• IFI

If you knew nothing about a server this can tell quite a lot!

How to Manage the Error Log

There are 2 options to be aware of when managing the error log:

1. Cycling the log
2. Controlling the size of the log

By default the error log will create 7 log files to cycle through. When you add an eighth it drops the oldest one and renames the rest accordingly. The first “ERRORLOG” is the current one always.

Every time the SQL Server service is restarted a new error log file is created

Cycling the Error Log

We can cycle the error logs (meaning create a new one and push the last out of the queue) by using sp_cycle_errorlog:

--cycle the error log
exec sp_cycle_errorlog;

Here are the log files before running the command:

…and after:

NOTE: I accidentally ran it twice – that is why there are 2 new logs created.

Under the covers this command runs dbcc errorlog.

Controlling the Size of the Error Log

To change the size that an error log file can be you need to make a change in the registry. The Microsoft documentation shows the key we are interested in:

HKEY_LOCAL_MACHINESOFTWAREMicrosoftMSSQLServerMSSQLServerNumErrorLogs

PROTIP: Always back up the registry BEFORE making any changes!

Understanding the Error Log

Some of these logs can get very big and appear hard to analyze. You need to know the important bits to examine and search. I like to do it this way:

• Find and open the Error Log in Notepad++ (or your favorite text editor)
• Ctrl + F to enter a search term
• Click “Find All in Current Document”

Now you can easily analyze the file. Double click on the line in the output to go straight to it in the file.

Notice you can use regular expressions – they are your friend when searching text.

I like to search out the following terms on the regular:

• severity:
• crash
• dump
• deadlock
• victim
• fatal

Searching on “severity:” tends to show useful output

Don’t just search for “error” because you will get a lot of false positives that you may not care about. I have the best luck with “severity:” because I can evaluate what is important and it reduces noise.

Well there you have it. Get your hands dirty in the log and you will level up your skills.

Thanks for reading!

If you liked this post then you might also like: Stop and Think Before You Shrink – Shrinking a SQL Server Database