sql mirror witness disconnected

The witness sqlserver sometime get disconnected, reboot it sometime fix the problem, if not, reset witness is required, here is how.

to find out how many databases are using this witness, run query on witness:

SELECT principal_server_name, mirror_server_name, database_name, safety_level_desc
FROM sys.database_mirroring_witnesses

to find out which server is disconnected, run this sql on the principle server:

select mirroring_partner_name,mirroring_partner_name,mirroring_witness_name, mirroring_state_desc,mirroring_witness_state_desc, * from sys.database_mirroring

if the mirroring_witness_state_desc = disconnected, then remove the witness by running the following script. let's refer this list as $DBLIST.

on witness:

select 'alter database '+database_name+' set witness off' FROM sys.database_mirroring_witnesses

pickup the output that matching $DBLIST and save the output as o1.sql.

select 'alter database '+database_name+' set witness ='+'''TCP://REPLACETHISSERVERNAME:REPLACETHISPORTNUMBER''' FROM sys.database_mirroring_witnesses

pickup the output that matching $DBLIST and save the output as o2.sql.

restart witness sqlserver service, and reset the witness by running o1.sql and o2.sql on principle. if the o2.sql fail, we need to re-run it or even restart the witness db service.

then check the status again by running the following sql on principle server, now the mirroring_witness_state_desc should show "connected".

select mirroring_partner_name,mirroring_partner_name,mirroring_witness_name, mirroring_state_desc,mirroring_witness_state_desc, * from sys.database_mirroring