Finding current database mirroring state in SQL SERVER

In mirroring databases can goes into suspend or fail-over status due to any reason like Network, Space etc. It is necessary to find easy ways to look into current running status of databases and servers. SQL SERVER 2008 provides variety of features to look into this.

There are different ways to look into mirroring status. 

T-SQL

Using t-sql query on master you can get all running databases on Principal server and datail about witness and mirror. I have returned only columns that are in my use you can have much more options. using DMVs you can get result. I did not setup Witness so data is empty for Witness

------------------------- TSQL Code -------------------------------------------------

SELECT d.name,m.database_id,m.mirroring_state,mirroring_state_desc,mirroring_role_desc,mirroring_partner_instance,mirroring_witness_name,mirroring_witness_state,mirroring_witness_state _desc FROM 
sys.database_mirroring M inner join SYS.DATABASES d
on m.database_id = d.database_id
where mirroring_state_desc is not null

--------------------------------------------------------------------------------------

SSMS Mirroring Properties

You can find database current status for mirroring using SSMS. 

GOTO --> database Properties 

Open --> Mirroring Tab

Press on Refresh button and you will get successfully synchronized message. If there is any problem between servers or database you will get different message and can identify that there is some problem.

Database Mirroring Monitor

If you go to database properties you will find Database Mirroring Monitor option. It will show you a new Monitor window and show 2 rows for that database. One if Principal server status and second is Mirror server status with witness address at the bottom. It will show you refresh time at top left corner as well. Using this monitor you can see History for last 2 minutes and set thresh hold for your easiness.

Warning messages can be set manually. I will warn you on windows log and sql server log as well.