{"id":2894,"date":"2016-06-14T13:57:46","date_gmt":"2016-06-14T13:57:46","guid":{"rendered":"http:\/\/radixcloud.com\/?p=2894"},"modified":"2016-06-15T07:37:34","modified_gmt":"2016-06-15T07:37:34","slug":"fix-suspect-database","status":"publish","type":"post","link":"https:\/\/radixcloud.com\/en\/2016\/06\/14\/fix-suspect-database\/","title":{"rendered":"How to fix a suspect database"},"content":{"rendered":"

<\/p>\n

<\/h1>\n

One of the relatively common situations during disaster recovery is a suspect database. The suspect flag is a mode which is set to the database by SQL Server in several cases. When it comes to disaster recovery, mainly databases are marked as suspect in case of a hardware failure, improper shutdown of the DB server, DB files corruption, etc. Strangely enough, it can happen when you restore the entire DB server from a valid snapshot (it happened to me).<\/p>\n

In such cases, you will need to bring the database back to online mode. At this point you need to be aware that during the process there may be some data loss. This can occur if there are incomplete transactions which will need to be rolled back. In general,<\/p>\n

What you will need for the procedure is the SQL Server Management Studio. I will use the Prime database which I corrupted in the previous post <\/a>\ud83d\ude42<\/p>\n

The first thing you need to do is to turn off the suspect flag (which does not mean that the DB is fixed). You will need to execute the following command:<\/p>\n

EXEC <\/span>sp_resetstatus<\/span> ‘Prime’<\/span><\/p>\n

You will get the following message:<\/p>\n

\"1\"<\/a><\/p>\n

The next step is to set the database into emergency mode, which will make it a read-only with the following command:<\/p>\n

ALTER DATABASE<\/span> Prime SET EMERGENCY<\/span><\/p>\n

\"2\"<\/a><\/p>\n

Then, perform a consistency check. The output will display any possible errors.<\/p>\n

DBCC<\/span> checkdb(‘Prime<\/span>‘)<\/p>\n

\"3\"<\/a><\/p>\n

In the next step we do a rollback of any pending transactions. This step also brings the DB in a single user mode.<\/p>\n

ALTER DATABASE<\/span> Prime SET SINGLE_USER WITH ROLLBACK IMMEDIATE<\/span><\/p>\n

\"4\"<\/a><\/p>\n

Finally, we will correct any reported errors. Keep track of the time, since this may be a lengthy process.<\/p>\n

DBCC<\/span> CheckDB (‘Prime<\/span>‘, REPAIR_ALLOW_DATA_LOSS)<\/p>\n

\"5\"<\/a><\/p>\n

To wrap up the procedure, we will need to enable the multi user access and bring it online.<\/p>\n

ALTER DATABASE<\/span> Prime SET MULTI_USER<\/span><\/p>\n

\"6\"<\/a><\/p>\n

If we check the DB status now, it will be shown as online. We can browse through the tables and query data:<\/p>\n

select<\/span> DATABASEPROPERTYE<\/span>X(‘Prime<\/span>‘, ‘status’<\/span>)<\/p>\n

\"7\"<\/a><\/p>\n

 <\/p>\n

Jana Georgievska,<\/p>\n

DBA<\/p>","protected":false},"excerpt":{"rendered":"

One of the relatively common situations during disaster recovery is a suspect database. The suspect flag is a mode which is set to the database by SQL Server in several […]<\/p>\n","protected":false},"author":8,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_mi_skip_tracking":false,"_exactmetrics_sitenote_active":false,"_exactmetrics_sitenote_note":"","_exactmetrics_sitenote_category":0,"footnotes":""},"categories":[3],"tags":[25,66,52,57,68,67,56,51,38,37,55,47],"yoast_head":"\nHow to fix a suspect database | Radix Cloud<\/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:\/\/radixcloud.com\/en\/2016\/06\/14\/fix-suspect-database\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"How to fix a suspect database | Radix Cloud\" \/>\n<meta property=\"og:description\" content=\"One of the relatively common situations during disaster recovery is a suspect database. The suspect flag is a mode which is set to the database by SQL Server in several […]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/radixcloud.com\/en\/2016\/06\/14\/fix-suspect-database\/\" \/>\n<meta property=\"og:site_name\" content=\"Radix Cloud\" \/>\n<meta property=\"article:publisher\" content=\"https:\/\/www.facebook.com\/radixtechnologies\" \/>\n<meta property=\"article:published_time\" content=\"2016-06-14T13:57:46+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2016-06-15T07:37:34+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/radixcloud.com\/wp-content\/uploads\/2016\/06\/1-1024x576.png\" \/>\n<meta name=\"author\" content=\"Hristijan Peshevski\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:creator\" content=\"@RadixCloud\" \/>\n<meta name=\"twitter:site\" content=\"@RadixCloud\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Hristijan Peshevski\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"2 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\/\/radixcloud.com\/en\/2016\/06\/14\/fix-suspect-database\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/radixcloud.com\/en\/2016\/06\/14\/fix-suspect-database\/\"},\"author\":{\"name\":\"Hristijan Peshevski\",\"@id\":\"https:\/\/radixcloud.com\/en\/#\/schema\/person\/cee27ca63bcfe60ba72cd3ee0f2a894e\"},\"headline\":\"How to fix a suspect database\",\"datePublished\":\"2016-06-14T13:57:46+00:00\",\"dateModified\":\"2016-06-15T07:37:34+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/radixcloud.com\/en\/2016\/06\/14\/fix-suspect-database\/\"},\"wordCount\":356,\"commentCount\":0,\"publisher\":{\"@id\":\"https:\/\/radixcloud.com\/en\/#organization\"},\"keywords\":[\"Cloud\",\"databases\",\"DBA\",\"DBA Management\",\"Disaster Recovery\",\"DR\",\"MS SQL\",\"Prime Database\",\"Radix Technologies\",\"RadixCloud\",\"Servers\",\"Swiss IT\"],\"articleSection\":[\"News Blog\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\/\/radixcloud.com\/en\/2016\/06\/14\/fix-suspect-database\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/radixcloud.com\/en\/2016\/06\/14\/fix-suspect-database\/\",\"url\":\"https:\/\/radixcloud.com\/en\/2016\/06\/14\/fix-suspect-database\/\",\"name\":\"How to fix a suspect database | Radix Cloud\",\"isPartOf\":{\"@id\":\"https:\/\/radixcloud.com\/en\/#website\"},\"datePublished\":\"2016-06-14T13:57:46+00:00\",\"dateModified\":\"2016-06-15T07:37:34+00:00\",\"breadcrumb\":{\"@id\":\"https:\/\/radixcloud.com\/en\/2016\/06\/14\/fix-suspect-database\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/radixcloud.com\/en\/2016\/06\/14\/fix-suspect-database\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/radixcloud.com\/en\/2016\/06\/14\/fix-suspect-database\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/radixcloud.com\/en\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"How to fix a suspect database\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/radixcloud.com\/en\/#website\",\"url\":\"https:\/\/radixcloud.com\/en\/\",\"name\":\"Radix Cloud\",\"description\":\"Managed Services for the Cloud\",\"publisher\":{\"@id\":\"https:\/\/radixcloud.com\/en\/#organization\"},\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/radixcloud.com\/en\/?s={search_term_string}\"},\"query-input\":\"required name=search_term_string\"}],\"inLanguage\":\"en-US\"},{\"@type\":\"Organization\",\"@id\":\"https:\/\/radixcloud.com\/en\/#organization\",\"name\":\"Radix Cloud Technologies\",\"url\":\"https:\/\/radixcloud.com\/en\/\",\"logo\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/radixcloud.com\/en\/#\/schema\/logo\/image\/\",\"url\":\"https:\/\/radixcloud.com\/wp-content\/uploads\/2015\/08\/radix_cloud_logo_x2.png\",\"contentUrl\":\"https:\/\/radixcloud.com\/wp-content\/uploads\/2015\/08\/radix_cloud_logo_x2.png\",\"width\":298,\"height\":120,\"caption\":\"Radix Cloud Technologies\"},\"image\":{\"@id\":\"https:\/\/radixcloud.com\/en\/#\/schema\/logo\/image\/\"},\"sameAs\":[\"https:\/\/www.facebook.com\/radixtechnologies\",\"https:\/\/twitter.com\/RadixCloud\",\"https:\/\/www.linkedin.com\/company\/radix-technologies_2?trk=nav_account_sub_nav_company_admin\"]},{\"@type\":\"Person\",\"@id\":\"https:\/\/radixcloud.com\/en\/#\/schema\/person\/cee27ca63bcfe60ba72cd3ee0f2a894e\",\"name\":\"Hristijan Peshevski\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/radixcloud.com\/en\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/95ce1c5411326181c8518f8413c43bc0?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/95ce1c5411326181c8518f8413c43bc0?s=96&d=mm&r=g\",\"caption\":\"Hristijan Peshevski\"},\"url\":\"https:\/\/radixcloud.com\/en\/author\/kiko\/\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"How to fix a suspect database | Radix Cloud","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:\/\/radixcloud.com\/en\/2016\/06\/14\/fix-suspect-database\/","og_locale":"en_US","og_type":"article","og_title":"How to fix a suspect database | Radix Cloud","og_description":"One of the relatively common situations during disaster recovery is a suspect database. The suspect flag is a mode which is set to the database by SQL Server in several […]","og_url":"https:\/\/radixcloud.com\/en\/2016\/06\/14\/fix-suspect-database\/","og_site_name":"Radix Cloud","article_publisher":"https:\/\/www.facebook.com\/radixtechnologies","article_published_time":"2016-06-14T13:57:46+00:00","article_modified_time":"2016-06-15T07:37:34+00:00","og_image":[{"url":"https:\/\/radixcloud.com\/wp-content\/uploads\/2016\/06\/1-1024x576.png"}],"author":"Hristijan Peshevski","twitter_card":"summary_large_image","twitter_creator":"@RadixCloud","twitter_site":"@RadixCloud","twitter_misc":{"Written by":"Hristijan Peshevski","Est. reading time":"2 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/radixcloud.com\/en\/2016\/06\/14\/fix-suspect-database\/#article","isPartOf":{"@id":"https:\/\/radixcloud.com\/en\/2016\/06\/14\/fix-suspect-database\/"},"author":{"name":"Hristijan Peshevski","@id":"https:\/\/radixcloud.com\/en\/#\/schema\/person\/cee27ca63bcfe60ba72cd3ee0f2a894e"},"headline":"How to fix a suspect database","datePublished":"2016-06-14T13:57:46+00:00","dateModified":"2016-06-15T07:37:34+00:00","mainEntityOfPage":{"@id":"https:\/\/radixcloud.com\/en\/2016\/06\/14\/fix-suspect-database\/"},"wordCount":356,"commentCount":0,"publisher":{"@id":"https:\/\/radixcloud.com\/en\/#organization"},"keywords":["Cloud","databases","DBA","DBA Management","Disaster Recovery","DR","MS SQL","Prime Database","Radix Technologies","RadixCloud","Servers","Swiss IT"],"articleSection":["News Blog"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/radixcloud.com\/en\/2016\/06\/14\/fix-suspect-database\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/radixcloud.com\/en\/2016\/06\/14\/fix-suspect-database\/","url":"https:\/\/radixcloud.com\/en\/2016\/06\/14\/fix-suspect-database\/","name":"How to fix a suspect database | Radix Cloud","isPartOf":{"@id":"https:\/\/radixcloud.com\/en\/#website"},"datePublished":"2016-06-14T13:57:46+00:00","dateModified":"2016-06-15T07:37:34+00:00","breadcrumb":{"@id":"https:\/\/radixcloud.com\/en\/2016\/06\/14\/fix-suspect-database\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/radixcloud.com\/en\/2016\/06\/14\/fix-suspect-database\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/radixcloud.com\/en\/2016\/06\/14\/fix-suspect-database\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/radixcloud.com\/en\/"},{"@type":"ListItem","position":2,"name":"How to fix a suspect database"}]},{"@type":"WebSite","@id":"https:\/\/radixcloud.com\/en\/#website","url":"https:\/\/radixcloud.com\/en\/","name":"Radix Cloud","description":"Managed Services for the Cloud","publisher":{"@id":"https:\/\/radixcloud.com\/en\/#organization"},"potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/radixcloud.com\/en\/?s={search_term_string}"},"query-input":"required name=search_term_string"}],"inLanguage":"en-US"},{"@type":"Organization","@id":"https:\/\/radixcloud.com\/en\/#organization","name":"Radix Cloud Technologies","url":"https:\/\/radixcloud.com\/en\/","logo":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/radixcloud.com\/en\/#\/schema\/logo\/image\/","url":"https:\/\/radixcloud.com\/wp-content\/uploads\/2015\/08\/radix_cloud_logo_x2.png","contentUrl":"https:\/\/radixcloud.com\/wp-content\/uploads\/2015\/08\/radix_cloud_logo_x2.png","width":298,"height":120,"caption":"Radix Cloud Technologies"},"image":{"@id":"https:\/\/radixcloud.com\/en\/#\/schema\/logo\/image\/"},"sameAs":["https:\/\/www.facebook.com\/radixtechnologies","https:\/\/twitter.com\/RadixCloud","https:\/\/www.linkedin.com\/company\/radix-technologies_2?trk=nav_account_sub_nav_company_admin"]},{"@type":"Person","@id":"https:\/\/radixcloud.com\/en\/#\/schema\/person\/cee27ca63bcfe60ba72cd3ee0f2a894e","name":"Hristijan Peshevski","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/radixcloud.com\/en\/#\/schema\/person\/image\/","url":"https:\/\/secure.gravatar.com\/avatar\/95ce1c5411326181c8518f8413c43bc0?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/95ce1c5411326181c8518f8413c43bc0?s=96&d=mm&r=g","caption":"Hristijan Peshevski"},"url":"https:\/\/radixcloud.com\/en\/author\/kiko\/"}]}},"_links":{"self":[{"href":"https:\/\/radixcloud.com\/en\/wp-json\/wp\/v2\/posts\/2894"}],"collection":[{"href":"https:\/\/radixcloud.com\/en\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/radixcloud.com\/en\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/radixcloud.com\/en\/wp-json\/wp\/v2\/users\/8"}],"replies":[{"embeddable":true,"href":"https:\/\/radixcloud.com\/en\/wp-json\/wp\/v2\/comments?post=2894"}],"version-history":[{"count":3,"href":"https:\/\/radixcloud.com\/en\/wp-json\/wp\/v2\/posts\/2894\/revisions"}],"predecessor-version":[{"id":2904,"href":"https:\/\/radixcloud.com\/en\/wp-json\/wp\/v2\/posts\/2894\/revisions\/2904"}],"wp:attachment":[{"href":"https:\/\/radixcloud.com\/en\/wp-json\/wp\/v2\/media?parent=2894"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/radixcloud.com\/en\/wp-json\/wp\/v2\/categories?post=2894"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/radixcloud.com\/en\/wp-json\/wp\/v2\/tags?post=2894"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}