To Show Database Mirror connections and status


To Show Database Mirror connections and status


The SQL script below will create a Stored Procedure, SP_ShowMirrors. This will show the mirroring status, partner information an automatic failover timeout for all mirrored databases, or a single database is the database name is entered as a parameted to the Stored Procedure.

E.G. copy and paste the code below and run to create the SP. Then to run it, from a new Query window type

sp_ShowMirrors
or
SP_Showmirrors myDatabase

-- sp_ShowMirrors Stored Procedure Creation
-- Mike Holden Cardium Solutions
-- October 2008
-- Procedure takes one optional parameter, a single database name
-- if no parameter is provided, all mirrored databases will be shown
USE master
GO
IF OBJECT_ID ('sp_ShowMirrors') IS NOT NULL
DROP PROCEDURE sp_ShowMirrors
GO
CREATE PROCEDURE sp_ShowMirrors
@dbName varchar(256) = '%'
AS
SELECT [name], mirroring_partner_name, mirroring_role_desc, mirroring_state_desc, mirroring_witness_name, mirroring_connection_timeout FROM sys.databases, sys.database_mirroring WHERE
sys.databases.database_id = sys.database_mirroring.database_id and
sys.databases.database_id IN
(SELECT database_id FROM sys.database_mirroring
WHERE mirroring_guid is not null)
AND [name] like @dbName
GO

 

 

Cardium offer services including Cloud Computing using Microsoft Remote Desktop Services paid for monthly and per user, installation, consultancy and virtualisation best practises.

Company Registration 5135448 VAT registration 834 5840 16

Your Office Anywhere, Cardium Outsourcing, Unit 4, Dominion Court, Billington Road, Burnley, Lancashire, BB11 5UB, UK.

 © Your Office Anywhere 2011



Add Feedback