Archive

Posts Tagged ‘SharePoint database in suspect’

SharePoint Central Administration Database in Suspect Mode!

Assalam o Alikum / Hi,

How do you feel when you need to open SharePoint Central Administration and ‘Connection Close’ message appears and SharePoint_AdminContent database halts in suspect mode? Yes I know how it feels! So to reduce the pain and get your smiles back I am writing this block. After googling so much, well 90% of the blog says to restore the database and bla bla but Developers are not used to take backups of the databases for their development environment.

Let’s cut the crap and jump to solution. Few SQL queries will fix this issue. Following are the details.

When I opened central admin it displayed Connection: close, then I checked my Database for SharePoint Central Administration which is SharePoint_AdminContent_eba61c34-8fb2-4c18-badd-d50aacfb05a7

So to fix the issue I searched on internet but most of them said to restore the database from its backup, which was not the case for me. One blog helped me a lot and I got the following queries from that. Moreover there are some steps involved as precautionary measures.

  1. Go to your MSSQL\Data files that reside under: C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA and copy your SharePoint_AdminContent_xxxx … ‘.mdf’ and ‘.ldf’ file on saperate location for backup
  2. You might need to stop the SQL Service to copy those files, remember to Start SQL service after copying those files.

  1. Open you SQL using SQL Server Management Studio and click -> New Query and it opens up your SQL Query editor, copy and paste the query below and change the highlighted to your database name:Use master
  2. Paste the following Queries and make sure changing Name of the SharePoint Admin content database as I have specified my database name highlighted in query.

 

–Verify whether Database has any issues

EXEC
sp_resetstatus
SharePoint_AdminContent_eba61c34-8fb2-4c18-badd-d50aacfb05a7.mdf

 

—Alter database and put it on Emergency Mode

ALTER
DATABASESharePoint_AdminContent_eba61c34-8fb2-4c18-badd-d50aacfb05a7SET
EMERGENCY

DBCC checkdb(‘SharePoint_AdminContent_eba61c34-8fb2-4c18-badd-d50aacfb05a7)

 

–Set the database in the Single User mode

ALTER
DATABASESharePoint_AdminContent_eba61c34-8fb2-4c18-badd-d50aacfb05a7SET
SINGLE_USER
WITH
ROLLBACK
IMMEDIATE

 

–Repair the database and allow data loss

DBCC CheckDB(‘SharePoint_AdminContent_eba61c34-8fb2-4c18-badd-d50aacfb05a7,REPAIR_ALLOW_DATA_LOSS)

 

–Set the database back to Multi-User mode

ALTER
DATABASESharePoint_AdminContent_eba61c34-8fb2-4c18-badd-d50aacfb05a7SET
MULTI_USER

 

–Ensure Database is reset

EXEC
sp_resetstatus
‘SharePoint_AdminContent_eba61c34-8fb2-4c18-badd-d50aacfb05a7

 


Execute all the commands in your SQL Query Editor, it will take some time depending upon the size of database.
Go back to your SQL Management Studio and you will see the (Suspect) mode issue against the Content/Config database is fixed and the database got fully repaired and restored.

To verify reopen your SharePoint Central Administration. This also applies if your Web Application database halts in (Suspect) state.