Просмотр информации о восстановлении базы данных

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

2010-12-29 12:02:10.43 spid25s Starting up database 'testdb'.
2010-12-29 12:02:31.23 spid25s Recovery of database 'testdb' (11) is 0% complete (approximately 1725 seconds remain). Phase 1 of 3. This is an informational message only. No user action is required.
2010-12-29 12:03:08.94 spid25s Recovery of database 'testdb' (11) is 1% complete (approximately 1887 seconds remain). Phase 1 of 3. This is an informational message only. No user action is required.
2010-12-29 12:03:08.95 spid25s Recovery of database 'testdb' (11) is 1% complete (approximately 1887 seconds remain). Phase 2 of 3. This is an informational message only. No user action is required.
2010-12-29 12:04:57.97 spid25s Recovery of database 'testdb' (11) is 43% complete (approximately 192 seconds remain). Phase 2 of 3. This is an informational message only. No user action is required.
2010-12-29 12:04:57.97 spid25s 580056 transactions rolled forward in database 'testdb' (11). This is an informational message only. No user action is required.
2010-12-29 12:04:57.97 spid25s Recovery of database 'testdb' (11) is 43% complete (approximately 192 seconds remain). Phase 3 of 3. This is an informational message only. No user action is required.
2010-12-29 12:06:32.05 spid25s Recovery of database 'testdb' (11) is 99% complete (approximately 2 seconds remain). Phase 3 of 3. This is an informational message only. No user action is required.
2010-12-29 12:06:35.09 spid25s 1 transactions rolled back in database 'testdb' (11). This is an informational message only. No user action is required.
2010-12-29 12:06:35.09 spid25s Recovery is writing a checkpoint in database 'testdb' (11). This is an informational message only. No user action is required.
2010-12-29 12:06:35.44 spid25s Recovery completed for database testdb (database ID 11) in 244 second(s) (analysis 37849 ms, redo 109038 ms, undo 97146 ms.) This is an informational message only. No user action is required.

Начиная с SQL Server 2008, Вы не должны неоднократно открываться/обновлять журнал ошибок SQL Server или выполнять хранимую процедуру sp_readerrorlog, чтобы получить свежую информацию о ходе восстановления базы данных. Большая часть информации легко доступна в представлениях динамического управления (DMV). Два таких
представления предлагают возможность просмотра хода процесса восстановления базы данных: sys.dm_exec_requests и sys.dm_tran_database_transactions.
Информация, представленная в них DMV’s, изменяется в зависимости от ситуации: восстановление баз данных во время запуска сервера, восстановление базы данных после операции присоединения, восстановления базы данных после операции восстановления.
Вы можете создать и использовать специальные таблицы для логирования процесса восстановления с помощью представления sys.dm_exec_requests showing с помощью скрипта:

-- создаём таблицы для хранения информации о процессе восстановления 
USE <db_name> 
GO 
-- можете дописать сюда код удаляющий таблицы только когда они уже существуют 
DROP TABLE
[dbo].[tbl_recovery_tracking] 
GO 
DROP TABLE
[dbo].[tbl_dm_tran_database_transactions] 
GO 
CREATE TABLE
[dbo].[tbl_recovery_tracking]( 
[runtime] [datetime] NOT NULL
,[command] [nvarchar](256) NOT NULL
,[session_id] [smallint] NOT NULL
,[database_id] [smallint] NOT NULL
,[total_elapsed_time] [int] NOT NULL
,[percent_complete] [real] NOT NULL
,[estimated_completion_time] [bigint] NOT NULL
,[wait_resource] [nvarchar](256) NOT NULL
,[wait_time] [int] NOT NULL
,[wait_type] [nvarchar](60) NULL
,[blocking_session_id] [smallint] NULL
,[reads] [bigint] NOT NULL
,[writes] [bigint] NOT NULL
,[cpu_time] [int] NOT NULL
) ON  [PRIMARY] 
GO 
  
CREATE TABLE  [dbo].[tbl_dm_tran_database_transactions](  
[runtime] [datetime] NOT NULL
,[transaction_id] [bigint] NOT NULL
,[database_id] [int] NOT NULL
,[database_transaction_log_record_count] [bigint] NOT NULL
,[database_transaction_log_bytes_used] [bigint] NOT NULL
,[database_transaction_log_bytes_reserved] [bigint] NOT NULL
,[database_transaction_next_undo_lsn] [numeric](25,0) NULL
) ON  [PRIMARY] 
GO 
-- собираем информацию в цикле 
WHILE 1 = 1 
BEGIN
INSERT INTO  [dbo].[tbl_recovery_tracking] 
SELECT GETDATE() as runtime, command, session_id, 
database_id, total_elapsed_time, percent_complete, estimated_completion_time, wait_resource, 
wait_time, wait_type, blocking_session_id, reads, writes, cpu_time 
FROM sys.dm_exec_requests 
WHERE command = 'DB STARTUP' – используйте другой тип для остлеживания другого типа восстановления 
INSERT INTO  tbl_dm_tran_database_transactions 
SELECT GETDATE() as runtime,transaction_id, database_id, database_transaction_log_record_count, database_transaction_log_bytes_used, database_transaction_log_bytes_reserved, database_transaction_next_undo_lsn 
FROM sys.dm_tran_database_transactions 
WAITFOR DELAY '00:00:01' – интервал обновления в 1 секунду  
END
GO 
-- после того как вы собрали информацию вы можете её проанализировать  
SELECT runtime, command, session_id, database_id, total_elapsed_time, percent_complete, 
estimated_completion_time, wait_resource, wait_time, wait_type,blocking_session_id, reads, 
writes, cpu_time  
FROM [dbo].[tbl_recovery_tracking] 
WHERE session_id =25 – измените на нужное вам значение   
ORDER BY  runtime 
GO 
SELECT runtime, transaction_id, database_id, database_transaction_log_record_count,  
database_transaction_log_bytes_used, database_transaction_log_bytes_reserved, database_transaction_next_undo_lsn 
FROM tbl_dm_tran_database_transactions 
WHERE database_id = 11 and transaction_id = 1452239 -- измените на нужное вам значение  
ORDER BY  runtime 
GO 

 

В этом примере используется тип команды [DB STARTUP] который показывает, что логируются системные задачи процесса во время восстановление баз данных во время запуска сервера.