Вы можете найти часть 2 здесь .
Вступление
Довольно часто транзакции в MS SQL Server теряются теми, кто их запускал. Нередко запускается сценарий в SSMS, который запускает явную транзакцию с помощью оператора BEGIN TRANSACTION, но затем возникает ошибка и происходит сбой COMMIT TRANSACTION или ROLLBACK TRANSACTION, в то время как инициатор транзакции покидает этот запрос в течение некоторого времени. Транзакции, оставленные забытыми на длительные периоды времени, могут препятствовать доступу пользователей к заблокированным ресурсам (таблицам, ресурсам сервера (ОЗУ, ЦП, система ввода-вывода).
В этой статье мы рассмотрим, как удалять потерянные транзакции с помощью SQL Complete .
Под потерянной транзакцией мы в основном будем понимать активную (запущенную) транзакцию, которая не имеет активных (запущенных) запросов в течение некоторого длительного периода времени T.
Общий алгоритм удаления потерянных транзакций
Вот общий алгоритм удаления потерянных транзакций:
- Создайте две таблицы: одну таблицу для хранения и анализа информации о текущих потерянных транзакциях, а вторую — для архивации транзакций, выбранных из первой таблицы, в соответствии с действиями по удалению для последующего анализа.
- Соберите информацию о транзакциях и их сеансах, которые не имеют запросов, то есть транзакции, запущенные и оставленные забытыми в течение определенного промежутка времени T.
- Обновите таблицу, содержащую текущие потерянные транзакции, начиная с шага 1 (если потерянная транзакция получает активный запрос, то такая транзакция больше не считается потерянной и удаляется из таблицы).
- Определите сеансы, которые нужно уничтожить (в сеансе есть хотя бы одна потерянная транзакция, упомянутая в таблице из шага 1, и для этого сеанса также не выполняются запросы).
- Заархивируйте информацию, которую вы собираетесь удалить (подробности о сеансах, соединениях и транзакциях, которые нужно уничтожить).
- Убить выбранные сессии.
- Удалите обработанные записи вместе с теми, которые не могут быть удалены и были в таблице с шага 1 слишком долго.
Теперь давайте посмотрим, как мы можем реализовать этот алгоритм.
Вы также можете прочитать:
Восстановить удаленные данные из таблицы SQL, используя журнал транзакций и номера LSN.
Реализация алгоритма удаления потерянных транзакций
Таблица для хранения и анализа потерянных транзакций
Форматирование кода
Давайте создадим таблицу для хранения и анализа информации о текущих потерянных транзакциях с использованием dbForge SQL Complete:
1. Раскрывающийся список предложений помогает быстро составить таблицу.
2. Команды SQL преобразуются в верхний регистр:
Таким образом, мы завершим скрипт для создания нужной нам таблицы:
SQL
xxxxxxxxxx
1
SET ANSI_NULLS ON
2
GO
3
SET QUOTED_IDENTIFIER ON
5
GO
6
CREATE TABLE [srv].[SessionTran](
8
[SessionID] INT NOT NULL,
9
[TransactionID] BIGINT NOT NULL,
10
[CountTranNotRequest] TINYINT NOT NULL,
11
[CountSessionNotRequest] TINYINT NOT NULL,
12
[TransactionBeginTime] DATETIME NOT NULL,
13
[InsertUTCDate] DATETIME NOT NULL,
14
[UpdateUTCDate] DATETIME NOT NULL,
15
CONSTRAINT [PK_SessionTran] PRIMARY KEY CLUSTERED
16
(
17
[SessionID] ASC,
18
[TransactionID] ASC
19
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 95) ON [PRIMARY]
20
) ON [PRIMARY]
21
GO
22
ALTER TABLE [srv].[SessionTran] ADD CONSTRAINT [DF_SessionTran_Count] DEFAULT ((0)) FOR [CountTranNotRequest]
24
GO
25
ALTER TABLE [srv].[SessionTran] ADD CONSTRAINT [DF_SessionTran_CountSessionNotRequest] DEFAULT ((0)) FOR [CountSessionNotRequest]
27
GO
28
ALTER TABLE [srv].[SessionTran] ADD CONSTRAINT [DF_SessionTran_InsertUTCDate] DEFAULT (getutcdate()) FOR [InsertUTCDate]
30
GO
31
ALTER TABLE [srv].[SessionTran] ADD CONSTRAINT [DF_SessionTran_UpdateUTCDate] DEFAULT (getutcdate()) FOR [UpdateUTCDate]
33
GO
Где:
1) SessionID является идентификатором сеанса
2) TransactionID - это идентификатор потерянной транзакции
3) CountTranNotRequest - количество раз, когда транзакция была зарегистрирована как потерянная
4) CountSessionNotRequest - это число раз, когда сеанс был записан как не имеющий активных запросов и содержащий потерянную транзакцию
5) TransactionBeginTime потерял дату и время начала транзакции
6) InsertUTCDate - это дата и время UTC, когда была создана запись
7) UpdateUTCDate - это дата и время UTC, когда запись была изменена.
Форматирование документа
Форматирование текущего документа
Если текст был напечатан в другом редакторе или без использования SQL Complete, вы можете отформатировать его с помощью параметра SQL Complete \ Format Document:
Ниже вы можете увидеть, как выглядел текст перед форматированием:
SQL
xxxxxxxxxx
1
create table [srv].[SessionTran](
2
[SessionID] int not null, [TransactionID] bigint not null, [CountTranNotRequest] tinyint not null, [CountSessionNotRequest] tinyint not null,
3
[TransactionBeginTime] datetime not null, [InsertUTCDate] datetime not null, [UpdateUTCDate] datetime not null,
4
constraint [PK_SessionTran] primary key clustered ([SessionID] asc, [TransactionID] asc)
5
with (pad_index = off, statistics_norecompute = off, ignore_dup_key = off, allow_row_locks = on, allow_page_locks = on, fillfactor = 95) ON [PRIMARY]
6
) on [PRIMARY]
7
GO
Вот как выглядит код после применения форматирования:
SQL
xxxxxxxxxx
1
CREATE TABLE [srv].[SessionTran] (
2
[SessionID] INT NOT NULL
3
,[TransactionID] BIGINT NOT NULL
4
,[CountTranNotRequest] TINYINT NOT NULL
5
,[CountSessionNotRequest] TINYINT NOT NULL
6
,[TransactionBeginTime] DATETIME NOT NULL
7
,[InsertUTCDate] DATETIME NOT NULL
8
,[UpdateUTCDate] DATETIME NOT NULL
9
,CONSTRAINT [PK_SessionTran] PRIMARY KEY CLUSTERED ([SessionID] ASC, [TransactionID] ASC)
10
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 95) ON [PRIMARY]
11
) ON [PRIMA
12
GO
Вы можете легко заметить разницу. Ключевые слова T-SQL теперь пишутся с большой буквы. Отступ был применен, и каждый столбец теперь начинается с новой строки. Это, очевидно, обеспечило более читаемый код, что привело к лучшему восприятию и более быстрому принятию решений.
Форматирование внешних документов
SQL Complete позволяет форматировать выбранный фрагмент кода. Параметр «Выбор формата» становится активным после выбора фрагмента кода для форматирования:
На рисунке ниже вы можете увидеть, как фрагмент кода выглядел до форматирования:
После применения форматирования фрагмент выглядит следующим образом:
Вы также можете отформатировать ранее написанные скрипты, используя опцию SQL Formatter :
В открывшемся окне вы можете выбрать форматирование файлов или каталогов. В нашем случае мы выбираем « Файлы» и нажимаем кнопку « Далее» :
После этого вам необходимо добавить необходимые файлы, нажав кнопку « Добавить» :
Далее мы форматируем выбранные файлы, нажав кнопку « Формат» :
После этого начинается форматирование:
После завершения процесса форматирования отобразится окно завершения и выбранные файлы с измененными сценариями откроются в SSMS:
Точно так же мы украшаем код для всего каталога Scripts.
Для этого после выбора опции SQL Formatter в меню SQL Complete выберите « Каталоги» и нажмите кнопку « Далее» :
Затем вам нужно снова нажать кнопку Добавить , чтобы выбрать необходимые каталоги. Также стоит обратить внимание на левый нижний угол мастера форматирования SQL, где вы можете выбрать расширения файлов для обработки и указать, включать ли подпапки или нет.
После завершения процесса форматирования файлы также откроются в SSMS:
Стоит отметить, что после форматирования файлы не сохраняются, а открываются в измененном виде. Чтобы сохранить файлы, вам нужно сохранить их в среде SSMS. Чтобы изменить это, снимите флажок Оставлять измененные файлы открытыми после форматирования (выбран по умолчанию). Если этот флажок снят, изменения форматирования будут применены и сохранены немедленно, а файлы не будут открываться в SSMS.
Параметры форматирования
Для более сложных опций форматирования вы можете обратиться к опциям SQL Complete:
Затем вам нужно перейти на вкладку Форматирование:
Вкладка имеет две вложенные вкладки:
Подвкладка Общие содержит основные настройки форматирования.
Подвкладка « Профили » содержит список форматирующих профилей в формате XML. На этой вкладке вы также можете создать новый профиль, отредактировать существующий, активировать нужный профиль и открыть папку с профилями.
Чтобы отредактировать профиль, вам нужно выбрать нужный профиль и либо нажать кнопку « Редактировать профиль» , либо дважды щелкнуть сам профиль. После этого у вас будет возможность проверить правила профиля и внести необходимые изменения в случае необходимости.
Функция форматирования SQL Complete значительно упрощает как разработку кода, так и обслуживание кода.
Включение и отключение SQL Complete
В некоторых случаях инструмент SQL Complete можно отключить, чтобы ускорить IDE. Это можно легко сделать, щелкнув параметр Отключить завершение кода в главном меню SQL Complete:
Вы можете снова включить SQL Complete, выбрав опцию Enable Code Completion в главном меню SQL Complete:
Теперь мы создали таблицу для исправления пропущенных сеансов для потерянной транзакции srv.SessionTran.
Создание таблицы для архивации потерянных транзакций путем удаления действий
Точно так же для дальнейшего анализа нам нужно создать таблицу для архивации транзакций, выбранных из первой таблицы в соответствии с действиями удаления: Полный скрипт на GitHub .
Где:
sys.dm_exec_sessions и sys.dm_exec_connections - это системные представления
InsertUTCDate - это дата и время UTC, когда была создана запись
Заключение
Итак, в этой статье мы рассмотрели общий алгоритм удаления потерянных транзакций в MS SQL Server и то, как этот алгоритм может быть реализован с использованием dbForge SQL Complete. В результате мы создали таблицу для фиксации уничтоженных сессий и создали таблицу архивных транзакций для дальнейшего анализа.
Дальнейшее чтение
Сравнение инструментов моделирования данных MS SQL Server: типы данных