October 15, 2010 at 12:36 pm
Hi..
everybody, i am doing the database mirroring.
after disabling the mirroring,my both
primary server instance and mirror database are in recovery state.
how is that possible??
so if anybody know, what to do..so help me..
thanks in advance..
October 15, 2010 at 1:55 pm
can you provide a bit of input.
- can you find any info in your sqlserver errorlog file(s) ?
- how did you set up your mirroring (ddl please)
- what kind of mirroring did you set up (witness or not, ..)
- what did you do to disable the mirroring ? (ddl please)
- you should be able to bring these database to an online state.
/*
* DB Mirroring : followup
*
*/
/* show mirrored databases and their mirror-state */
Select db_name(database_id) as dbName
, *
from sys.database_mirroring
Where mirroring_guid is not null -- show only mirrored databases
order by dbName;
/* the "simple" proc */
exec msdb..sp_dbmmonitorresults 'MYDataBase'
SELECT principal_server_name, mirror_server_name,
database_name, safety_level_desc
FROM sys.database_mirroring_witnesses
/*
* to be run at the WITNESS
*/
Select *
from sys.database_mirroring_witnesses
order by database_name, principal_server_name;
/*
* Make mirror the principal
*/
-- Alter database [TheDb] set partner failover;
/*
* Make mirror the principal with potential dataloss (if unsynced !)
*/
-- Alter database [TheDb] set partner FORCE_SERVICE_ALLOW_DATA_LOSS;
-- db will be in state (Principal, Suspended) !!
-- You need to reactivate mirroring !! using: ALTER DATABASE [TheDb] SET PARTNER RESUME;
/*
* Suspendig / resuming
*/
-- at the CURRENT Principal server
-- ALTER DATABASE [TheDb] SET PARTNER SUSPEND;
-- ALTER DATABASE [TheDb] SET PARTNER RESUME;
/*
* Ending DbMirroring
*/
-- at the CURRENT Principal server
-- ALTER DATABASE [TheDb] SET PARTNER OFF;
-- at the CURRENT Mirror server the db stays in "recovering" mode.
-- If you want to activate it, you can:
-- Restore database [TheDb] with recovery:
/*
* follow up queues
*/
/* endpoint overview */
select e.*
, t.*
FROM sys.database_mirroring_endpoints e
INNER JOIN sys.tcp_endpoints t
ON e.endpoint_id = t.endpoint_id
order by e.name;
/* DBM connections */
select *
from sys.dm_db_mirroring_connections
go
select *
from sys.event_notifications
select *
from sys.dm_os_wait_stats
where wait_type like '%MIRROR%'
order by wait_type
SELECT [database_id], db_name(database_id) as DbName
,[retention_period]
,[time_behind]
,[enable_time_behind]
,[send_queue]
,[enable_send_queue]
,[redo_queue]
,[enable_redo_queue]
,[average_delay]
,[enable_average_delay]
FROM [msdb].[dbo].[dbm_monitor_alerts]
order by [database_id]
SELECT [database_id], db_name(database_id) as DbName
,[role]
,[status]
,[witness_status]
,[log_flush_rate]
,[send_queue_size]
,[send_rate]
,[redo_queue_size]
,[redo_rate]
,[transaction_delay]
,[transactions_per_sec]
,[time]
,[end_of_log_lsn]
,[failover_lsn]
,[local_time]
FROM [msdb].[dbo].[dbm_monitor_data]
order by [database_id], [local_time] desc
/*
select *
from sys.messages
where text like '%mirror%'
and language_id = 1033
order by message_id
*/
/* We use SQLServer events to monitor db state changes */
----/* Monitor db state changes using WMI alerts */
----
----USE [msdb]
----GO
----EXEC msdb.dbo.sp_add_operator @name=N'DBA',
---- @enabled=1,
---- @pager_days=0,
---- @email_address=N'Wachtdienst.DBA@arcelormittal.com'
----GO
----/*
----Below is a list of the different state changes that can be monitored.
----Additional information can be found here Database Mirroring State Change Event Class.
----0 = Null Notification
----1 = Synchronized Principal with Witness
----2 = Synchronized Principal without Witness
----3 = Synchronized Mirror with Witness
----4 = Synchronized Mirror without Witness
----5 = Connection with Principal Lost
----6 = Connection with Mirror Lost
----7 = Manual Failover
----8 = Automatic Failover
----9 = Mirroring Suspended
----10 = No Quorum
----11 = Synchronizing Mirror
----12 = Principal Running Exposed
----*/
----EXEC msdb.dbo.sp_add_alert @name=N'DB_Mirroring_Check_WMI',
---- @enabled=1,
---- @delay_between_responses=0,
---- @include_event_description_in=1,
---- @notification_message=N'DB_Mirroring_Check_WMI alert !',
---- @wmi_namespace=N'\\.\root\Microsoft\SqlServer\ServerEvents\MSSQLSERVER',
---- @wmi_query=N'SELECT * FROM DATABASE_MIRRORING_STATE_CHANGE WHERE State = 7 OR State = 8 OR Stare = 10 '
------ @wmi_namespace=N'\\.\root\Microsoft\SqlServer\ServerEvents\instancename',
----GO
----EXEC msdb.dbo.sp_add_notification @alert_name=N'DB_Mirroring_Check_WMI', @operator_name=N'DBA', @notification_method = 1
----GO
----/* you may want to link this alert to a job to perform additional check / failover preparation / ... */
/*
If there are potential issues with network load or some other reason that may be causing
a delay in communicating with all three servers one solution is to change
the PARTNER TIMEOUT.
By default this value is set to 10 seconds, so if a "ping" is not received
in this 10 second period a failover may occur.
To make this change to a longer value, such as 20 seconds,
the following command would be issued on the Principal server for the database
that is mirrored.
ALTER DATABASE dbName SET PARTNER TIMEOUT 20
Be careful on the value that use for this option.
If this value is set to high and a failure really does occur, the automatic failover
will take longer based on the value you set.
In addition, the lowest this value can be set to is 5 seconds based
on information found in SQL Server Books Online.
*/
go
Select *
-- BOL nov 2008
, case New_State when 0 then 'Null Notification'
when 1 then 'Synchronized Principal with Witness'
when 2 then 'Synchronized Principal without Witness'
when 3 then 'Synchronized Mirror with Witness'
when 4 then 'Synchronized Mirror without Witness'
when 5 then 'Connection with Principal Lost'
when 6 then 'Connection with Mirror Lost'
when 7 then 'Manual Failover'
when 8 then 'Automatic Failover'
when 9 then 'Mirroring Suspended'
when 10 then 'No Quorum'
when 11 then 'Synchronizing Mirror'
when 12 then 'Principal Running Exposed'
when 13 then 'Synchronizing Principal'
else 'DBA - UNDOCUMENTED STATE'
end as New_State_Descryption
from DDBAServerPing.dbo.T_DBA_DBMirroring_Events with (nolock)
order by tsStartTime, [tsRegistration] desc
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply