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

How to read SQL Server Error log using Query Analyzer

Reading SQL Error log typically we do by going through the Management  then SQL Server error log and open respective one .  But it is little difficult to trace it out only the particular information out of the bunch of records if you activated all kind of log info.   So alternatively it could be done using SSMS by executing extended system procedures.  

xp_readerrorlog         returns the latest error log
xp_readerrorlog 1      returns the latest archived error log
xp_readerrorlog 2      returns the previous archived error log
 ..........  and so on.

You can try inserting the log records into temp table then filter only the information that you might required to analyze the issue.  I hope this would help and save the time instead of checking complete error log manually.
      

Understand the index usage details in Sql server

Understand the index usage from DMVs  in Sql server :  The below will retrieve the indexes performed look ups, scans, seeks by the user with the utilized dates.  If we see any unused indexes, could be deleted to improve the performance for Insert,update,delete statements.

select object_name(a.object_id) TABLE_NAME, a.index_id,b.name IndexName,
b.type_desc,a.last_user_seek,a.last_user_scan,a.last_user_lookup,a.last_user_update
from sys.dm_db_index_usage_stats a inner join sys.indexes b
on a.object_id = b.object_id and a.index_id = b.index_id
where a.database_id =6 and a.object_id in (object_id('xxx'), object_id ('xxx'))
order by A.OBJECT_ID,a.index_id

Find Orphan users from all databases exists in the SQL server

It is a typical requirement to find and fix the Orphan users in restored database to access the database with its underlying privileges.  In order to achieve this I have prepared a small script to find the Orphan users in all the databses exists in server.  Hope this helps to all, who are looking for this info in MS Sql server.

Script :

Declare @dbs table (id int identity(1,1), db varchar(100))

insert into @dbs (db)
select name from master.sys.databases where database_id > 4 order by name

--select * from @dbs


Declare @findOrphans table (db varchar(100), Orpuser varchar (100),USID varchar(200))
Declare @fixorphans table (db varchar(100), Script varchar (500))


Declare @a varchar(1000)
Declare @b tinyint
Declare @c tinyint
Declare @d varchar(100)

set @b = 1
select @c = MAX (id) from @dbs

while @b < @c
Begin
select @d = db from @dbs where id=@b
select @a = db+'.dbo.sp_change_users_login report ' from @dbs where id = @b
insert into @findorphans (Orpuser, UsID) exec (@a)
Update @findOrphans set db = @d where db is null
set @b = @b+1
end

select * from @findorphans

Slipstream Installation in Sql server 2008

Sql server installation has ability called slipstream which performs the service pack installation along with the software installation.  This saves time, pleas refer below link.

http://support.microsoft.com/kb/955392

Findout SQL server Job notification details

How to findout all the notification details which are configured in the server, like there are some jobs which are not sending notifications for successive / failure actions, so we need to fix that. By identifying each and everyone manually it takes a long time when there are no. of jobs, so following query will help to identify the notification details.

select a.name, case when a.enabled =1 then 'Enabled' else 'Disabled' end Status, case when notify_level_email = 1 then 'Job success' when notify_level_email = 2 then 'Job failure' end NotifyEmail, b.name
from sysjobs a left outer join (Select * from Sysoperators ) b on a.notify_email_operator_id = b.id
union all
select a.name,case when a.enabled =1 then 'Enabled' else 'Disabled' end Status, case when notify_level_page = 1 then 'Job success' when notify_level_page = 2 then 'Job failure' end NotifyPage, c.name
from sysjobs a left outer join (Select * from Sysoperators ) c on a.notify_page_operator_id = c.id order by a.name