{"id":260,"date":"2021-11-19T22:25:12","date_gmt":"2021-11-19T22:25:12","guid":{"rendered":"http:\/\/localhost\/blog\/?p=260"},"modified":"2022-06-03T20:07:27","modified_gmt":"2022-06-03T19:07:27","slug":"database-in-recovery","status":"publish","type":"post","link":"https:\/\/www.davidirwin.co.uk\/index.php\/2021\/11\/19\/database-in-recovery\/","title":{"rendered":"Database in recovery"},"content":{"rendered":"<p>I had an issue with an old sharepoint 2003 server crashing when deleting a sharepoint site.<br \/>\nWhen i looked in sql manager the database was in recovery mode.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-244\" src=\"https:\/\/www.davidirwin.co.uk\/wp-content\/uploads\/2022\/03\/wss1.png\" alt=\"\" width=\"255\" height=\"183\" \/><\/p>\n<p>I need to find out how long it was going to take. After some googling I found this script below.<\/p>\n<blockquote class=\"wp-embedded-content\" data-secret=\"6AYrRq38DI\"><p><a href=\"https:\/\/jasonbrimhall.info\/2015\/05\/18\/database-in-recovery-update\/\">Database In Recovery Update<\/a><\/p><\/blockquote>\n<p><iframe loading=\"lazy\" class=\"wp-embedded-content\" sandbox=\"allow-scripts\" security=\"restricted\" style=\"position: absolute; clip: rect(1px, 1px, 1px, 1px);\" title=\"&#8220;Database In Recovery Update&#8221; &#8212; SQL RNNR\" src=\"https:\/\/jasonbrimhall.info\/2015\/05\/18\/database-in-recovery-update\/embed\/#?secret=lng5KpRPza#?secret=6AYrRq38DI\" data-secret=\"6AYrRq38DI\" width=\"500\" height=\"282\" frameborder=\"0\" marginwidth=\"0\" marginheight=\"0\" scrolling=\"no\"><\/iframe><\/p>\n<p>DECLARE @ErrorLog AS TABLE<br \/>\n(<br \/>\n[LogDate] DATETIME<br \/>\n, [ProcessInfo] VARCHAR(64)<br \/>\n, [TEXT] VARCHAR(MAX)<br \/>\n);<\/p>\n<p>INSERT INTO @ErrorLog<br \/>\nEXEC sys.xp_readerrorlog 0, 1, &#8220;Recovery of database&#8221;;<\/p>\n<p>SELECT DB_NAME(dt.database_id) AS DBName<br \/>\n, GETDATE() AS currenttime<br \/>\n, at.transaction_begin_time<br \/>\n, dt.transaction_id<br \/>\n, at.name AS TranName<br \/>\n, cx.PercentComplete<br \/>\n, cx.MinutesRemaining<br \/>\n, d.log_reuse_wait_desc<br \/>\n, database_transaction_log_record_count<br \/>\n, database_transaction_log_bytes_used<br \/>\n, database_transaction_next_undo_lsn<br \/>\n, CASE at.transaction_state<br \/>\nWHEN 0 THEN &#8216;Not Completely Initialized&#8217;<br \/>\nWHEN 1 THEN &#8216;Initialized but Not Started&#8217;<br \/>\nWHEN 2 THEN &#8216;Transaction is Active&#8217;<br \/>\nWHEN 3 THEN &#8216;Read-Only tran has Ended&#8217;<br \/>\nWHEN 4 THEN &#8216;Distributed Tran commit process has been initiated&#8217;<br \/>\nWHEN 5 THEN &#8216;In prepared state and waiting resolution&#8217;<br \/>\nWHEN 6 THEN &#8216;Transaction has been committed&#8217;<br \/>\nWHEN 7 THEN &#8216;Transaction is being rolled back&#8217;<br \/>\nWHEN 8 THEN &#8216;Transaction has been rolled back&#8217;<br \/>\nEND AS TranState<br \/>\nFROM sys.dm_tran_database_transactions dt<br \/>\nLEFT OUTER JOIN sys.dm_tran_active_transactions at<br \/>\nON dt.transaction_id = at.transaction_id<br \/>\nINNER JOIN master.sys.databases d<br \/>\nON d.database_id = dt.database_id<br \/>\nCROSS APPLY ( SELECT TOP 1<br \/>\n[LogDate]<br \/>\n, SUBSTRING([TEXT],<br \/>\nCHARINDEX(&#8216;) is &#8216;, [TEXT]) + 4,<br \/>\nCHARINDEX(&#8216; complete (&#8216;, [TEXT])<br \/>\n&#8211; CHARINDEX(&#8216;) is &#8216;, [TEXT]) &#8211; 4) AS PercentComplete<br \/>\n, CAST(SUBSTRING([TEXT],<br \/>\nCHARINDEX(&#8216;approximately&#8217;,<br \/>\n[TEXT]) + 13,<br \/>\nCHARINDEX(&#8216; seconds remain&#8217;,<br \/>\n[TEXT])<br \/>\n&#8211; CHARINDEX(&#8216;approximately&#8217;,<br \/>\n[TEXT]) &#8211; 13) AS FLOAT)<br \/>\n\/ 60.0 AS MinutesRemaining<br \/>\n, DB_NAME(SUBSTRING([TEXT],<br \/>\nCHARINDEX(&#8216;(&#8216;, [TEXT]) + 1,<br \/>\nCHARINDEX(&#8216;)&#8217;, [TEXT])<br \/>\n&#8211; CHARINDEX(&#8216;(&#8216;, [TEXT]) &#8211; 1)) AS DBName<br \/>\n, CAST(SUBSTRING([TEXT],<br \/>\nCHARINDEX(&#8216;(&#8216;, [TEXT]) + 1,<br \/>\nCHARINDEX(&#8216;)&#8217;, [TEXT])<br \/>\n&#8211; CHARINDEX(&#8216;(&#8216;, [TEXT]) &#8211; 1) AS INT) AS DBID<br \/>\nFROM @ErrorLog<br \/>\nORDER BY [LogDate] DESC<br \/>\n) cx<br \/>\nWHERE d.state_desc &lt;&gt; &#8216;online&#8217;<br \/>\nAND cx.DBID = dt.database_id;<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-245\" src=\"https:\/\/www.davidirwin.co.uk\/wp-content\/uploads\/2022\/03\/wss2.png\" alt=\"\" width=\"814\" height=\"109\" \/><\/p>\n","protected":false},"excerpt":{"rendered":"<p>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. Database In Recovery Update DECLARE @ErrorLog AS [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[16],"tags":[],"class_list":["post-260","post","type-post","status-publish","format-standard","hentry","category-windows-server"],"_links":{"self":[{"href":"https:\/\/www.davidirwin.co.uk\/index.php\/wp-json\/wp\/v2\/posts\/260","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.davidirwin.co.uk\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.davidirwin.co.uk\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.davidirwin.co.uk\/index.php\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.davidirwin.co.uk\/index.php\/wp-json\/wp\/v2\/comments?post=260"}],"version-history":[{"count":4,"href":"https:\/\/www.davidirwin.co.uk\/index.php\/wp-json\/wp\/v2\/posts\/260\/revisions"}],"predecessor-version":[{"id":348,"href":"https:\/\/www.davidirwin.co.uk\/index.php\/wp-json\/wp\/v2\/posts\/260\/revisions\/348"}],"wp:attachment":[{"href":"https:\/\/www.davidirwin.co.uk\/index.php\/wp-json\/wp\/v2\/media?parent=260"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.davidirwin.co.uk\/index.php\/wp-json\/wp\/v2\/categories?post=260"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.davidirwin.co.uk\/index.php\/wp-json\/wp\/v2\/tags?post=260"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}