Drop database doesn't work when database configured for replication

I incurred this situation when one of the my test server configured for Transnational replication some time before and now I wanted to restore a fresh database copy on top of it, it failed with error saying 'Database is in use'.

Then I tried working to drop the database.  Since it is involved on replication it says 'database cannot be dropped unless you remove the replication'

Then tried removing the replication it says  'the object used for the replication owner is not DBO or you don't have access to remove the replication', though I am trying with sysadmin privileges.


Finally I used one of the system proc 'sp removedbreplication' and passed the Subscriber and Publisher database names as parameters then it got dropped both the subscriber and publisher under Replication Folder.


sp_removedbreplication @publisher
sp_removedbreplication @subscriber

Hope this information helps to remove the replication when it gives you an error !!!!

SQL Server database corrupted and in SUSPECT mode, how to fix it

As a routine DBA issue, sometimes the Database goes to suspect mode and not accessible to any of  the users.  When we verfiy the SQL Server logs it says the database consistency issue  which needs to be repaired. So how do one fix the database issue......

The possibilities are the database pages might have corrupted due to inconsistency where
the particular table resides.  We can identify these tables by using various methods;

Refer MSDB database and suspect_pages table for the list of the corrupted pages.
use MSDB
Go
select * from suspect_pages
Go

Alternatively run the below command for identify the corrupted object details

DBCC CHECKDB (DB_NAME) WITH ALL_ERRORMSGS, NO_INFOMSGS;
Error :  Table error : Object ID 0, Index ID -1, Partition ID 0 ..........
CheckDB found 0 allocation errors and 1 consistency errors not associated with any single
object.
Repair_Allow_data_loss is the minimum repair level for the errors found by DBCC CHECKDB
(db_name)

Repair Method :
You can follow the below method for getting the database online by removing the corrupted
portion, but make sure you have proper approvals before doing so.  Because it removes the
corrupted pages completely from the databases.
use MASTER
GO
alter database db_name set single_user ;
Go
DBCC CHECKDB (db_name,'repair_allow_data_loss')
Go

this will give the results something like below, once it is repaired then you can make
the database into multi_user to give it to the users with proper backups being taken.

GO
alter database db_name set multi_user ;

DBCC results :
DBCC results for 'TEST'.
Service Broker Msg 9675, State 1: Message Types analyzed: 14.
Service Broker Msg 9676, State 1: Service Contracts analyzed: 6.
Service Broker Msg 9667, State 1: Services analyzed: 3.
Service Broker Msg 9668, State 1: Service Queues analyzed: 3.
Service Broker Msg 9669, State 1: Conversation Endpoints analyzed: 0.
Service Broker Msg 9674, State 1: Conversation Groups analyzed: 0.
Service Broker Msg 9670, State 1: Remote Service Bindings analyzed: 0.
Service Broker Msg 9605, State 1: Conversation Priorities analyzed: 0.
DBCC results for 'sys.sysrscols'.
There are 870 rows in 12 pages for object "sys.sysrscols".
DBCC results for 'sys.sysrowsets'.
There are 124 rows in 1 pages for object "sys.sysrowsets".
DBCC results for 'sys.sysclones'.
There are 0 rows in 0 pages for object "sys.sysclones".
DBCC results for 'sys.sysallocunits'.
There are 138 rows in 2 pages for object "sys.sysallocunits".
DBCC results for 'sys.sysfiles1'.
There are 2 rows in 1 pages for object "sys.sysfiles1".
DBCC results for 'sys.sysseobjvalues'.
There are 0 rows in 0 pages for object "sys.sysseobjvalues".
DBCC results for 'sys.syspriorities'.
There are 0 rows in 0 pages for object "sys.syspriorities".
DBCC results for 'sys.sysdbfrag'.
There are 0 rows in 0 pages for object "sys.sysdbfrag".
DBCC results for 'sys.sysfgfrag'.
There are 0 rows in 0 pages for object "sys.sysfgfrag".
DBCC results for 'sys.sysdbfiles'.
There are 2 rows in 1 pages for object "sys.sysdbfiles".
DBCC results for 'sys.syspru'.
There are 0 rows in 0 pages for object "sys.syspru".
DBCC results for 'sys.sysbrickfiles'.
There are 0 rows in 0 pages for object "sys.sysbrickfiles".
DBCC results for 'sys.sysphfg'.
There are 1 rows in 1 pages for object "sys.sysphfg".
DBCC results for 'sys.sysprufiles'.
There are 2 rows in 1 pages for object "sys.sysprufiles".
DBCC results for 'sys.sysftinds'.
There are 0 rows in 0 pages for object "sys.sysftinds".
DBCC results for 'sys.sysowners'.
There are 14 rows in 1 pages for object "sys.sysowners".
DBCC results for 'sys.sysdbreg'.
There are 0 rows in 0 pages for object "sys.sysdbreg".
DBCC results for 'sys.sysprivs'.
There are 136 rows in 1 pages for object "sys.sysprivs".
DBCC results for 'sys.sysschobjs'.
There are 2180 rows in 29 pages for object "sys.sysschobjs".
DBCC results for 'sys.syscolpars'.
There are 694 rows in 11 pages for object "sys.syscolpars".
DBCC results for 'sys.sysxlgns'.
There are 0 rows in 0 pages for object "sys.sysxlgns".
DBCC results for 'sys.sysxsrvs'.
There are 0 rows in 0 pages for object "sys.sysxsrvs".
DBCC results for 'sys  ...................   So on