September 2008

You are currently browsing the monthly archive for September 2008.


Сегодня утром на одном из серверов произошёл “казус” с пользовательской базой данных, которую мои коллеги пытались перевести в OFFLINE, но процесс пошёл не штатно, база попала в переходное состояние, став недоступной и затруднив при этом мониторинг активности других пользовательских и системных баз… Ситуация точно такая же, какая была описана Полом Ибисоном в группе новостей: OFFLINING and “Database ‘xxx’ is in transition. Try the statement later.”

Проблема проявляется тем, что любые попытки подключиться к объектам базы ХХХ заканчиваются следующим сообщением об ошибке:

    "Database 'ХХХ' is in transition. Try the statement later."

В журнале ошибок SQL Server есть сообщение типа:

    Дата 26.09.2008 8:47:40
    Журнал SQL Server (Текущий - 26.09.2008 8:51:00)
    Источник spid131
    Сообщение Setting database option OFFLINE to ON for database ХХХ.

Обращение к системным метаданным построенное в виде сценария:

select * from sys.dm_exec_requests ORDER BY command

Возвращает в числе первых процесс, в колонке command которого фигурирует инструкция ALTER DATABASE.

Рецепт, который подсказывает Пол, простой, но действенный. Посмотрите идентификатор процесса, в контексте которого исполняется инструкция ALTER DATABASE, и удалите этот процесс с помощью команды KILL.

Для того чтобы исключить возможность возникновения описываемой проблемы, Пол предлагает предварительно переводить базу данных в однопользовательский режим READONLY или в SINGLE_USER.
Например так:

ALTER DATABASE [ХХХ] SET SINGLE_USER WITH ROLLBACK IMMEDIATE


Начиная с SQL Server 2005, на службе DBA появилась такая замечательная возможность, как Event Notifications, что в русской версии BOL принято называть уведомлением о событиях. Этот механизм позволяет включить незаметную трассировку системных событий и извлекать информацию о заданных события из очереди для анализа или реакции со стороны администратора. Полный список событий, которые таким образом можно отслеживать, можно найти в статье: События трассировки для использования с уведомлениями о событии


Давайте рассмотрим упрощённый пример организации уведомлений о тупиковой блокировке и сделаем так, чтобы по электронной почте можно было получить письмо с графом блокировок.


Подготовительные действия


Для начала настройте на тестовом сервере Database Mail и убедитесь, что всё работает правильно. С возможными проблемами с почтой поможет статья: Устранение неполадок в работе компонента Database Mail


У меня с настройками возникла только одна проблема, пришлось учесть, что вызов системной хранимой процедуры в следующем ниже примере будет выполняться не в том контексте, в котором я привык работать :) В общем, пришлось на время сделать её доступной всем желающим. Вот один из способов это сделать:


USE MSDB Go GRANT EXECUTE ON msdb.dbo.sp_send_dbmail TO public GO


Для того, чтобы задействовать очереди событий для интересующих нас баз данных, нужно включить компонент Service Broker. Как это сделать для базы AdventureWorks показано в следующем сценарии:


USE master GO ALTER DATABASE [AdventureWorks] SET ENABLE_BROKER WITH NO_WAIT -- с базой должен работать брокер SELECT name, is_broker_enabled FROM sys.databases -- проверка, что брокер включён GO


Если в ответ на это действие вы получили сообщение об ошибке: The SQL Server Service Broker for the current database is not enabled Можно включить брокера принудительно, например, так:

ALTER DATABASE [AdventureWorks]SET NEW_BROKER WITH ROLLBACK IMMEDIATE GO ALTER DATABASE [AdventureWorks] SET ENABLE_BROKER GO

 

Создание очереди, службы, сообщения и маршрута


В качестве места базирования наших очередей и сообщений мы выберем системную базу данных tempdb. Она есть на любом сервере и Service Broker для неё включён по умолчанию. Первым делом мы заготавливаем процедуру, которая будет вызываться при регистрации интересующего нас события:


USE tempdb GO CREATE PROCEDURE [p_Notify] AS DECLARE @subject nvarchar(128), @body nvarchar(MAX) DECLARE @NotificationStore TABLE (message_body varbinary(MAX)); -- извлекаем сообщения из очереди и помещаем их в табличную переменную RECEIVE TOP (1) message_body FROM tempdb..DEADLOCK_Queue INTO @NotificationStore -- собираем тему и содержимое электронного письма SELECT TOP (1) @body = CAST(message_body AS nvarchar(MAX)) FROM @NotificationStore SELECT @subject = CAST(CURRENT_TIMESTAMP AS varchar) + ' Случился DEADLOCK' IF @body IS NOT NULL EXEC msdb.dbo.sp_send_dbmail @profile_name = 'ПОЧТОВЫЙ ПРОФИЛЬ' -- профиль по умолчанию и не скрытый ,@recipients = 'АДРЕСАТ@ХОСТ.ru' -- адресат ,@subject = @subject ,@body = @body GO -- создаём очередь CREATE QUEUE DEADLOCK_Queue WITH STATUS = ON ,RETENTION = OFF ,ACTIVATION ( PROCEDURE_NAME = tempdb..p_Notify, MAX_QUEUE_READERS = 1, EXECUTE AS 'dbo' ) GO -- создаём службу CREATE SERVICE DEADLOCK_Notify ON QUEUE DEADLOCK_Queue ([http://schemas.microsoft.com/SQL/Notifications/PostEventNotification]) GO -- определяем локальную маршрутизацию CREATE ROUTE DEADLOCK_Route WITH SERVICE_NAME = 'DEADLOCK_Notify', ADDRESS = 'LOCAL'; GO -- создаём уведомление уровня сервера для событий появления графа тупиковой блокировки CREATE EVENT NOTIFICATION DEADLOCK_Event ON SERVER FOR DEADLOCK_GRAPH -- если нужны все виды сообщений о блокировках, используйте TRC_LOCKS TO SERVICE 'DEADLOCK_Notify', 'current database' -- проверка SELECT * FROM sys.server_event_notifications WHERE name = 'DEADLOCK_Event'; SELECT * FROM sys.server_events GO


После того, как представленный выше сценарий будет успешно применён на тестовом сервере, будет создано всё необходимое для получения почтовых сообщений. В теле письма будет правильный XML, у которого в теге <TextData> лежит стандартный граф взаимоблокировки, который открывается тэгом <deadlock-list>. Если его вырезать и сохранить в файле с расширением xdl, то при открытии такого файла в приложении SQL Server Management Studio, вы увидите привычную схему блокировки, обычно получаемую с помощью SQL Server Profiler.


Удаление созданных для тестовых целей объектов


Удалить следы наших сценариев в базе данных tempdb помогут следующие команды:


-- DROP PROCEDURE p_Notify -- DROP EVENT NOTIFICATION DEADLOCK_Event ON SERVER -- DROP ROUTE DEADLOCK_Route -- DROP SERVICE DEADLOCK_Notify -- DROP QUEUE DEADLOCK_Queue -- ALTER DATABASE [AdventureWorks] SET DISABLE_BROKER WITH NO_WAIT


Бонус


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


USE tempdb GO ALTER PROCEDURE [p_Notify] AS DECLARE @subject nvarchar(255), @x XML, @body NVARCHAR(MAX), @query NVARCHAR(MAX), @filename nvarchar(30) DECLARE @NotificationStore TABLE (message_body varbinary(MAX)); -- извлекаем сообщения из очереди и помещаем их в табличную переменную RECEIVE TOP (1) message_body FROM tempdb..DEADLOCK_Queue INTO @NotificationStore -- собираем тему и содержимое электронного письма SELECT TOP (1) @x = CAST(message_body AS XML) FROM @NotificationStore SELECT @body = CAST(@x AS nvarchar(MAX)) SELECT @x = @x.query('/EVENT_INSTANCE/TextData/deadlock-list') SELECT @subject = CONVERT(nvarchar(19), CURRENT_TIMESTAMP, 126) + '_DEADLOCK' SELECT @filename = REPLACE(@subject,':','')+ '.xdl' IF @body IS NOT NULL BEGIN SELECT @query = N'SET NOCOUNT ON SELECT ' + N'''' + REPLACE(CAST(@x AS nvarchar(MAX)),'''','') + N'''' EXEC msdb.dbo.sp_send_dbmail @profile_name = 'ПОЧТОВЫЙ ПРОФИЛЬ' -- профиль по умолчанию и не скрытый ,@recipients = 'АДРЕСАТ@ХОСТ.ru' -- адресат ,@subject = @subject ,@body = @body ,@query = @query ,@execute_query_database = 'tempdb' ,@query_attachment_filename = @filename ,@attach_query_result_as_file = 1 ,@query_result_header = 0 ,@query_result_width = 32767 END GO


 


В тему:


Настоящие требования распространяются на тестовые и пользовательские базы данных, с размерами до 300Гб, без повышенных требований к доступности, готовности и надёжности.

Файлы пользовательских баз данных, журналов транзакций, журналов планов обслуживания и других журналов располагаются в стандартной структуре каталогов, используемой вендором. При наличии нескольких дисков (дисковых массивов), системные и серверные каталоги располагаются на диске C (в предлагаемых программой установке каталогах по умолчанию – C:\Program Files\Microsoft SQL Server), файлы данных располагаются на диске D, а файл журнала транзакций (который рекомендуется иметь один для каждой БД) располагаются на диске E. В корне каждого диска создаётся каталог MSSQL, в котором создаются подкаталоги для размещения соответствующих файлов. Для файлов данных создаётся подкаталог DATA, для файлов журналов подкаталог LOG, для файлов резервных копий подкаталог BACKUP. Нежелательно располагать на одном диске файлы с разным типом доступа, например, файлы данных и файл журнала транзакций. Для повышения живучести приложения баз данных, нежелательно располагать файлы резервных копий на одном диске с файлами данных. Например, для трёх дисков возможна такая, типовая конфигурация размещения файлов баз данных:

  • C:\Program Files\Microsoft SQL Server\ – фалы сервера баз данных, системные базы данных, журналы планов обслуживания и т.п.
  • D:\MSSQL\DATA\ – Файлы данных пользовательских баз данных.
  • E:\MSSQL\LOG\ – Файл журнала регистрации транзакций пользовательской базы данных.
  • E:\MSSQL\BACKUP\ – Файлы резервных копий пользовательских и системных баз данных.

Параметры создаваемых баз данных желательно выбирать стандартными, предлагаемыми по умолчанию. Каждое изменение стандартных значений параметров должно быть обосновано и документировано (sp_addextendedproperty).

Для размещения пользовательских данных создаётся отдельная файловая группа (группы), которая делается файловой группой по умолчанию. Предлагается называть такую группу: DATAGROUP. Делается это для того, чтобы разнести по разным файлам системные и пользовательские объекты базы данных.

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

Для размещения файлов журналов транзакций необходимо использовать защищённые от отказа диска массивы, например RAID1. Для файлов данных пользовательских БД использование защищённых массивов спецификацией требование ACID не является обязательным при условии использования полной модели восстановления и регулярного резервирования не только баз данных, но и журналов регистрации транзакций.

Для размещения файлов баз данных и особенно журналов транзакций нежелательно использовать массивы RAID5x или RAID6x, в силу крайне низкой скорости записи. Эти типы массивов можно использовать для размещения файлов резервных копий или файлов данных с малым процентом операций записи.

Как правило, SQL Server балансирует нагрузку ввода-вывода эффективнее аппаратных решений. Поэтому предпочтительно размещать базу данных на нескольких файлах и помещать файлы на собственные диски, чем размещать базу в одном файле данных и помещать его на массивы типа RAID10 или SAN. Рекомендуется, для принятия решения о целесообразности использования RAID10 или SAN проводить тестирование разных вариантов конфигурации дисковой подсистемы.

Если после создания БД планируется массовая загрузка данных, желательно при создании установить достаточный размер файлам данных и журнала транзакций. Автоматическое приращение файлов является ресурсоёмкой операцией и может существенно увеличить время загрузки данных. Кроме того, если на разделе диска находятся несколько файлов данных, размер которых может изменяться, это становится причиной файловой фрагментации данных и журналов. Для обеспечения высокой производительности работы приложений необходимо дефрагментировать файлы баз данных на регулярной основе. Уменьшение размеров файлов данных крайне не желательно, эта операция может применяться только для сокращения размера файла журнала транзакций, если это стало необходимо (например, после массовой журналируемой загрузки).

Пример сценария создания пользовательской базы данных:

    USE [master]
    GO
    CREATE DATABASE [ИмяБазыДанных] ON PRIMARY
    (      NAME = N'ЛогическоеИмяФайлаСистемныхДанных'
        ,  FILENAME = N'D:\MSSQL\DATA\ИмяФайлаСистемныхДанных.mdf'
        ,  SIZE = 131072KB
        ,  MAXSIZE = UNLIMITED
        ,  FILEGROWTH = 131072KB
    ),
    FILEGROUP [DATAGROUP]
    (      NAME = N'ЛогическоеИмяФайлаДанных'
        ,  FILENAME = N'D:\MSSQL\DATA\ИмяФайлаДанных.ndf'
        ,  SIZE = 131072KB
        ,  MAXSIZE = UNLIMITED
        ,  FILEGROWTH = 131072KB
    )
    LOG ON
    (      NAME = N'ЛогическоеИмяФайлаЖурнала'
        ,  FILENAME = N'E:\MSSQL\LOG\ИмяФайлаЖурнала.ldf'
        ,  SIZE = 131072KB
        ,  MAXSIZE = 2048GB
        ,  FILEGROWTH = 131072KB
    )
    GO
    EXEC dbo.sp_dbcmptlevel @dbname=N'ИмяБазыДанных', @new_cmptlevel=90
    GO
    IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled'))
    begin
    EXEC [ИмяБазыДанных].[dbo].[sp_fulltext_database] @action = 'disable'
    end
    GO
    ALTER DATABASE [ИмяБазыДанных] MODIFY FILEGROUP [DATAGROUP] DEFAULT
    ALTER DATABASE [ИмяБазыДанных] SET ANSI_NULL_DEFAULT OFF
    ALTER DATABASE [ИмяБазыДанных] SET ANSI_NULLS OFF
    ALTER DATABASE [ИмяБазыДанных] SET ANSI_PADDING OFF
    ALTER DATABASE [ИмяБазыДанных] SET ANSI_WARNINGS OFF
    ALTER DATABASE [ИмяБазыДанных] SET ARITHABORT OFF
    ALTER DATABASE [ИмяБазыДанных] SET AUTO_CLOSE OFF
    ALTER DATABASE [ИмяБазыДанных] SET AUTO_CREATE_STATISTICS ON
    ALTER DATABASE [ИмяБазыДанных] SET AUTO_SHRINK OFF
    ALTER DATABASE [ИмяБазыДанных] SET AUTO_UPDATE_STATISTICS ON
    ALTER DATABASE [ИмяБазыДанных] SET CURSOR_CLOSE_ON_COMMIT OFF
    ALTER DATABASE [ИмяБазыДанных] SET CURSOR_DEFAULT GLOBAL
    ALTER DATABASE [ИмяБазыДанных] SET CONCAT_NULL_YIELDS_NULL OFF
    ALTER DATABASE [ИмяБазыДанных] SET NUMERIC_ROUNDABORT OFF
    ALTER DATABASE [ИмяБазыДанных] SET QUOTED_IDENTIFIER OFF
    ALTER DATABASE [ИмяБазыДанных] SET RECURSIVE_TRIGGERS OFF
    ALTER DATABASE [ИмяБазыДанных] SET ENABLE_BROKER
    ALTER DATABASE [ИмяБазыДанных] SET AUTO_UPDATE_STATISTICS_ASYNC ON
    ALTER DATABASE [ИмяБазыДанных] SET DATE_CORRELATION_OPTIMIZATION OFF
    ALTER DATABASE [ИмяБазыДанных] SET TRUSTWORTHY OFF
    ALTER DATABASE [ИмяБазыДанных] SET ALLOW_SNAPSHOT_ISOLATION OFF
    ALTER DATABASE [ИмяБазыДанных] SET PARAMETERIZATION SIMPLE
    ALTER DATABASE [ИмяБазыДанных] SET READ_WRITE
    ALTER DATABASE [ИмяБазыДанных] SET RECOVERY SIMPLE
    ALTER DATABASE [ИмяБазыДанных] SET MULTI_USER
    ALTER DATABASE [ИмяБазыДанных] SET PAGE_VERIFY NONE
    ALTER DATABASE [ИмяБазыДанных] SET DB_CHAINING OFF
    GO
    USE [ИмяБазыДанных]
    GO
    EXEC sp_changedbowner 'sa'
    GO
    EXEC [ИмяБазыДанных].sys.sp_addextendedproperty @name=N'SET AUTO_UPDATE_STATISTICS_ASYNC ON'
    , @value=N'Стандартное значение изменено для снижения необходимости частого обновления статистики в рамках планов обслуживания БД.'
    EXEC [ИмяБазыДанных].sys.sp_addextendedproperty @name=N'SET PAGE_VERIFY NONE'
    , @value=N'Стандартное значение изменено для повышения производительности IO, включать нужно при обнаружении ошибок DBCC CHECKDB или для критических бизнесу приложений.'
    EXEC [ИмяБазыДанных].sys.sp_addextendedproperty @name=N'SET RECOVERY SIMPLE'
    , @value=N'Для повышения производительности не критичных приложений. Если нужно восстанавливать данные на заданый момент времени, нужно выбрать другую модель и обеспечить резервное копирование журнала транзакций.'
    GO

     

Полезные ссылки:

FAQ:

Вопрос 1.

В представленном выше примере сценария создания БД, есть установка значений настроек по умолчанию:

    ALTER DATABASE [ИмяБазыДанных] SET ANSI_NULLS OFF
    ALTER DATABASE [ИмяБазыДанных] SET ANSI_PADDING OFF
    ALTER DATABASE [ИмяБазыДанных] SET ANSI_WARNINGS OFF
    ALTER DATABASE [ИмяБазыДанных] SET ARITHABORT OFF
    ALTER DATABASE [ИмяБазыДанных] SET CONCAT_NULL_YIELDS_NULL OFF
    ALTER DATABASE [ИмяБазыДанных] SET QUOTED_IDENTIFIER OFF

     

Тут значения по умолчанию выставляются в OFF. Чем это обусловлено? Вопрос возник, поскольку неясны причины отклонения от ANSI стандарта, к тому же в другом регламенте рекомендуется выставлять данные настройки в ON:

    SET transaction isolation level read committed;
    -- Если всё совсем уж плохо - uncommitted
    SET nocount -- меньше трафик
        ,quoted_identifier -- стандартизация кавычек
        ,ansi_nulls -- стандартизация сравнения с NULL
        ,ansi_warnings -- вывод ошибок агрегации NULL и деления на 0
        ,ansi_padding -- стандартизация оконечных пробелов и нулей
        ,arithabort -- стандартизация отката транзакций
        ,xact_abort -- стандартизация отката транзакций
        ,concat_null_yields_null -- сцепление с NULL
    ON;
    SET numeric_roundabort off; -- стандартизация потери точности
    GO

     

Ответ 1.

Это установки по умолчанию, которые делает для новой базы данных соответствующий мастер SQL Server 2005 Management Studio. В регламенте предлагается документировать все отклонения от стандартных настроек, которые показаны в примере и там же есть образец документирования. На самом деле, если проще изменять установки по умолчанию, а не добавлять их в каждый сценарий создания объектов, можно создать базу с желаемым набором умолчаний. Однако, в таком случае нужно чётко понимать, что изменение настроек не приведёт к конфликтам с системными объектами или при промежуточной материализации в tempdb… Добавление установок в сценарии является более простым, понятным и, главное, управляемым путём настройки.