I had an issue with an old sharepoint 2003 server crashing when deleting a sharepoint site.
When i looked in sql manager the database was in recovery mode.
I need to find out how long it was going to take. After some googling I found this script below.
DECLARE @ErrorLog AS TABLE
(
[LogDate] DATETIME
, [ProcessInfo] VARCHAR(64)
, [TEXT] VARCHAR(MAX)
);
INSERT INTO @ErrorLog
EXEC sys.xp_readerrorlog 0, 1, “Recovery of database”;
SELECT DB_NAME(dt.database_id) AS DBName
, GETDATE() AS currenttime
, at.transaction_begin_time
, dt.transaction_id
, at.name AS TranName
, cx.PercentComplete
, cx.MinutesRemaining
, d.log_reuse_wait_desc
, database_transaction_log_record_count
, database_transaction_log_bytes_used
, database_transaction_next_undo_lsn
, CASE at.transaction_state
WHEN 0 THEN ‘Not Completely Initialized’
WHEN 1 THEN ‘Initialized but Not Started’
WHEN 2 THEN ‘Transaction is Active’
WHEN 3 THEN ‘Read-Only tran has Ended’
WHEN 4 THEN ‘Distributed Tran commit process has been initiated’
WHEN 5 THEN ‘In prepared state and waiting resolution’
WHEN 6 THEN ‘Transaction has been committed’
WHEN 7 THEN ‘Transaction is being rolled back’
WHEN 8 THEN ‘Transaction has been rolled back’
END AS TranState
FROM sys.dm_tran_database_transactions dt
LEFT OUTER JOIN sys.dm_tran_active_transactions at
ON dt.transaction_id = at.transaction_id
INNER JOIN master.sys.databases d
ON d.database_id = dt.database_id
CROSS APPLY ( SELECT TOP 1
[LogDate]
, SUBSTRING([TEXT],
CHARINDEX(‘) is ‘, [TEXT]) + 4,
CHARINDEX(‘ complete (‘, [TEXT])
– CHARINDEX(‘) is ‘, [TEXT]) – 4) AS PercentComplete
, CAST(SUBSTRING([TEXT],
CHARINDEX(‘approximately’,
[TEXT]) + 13,
CHARINDEX(‘ seconds remain’,
[TEXT])
– CHARINDEX(‘approximately’,
[TEXT]) – 13) AS FLOAT)
/ 60.0 AS MinutesRemaining
, DB_NAME(SUBSTRING([TEXT],
CHARINDEX(‘(‘, [TEXT]) + 1,
CHARINDEX(‘)’, [TEXT])
– CHARINDEX(‘(‘, [TEXT]) – 1)) AS DBName
, CAST(SUBSTRING([TEXT],
CHARINDEX(‘(‘, [TEXT]) + 1,
CHARINDEX(‘)’, [TEXT])
– CHARINDEX(‘(‘, [TEXT]) – 1) AS INT) AS DBID
FROM @ErrorLog
ORDER BY [LogDate] DESC
) cx
WHERE d.state_desc <> ‘online’
AND cx.DBID = dt.database_id;
