{"id":558,"date":"2011-06-24T03:19:00","date_gmt":"2011-06-24T03:19:00","guid":{"rendered":"\/blogs\/paul\/post\/Disaster-recovery-101-dealing-with-negative-SPIDS-(-2-and-3).aspx"},"modified":"2017-07-25T09:55:05","modified_gmt":"2017-07-25T16:55:05","slug":"disaster-recovery-101-dealing-with-negative-spids-2-and-3","status":"publish","type":"post","link":"https:\/\/www.sqlskills.com\/blogs\/paul\/disaster-recovery-101-dealing-with-negative-spids-2-and-3\/","title":{"rendered":"Disaster recovery 101: dealing with negative SPIDS (-2 and -3)"},"content":{"rendered":"

Every so often I get asked how to deal with SPID -2 and once in a blue moon I get asked about SPID -3. Neither of these SPIDs will show up in sysprocesses<\/em> or sys.dm_exec_requests<\/em> or sys.dm_exec_sessions<\/em>, but they’re both valid SPIDs.<\/p>\n

SPID -2 is an orphaned DTC transaction.<\/p>\n

SPID -3 is a deferred transaction.<\/p>\n

Both SPIDs will hold locks that can cause blocking, and these are often how they’re noticed in the first place.<\/p>\n

SPID -4 is a transaction blocked waiting for a latch but the latch owner can’t be determined. There’s no good way to deal with these.<\/p>\n

For a good walkthrough of how to kill an orphaned DTC transaction if you cannot resurrect it any other way, see this excellent post<\/a> by Ajmer Dhariwal (ex-Product Support, and a student in our London Immersion Event this week).<\/p>\n

A deferred transaction is one where the transaction could not be recovered fully because of an I\/O error or a gross file system problem like an unavailable file. Books Online has a good description here<\/a> – so I won’t regurgitate it.<\/p>\n

What I’d like to do is show you how to create a deferred transaction so you can actually see SPID -3 and how to tell if you have deferred transactions.<\/p>\n

First off I’ll create a database, with an extra file sitting on a USB drive (G:) in my laptop.<\/p>\n

\r\nCREATE DATABASE [DeferredTran];\r\nGO\r\n\r\nALTER DATABASE [DeferredTran] ADD FILEGROUP [FG_USB];\r\nGO\r\n\r\nALTER DATABASE [DeferredTran] ADD FILE (\r\n    NAME = [FG_USB_1], FILENAME = N'G:\\FG_USB_1.ndf')\r\nTO FILEGROUP [FG_USB];\r\nGO\r\n\r\nBACKUP DATABASE [DeferredTran] TO DISK = N'C:\\SQLskills\\DeferredTran.bck' WITH INIT;\r\nGO\r\n<\/pre>\n
Next I’ll create a table on the USB filegroup, and create an explicit transaction and force the log and data to disk.<\/p>\n
\r\nUSE [DeferredTran];\r\nGO\r\n\r\nCREATE TABLE [t1] ([c1] INT) ON [FG_USB];\r\nGO\r\n\r\nBEGIN TRAN\r\nINSERT INTO [t1] VALUES (1);\r\nGO\r\n\r\nCHECKPOINT;\r\nGO\r\n<\/pre>\n
In another window, crash SQL Server using SHUTDOWN WITH NOWAIT<\/em>. Then unplug the USB drive and restart SQL Server.<\/p>\n
Trying to access the database will fail, because the boot page says the database wasn’t shut down cleanly, but the data file necessary to roll back our transaction isn’t available.<\/p>\n
\r\nUSE [DeferredTran];\r\nGO\r\n<\/pre>\n
\r\nMsg 945, Level 14, State 2, Line 1\r\nDatabase 'DeferredTran' cannot be opened due to inaccessible files or insufficient memory or disk space.\u00a0 See the SQL Server errorlog for details.\r\n<\/pre>\n
\r\nALTER DATABASE [DeferredTran] SET ONLINE;\r\nGO\r\n<\/pre>\n
\r\nMsg 5120, Level 16, State 5, Line 1\r\nUnable to open the physical file "G:\\FG_USB_1.ndf". Operating system error 2: "2(The system cannot find the file specified.)".\r\nMsg 945, Level 14, State 2, Line 1\r\nDatabase 'DeferredTran' cannot be opened due to inaccessible files or insufficient memory or disk space.\u00a0 See the SQL Server errorlog for details.\r\nMsg 5069, Level 16, State 1, Line 1\r\nALTER DATABASE statement failed.\r\n<\/pre>\n
By explicitly setting that file offline, we can bring the database online, albeit with a deferred transaction. BE CAREFUL!<\/strong> The only way to bring that file online again is to restore it from a backup!<\/p>\n
\r\nALTER DATABASE [DeferredTran] MODIFY FILE (NAME = N'FG_USB_1', OFFLINE);\r\nGO\r\n\r\nALTER DATABASE [DeferredTran] SET ONLINE;\r\nGO\r\n<\/pre>\n
From the error log…<\/p>\n
\r\n2011-06-24 09:54:27.650 spid51\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Setting database option ONLINE to ON for database DeferredTran.\r\n2011-06-24 09:54:27.650 spid51\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Starting up database 'DeferredTran'.\r\n2011-06-24 09:54:27.680 spid51\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Filegroup FG_USB in database DeferredTran is unavailable because it is Offline. Restore or alter the filegroup to be available.\r\n2011-06-24 09:54:27.690 spid22s\u00a0\u00a0\u00a0\u00a0\u00a0 Error: 3410, Severity: 16, State: 1.\r\n2011-06-24 09:54:27.690 spid22s\u00a0\u00a0\u00a0\u00a0\u00a0 Data in filegroup FG_USB is offline, and deferred transactions exist. Use RESTORE to recover the filegroup, or drop the filegroup if you never intend to recover it. Log truncation cannot occur until this condition is resolved.\r\n2011-06-24 09:54:27.690 spid22s\u00a0\u00a0\u00a0\u00a0\u00a0 Error: 3314, Severity: 21, State: 1.\r\n2011-06-24 09:54:27.690 spid22s\u00a0\u00a0\u00a0\u00a0\u00a0 During undoing of a logged operation in database 'DeferredTran', an error occurred at log record ID (24:101:23). Typically, the specific failure is logged previously as an error in the Windows Event Log service. Restore the database or file from a backup,\r\n2011-06-24 09:54:27.690 spid22s\u00a0\u00a0\u00a0\u00a0\u00a0 Error: 3414, Severity: 21, State: 2.\r\n2011-06-24 09:54:27.690 spid22s\u00a0\u00a0\u00a0\u00a0\u00a0 An error occurred during recovery, preventing the database 'DeferredTran' (database ID 22) from restarting. Diagnose the recovery errors and fix them, or restore from a known good backup. If errors are not corrected or expected, contact Technical Support.\r\n<\/pre>\n
Look at the part about log truncation – until we resolve the deferred transaction, it will hold the transaction log active and the log will grow and grow and grow… in this case, the database remains SUSPECT<\/em> and unusable though, but if the transaction was deferred because of a restore issue, the database may be online and usable.<\/p>\n
We can tell if there are deferred transactions\u00a0using the sys.dm_tran_database_transactions<\/em> DMV. A deferred transaction will have a transaction status with the 0x80000 bit set (undocumented).<\/p>\n
\r\nSELECT * FROM sys.dm_tran_database_transactions'\r\nWHERE [database_transaction_status] & 0x80000 = 0x80000;\r\nGO\r\n<\/pre>\n
And if we look in sys.dm_tran_locks<\/em> we’ll see that it’s holding locks that could cause our applications to block:<\/p>\n
\r\nSELECT\r\n    [request_session_id] AS [SPID],\r\n    [resource_type] AS [LockType],\r\n    DB_NAME ([resource_database_id]) AS [DB],\r\n    [resource_description] AS [Resource],\r\n    [resource_associated_entity_id] AS [ResourceID],\r\n    [request_mode] AS [Mode],\r\n    [request_status] AS [Status]\r\nFROM sys.dm_tran_locks\r\nWHERE [request_session_id] < 0;\r\nGO\r\n<\/pre>\n
\r\nSPID LockType DB\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Resource ResourceID\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Mode Status\r\n---- -------- ------------ -------- ----------------- ---- ------\r\n-3\u00a0\u00a0 RID\u00a0\u00a0\u00a0\u00a0\u00a0 DeferredTran 3:8:0\u00a0\u00a0\u00a0 72057594038779904 X\u00a0\u00a0\u00a0 GRANT\r\n-3\u00a0\u00a0 PAGE\u00a0\u00a0\u00a0\u00a0 DeferredTran 3:8\u00a0\u00a0\u00a0\u00a0\u00a0 72057594038779904 IX\u00a0\u00a0 GRANT\r\n-3\u00a0\u00a0 OBJECT\u00a0\u00a0 DeferredTran\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 2105058535\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 IX\u00a0\u00a0 GRANT\r\n<\/pre>\n
Notice that there’s no database S lock as all regular connections have, as this SPID isn’t a regular connection. You won’t be able to see the SPID in any other way.<\/p>\n
To recover from this we need to restore the offline file from a backup. We’ll also need to perform a tail-log backup so we can restore it to bring the restored file up-to-date.<\/p>\n
\r\nBACKUP LOG [DeferredTran] TO DISK = N'C:\\SQLskills\\DeferredTran_log.bck';\r\nGO\r\n<\/pre>\n
\r\nMsg 945, Level 14, State 2, Line 1\r\nDatabase 'DeferredTran' cannot be opened due to inaccessible files or insufficient memory or disk space.\u00a0 See the SQL Server errorlog for details.\r\nMsg 3013, Level 16, State 1, Line 1\r\nBACKUP LOG is terminating abnormally.\r\n<\/pre>\n
We have to use WITH NO_TRUNCATE…<\/p>\n
\r\nBACKUP LOG [DeferredTran] TO DISK = N'C:\\SQLskills\\DeferredTran_log.bck'\r\nWITH NO_TRUNCATE, INIT;\r\nGO\r\n\r\nRESTORE DATABASE [DeferredTran] FILE = N'FG_USB_1'\r\nFROM DISK = N'C:\\SQLskills\\DeferredTran.bck'\r\nWITH MOVE N'FG_USB_1' TO N'C:\\SQLskills\\FG_USB_1.ndf', NORECOVERY;\r\nGO\r\n\r\nRESTORE LOG [DeferredTran]\r\nFROM DISK = N'C:\\SQLskills\\DeferredTran_log.bck'\r\nWITH NORECOVERY;\r\nGO\r\n\r\nRESTORE DATABASE [DeferredTran] WITH RECOVERY;\r\nGO;\r\n<\/pre>\n
And\u00a0the output from the final RESTORE<\/em> statement is:<\/p>\n
\r\n1 transactions rolled back in database 'DeferredTran' (22). This is an informational message only. No user action is required.\r\n\r\nRESTORE DATABASE successfully processed 0 pages in 0.006 seconds (0.000 MB\/sec).\r\n<\/pre>\n
So there you are – an easy way to create deferred transactions and negative SPIDs to play with.<\/p>\n
Enjoy!<\/p>\n","protected":false},"excerpt":{"rendered":"
Every so often I get asked how to deal with SPID -2 and once in a blue moon I get asked about SPID -3. Neither of these SPIDs will show up in sysprocesses or sys.dm_exec_requests or sys.dm_exec_sessions, but they’re both valid SPIDs. SPID -2 is an orphaned DTC transaction. SPID -3 is a deferred transaction. […]<\/p>\n","protected":false},"author":5,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[35],"tags":[],"class_list":["post-558","post","type-post","status-publish","format-standard","hentry","category-disaster-recovery"],"yoast_head":"\nDisaster recovery 101: dealing with negative SPIDS (-2 and -3) - Paul S. Randal<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/www.sqlskills.com\/blogs\/paul\/disaster-recovery-101-dealing-with-negative-spids-2-and-3\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Disaster recovery 101: dealing with negative SPIDS (-2 and -3) - Paul S. Randal\" \/>\n<meta property=\"og:description\" content=\"Every so often I get asked how to deal with SPID -2 and once in a blue moon I get asked about SPID -3. Neither of these SPIDs will show up in sysprocesses or sys.dm_exec_requests or sys.dm_exec_sessions, but they’re both valid SPIDs. SPID -2 is an orphaned DTC transaction. SPID -3 is a deferred transaction. […]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.sqlskills.com\/blogs\/paul\/disaster-recovery-101-dealing-with-negative-spids-2-and-3\/\" \/>\n<meta property=\"og:site_name\" content=\"Paul S. Randal\" \/>\n<meta property=\"article:published_time\" content=\"2011-06-24T03:19:00+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2017-07-25T16:55:05+00:00\" \/>\n<meta name=\"author\" content=\"Paul Randal\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Paul Randal\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"6 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/disaster-recovery-101-dealing-with-negative-spids-2-and-3\/\",\"url\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/disaster-recovery-101-dealing-with-negative-spids-2-and-3\/\",\"name\":\"Disaster recovery 101: dealing with negative SPIDS (-2 and -3) - Paul S. Randal\",\"isPartOf\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/#website\"},\"datePublished\":\"2011-06-24T03:19:00+00:00\",\"dateModified\":\"2017-07-25T16:55:05+00:00\",\"author\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/#\/schema\/person\/ffcec826c18782e1e0adf173826a7fce\"},\"breadcrumb\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/disaster-recovery-101-dealing-with-negative-spids-2-and-3\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.sqlskills.com\/blogs\/paul\/disaster-recovery-101-dealing-with-negative-spids-2-and-3\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/disaster-recovery-101-dealing-with-negative-spids-2-and-3\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Disaster recovery 101: dealing with negative SPIDS (-2 and -3)\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/#website\",\"url\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/\",\"name\":\"Paul S. Randal\",\"description\":\"In Recovery...\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/#\/schema\/person\/ffcec826c18782e1e0adf173826a7fce\",\"name\":\"Paul Randal\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/0b6a266bba2f088f2551ef529293001bd73bf026bc1908b9866728c062beeeb6?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/0b6a266bba2f088f2551ef529293001bd73bf026bc1908b9866728c062beeeb6?s=96&d=mm&r=g\",\"caption\":\"Paul Randal\"},\"sameAs\":[\"http:\/\/3.209.169.194\/blogs\/paul\"],\"url\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/author\/paul\/\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"Disaster recovery 101: dealing with negative SPIDS (-2 and -3) - Paul S. Randal","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/www.sqlskills.com\/blogs\/paul\/disaster-recovery-101-dealing-with-negative-spids-2-and-3\/","og_locale":"en_US","og_type":"article","og_title":"Disaster recovery 101: dealing with negative SPIDS (-2 and -3) - Paul S. Randal","og_description":"Every so often I get asked how to deal with SPID -2 and once in a blue moon I get asked about SPID -3. Neither of these SPIDs will show up in sysprocesses or sys.dm_exec_requests or sys.dm_exec_sessions, but they’re both valid SPIDs. SPID -2 is an orphaned DTC transaction. SPID -3 is a deferred transaction. […]","og_url":"https:\/\/www.sqlskills.com\/blogs\/paul\/disaster-recovery-101-dealing-with-negative-spids-2-and-3\/","og_site_name":"Paul S. Randal","article_published_time":"2011-06-24T03:19:00+00:00","article_modified_time":"2017-07-25T16:55:05+00:00","author":"Paul Randal","twitter_misc":{"Written by":"Paul Randal","Est. reading time":"6 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebPage","@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/disaster-recovery-101-dealing-with-negative-spids-2-and-3\/","url":"https:\/\/www.sqlskills.com\/blogs\/paul\/disaster-recovery-101-dealing-with-negative-spids-2-and-3\/","name":"Disaster recovery 101: dealing with negative SPIDS (-2 and -3) - Paul S. Randal","isPartOf":{"@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/#website"},"datePublished":"2011-06-24T03:19:00+00:00","dateModified":"2017-07-25T16:55:05+00:00","author":{"@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/#\/schema\/person\/ffcec826c18782e1e0adf173826a7fce"},"breadcrumb":{"@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/disaster-recovery-101-dealing-with-negative-spids-2-and-3\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.sqlskills.com\/blogs\/paul\/disaster-recovery-101-dealing-with-negative-spids-2-and-3\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/disaster-recovery-101-dealing-with-negative-spids-2-and-3\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.sqlskills.com\/blogs\/paul\/"},{"@type":"ListItem","position":2,"name":"Disaster recovery 101: dealing with negative SPIDS (-2 and -3)"}]},{"@type":"WebSite","@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/#website","url":"https:\/\/www.sqlskills.com\/blogs\/paul\/","name":"Paul S. Randal","description":"In Recovery...","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.sqlskills.com\/blogs\/paul\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/#\/schema\/person\/ffcec826c18782e1e0adf173826a7fce","name":"Paul Randal","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/#\/schema\/person\/image\/","url":"https:\/\/secure.gravatar.com\/avatar\/0b6a266bba2f088f2551ef529293001bd73bf026bc1908b9866728c062beeeb6?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/0b6a266bba2f088f2551ef529293001bd73bf026bc1908b9866728c062beeeb6?s=96&d=mm&r=g","caption":"Paul Randal"},"sameAs":["http:\/\/3.209.169.194\/blogs\/paul"],"url":"https:\/\/www.sqlskills.com\/blogs\/paul\/author\/paul\/"}]}},"_links":{"self":[{"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/posts\/558","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/users\/5"}],"replies":[{"embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/comments?post=558"}],"version-history":[{"count":0,"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/posts\/558\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/media?parent=558"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/categories?post=558"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/tags?post=558"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}