Вопрос 1: Является ли команда TRUNCATE TABLE транзактивной?
Краткий ответ: Да.
Далее приведена репродукция кода, в котором выполняется
откат транзакции, содержащей TRUNCATE TABLE:
USE TEMPDB
SET NOCOUNT ON
GO
CREATE TABLE DATA (COL1 INT, COL2 INT)
GO
INSERT DATA VALUES (1, 1)
GO
BEGIN TRAN
TRUNCATE TABLE DATA
ROLLBACK TRAN
SELECT * FROM DATA
GO
DROP TABLE DATA
Результат:
COL1 COL2
----------- -----------
1 1
В своем блоге Paul Randal превосходно описал, каким образом гарантируется транзактивность TRUNCATE TABLE.
Далее, я привожу перевод этого поста.
Сначала создадим тестовую таблицу для экспериментов.
SET NOCOUNT ON
CREATE TABLE TruncateTest (c1 INT IDENTITY, c2 CHAR (8000) DEFAULT 'A');
GO
DECLARE @a INT;
SELECT @a = 1;
WHILE (@a < 20)
BEGIN
INSERT INTO TruncateTest DEFAULT VALUES;
SELECT @a = @a + 1;
END;
GO
Воспользуемся недокументированной командой DBCC IND, чтобы
увидеть какие страницы и экстенты выделены для нашей экспериментальной таблицы:
DBCC IND (test, TruncateTest, 0);
GO
Результат:
PageFID PagePID
------- ---------
1 193
1 192
1 194
1 195
1 196
1 197
1 198
1 199
1 200
1 224
1 225
1 226
1 227
1 228
1 229
1 230
1 231
1 232
1 233
1 234
DBCC execution completed. If DBCC printed error messages,
contact your system administrator.
Я преднамеренно сократил вывод команды, оставив лишь ID
страниц. Мы видим, что эта таблица задействовала 4 экстента (которые начинаются
с страниц (1:192), (1:200), (1:224), (1:232)). Теперь, если мы выполним команду
TRUNCATE TABLE в транзакции, что покажет DBCC IND?
BEGIN TRAN;
GO
TRUNCATE TABLE TruncateTest;
GO
DBCC IND (test, TruncateTest, 0);
GO
Результат:
DBCC execution completed. If DBCC printed error messages,
contact your system administrator.
Похоже что страницы под таблицу уже не выделены, но где же
они? Проверим текущие блокировки. Вместо использования sp_lock я использую ее
DMV-замену, sys.dm_tran_locks:
SELECT resource_type, resource_description, request_mode FROM sys.dm_tran_locks WHERE resource_type IN ('EXTENT', 'PAGE');
GO
Результат:
resource_type resource_description
request_mode
--------------- ---------------------- --------------
EXTENT
1:200
X
PAGE
1:198
X
PAGE
1:199
X
PAGE
1:196
X
PAGE
1:197
X
PAGE
1:194
X
PAGE
1:195
X
PAGE
1:192
X
PAGE
1:193
X
EXTENT
1:192
X
PAGE
1:200
X
EXTENT
1:232
X
EXTENT
1:224
X
Собственно, все страницы и экстенты заблокированы. Страницы
не являются уже выделенными для этой таблицы, но поскольку на них наложены
эксклюзивные блокировки, подсистема распределения страниц не может освободить
эти страницы пока блокировки не снимутся (произойдет commit транзакции).
Если
же произойдет откат транзакции, страницы попросту будут снова отмечены как
принадлежащие таблице TruncateTest.
Вопрос 2: Возможно ли откатить успешное выполнение TRUNCATE
TABLE посредством восстановления транзакционных логов на момент предшествующий
запуску команды?
Краткий ответ: Да.
Данный вопрос обсуждал в своем блоге Pinal Dave - TRUNCATE
Can’t be Rolled Back Using Log Files After Transaction Session Is Closed. Но его
мнение не было разделено посетителями его блога, включая и мною.
При выполнении TRUNCATE TABLE в лог не попадают ни удаленные страницы, ни
экстенты. Как же тогда возможно восстановить данные таблицы при восстановлении
журналов транзакций?
Ответ кроется в том, что для восстановления данных таблицы, необходимо
чтобы в журнал попали факты добавления (и изменения) данных. Или же данные
таблицы уже содержались в полном бэкапе.
Таким образом, при восстановлении
полного бэкапа, затем журналов транзакций до момента
запуска команды TRUNCATE данные таблицы будут восстановлены в полном
объеме.
Репродуктивный скрипт:
SET NOCOUNT ON
CREATE DATABASE TEST
GO
ALTER DATABASE TEST SET RECOVERY FULL
USE TEST
GO
-- STEP 2. Creating table with some data (100 rows)
CREATE TABLE data (f1 INT IDENTITY)
GO
INSERT data DEFAULT VALUES
GO 100
-- STEP 3. Performing full backup
BACKUP DATABASE TEST TO DISK='c:\test_full.bak' WITH INIT
GO
-- STEP 4. Performing log backup
DECLARE @date DATETIME
--variant1: Date mark just before truncation
SET @date = DATEADD(s, 0, GETDATE())
-- variant2: Date mark after truncation
-- SET @date = DATEADD(s, 1, GETDATE())
TRUNCATE TABLE data
WAITFOR DELAY '00:00:03'
BACKUP LOG TEST TO DISK='c:\test_log.bak' WITH INIT
-- STEP 5. Restoring Test DB.
USE MASTER
RESTORE DATABASE TEST FROM DISK='c:\test_full.bak' WITH NORECOVERY, REPLACE
RESTORE LOG TEST FROM DISK='c:\test_log.bak' WITH RECOVERY, STOPAT=@date
GO
USE TEST
GO
select COUNT(*) AS RowCnt FROM data
go
USE MASTER
DROP DATABASE TEST
Результат:
RowCnt
-----
100