DECLARE @state VARCHAR(30)
DECLARE @DbMirrored INT
DECLARE @DbId INT
DECLARE @String NVARCHAR(200)
DECLARE @String1 NVARCHAR(200)
DECLARE @databases TABLE (DBid INT, mirroring_state_desc VARCHAR(30))

-- get status for mirrored databases
INSERT @databasesSELECT database_id, mirroring_state_descFROM sys.database_mirroringWHERE mirroring_role_desc IN ('PRINCIPAL','MIRROR')

-- iterate through mirrored databases and send email alert

WHILE EXISTS (SELECT TOP 1 DBid FROM @databases WHERE mirroring_state_desc IS NOT NULL)
BEGIN
SELECT TOP 1 @DbId = DBid, @State = mirroring_state_desc FROM @databases
SET @String1 = 'Alter Database ['+CAST(DB_NAME(@DbId) AS VARCHAR)+ '] Set Partner timeout 30'
print @String1
EXECUTE sp_executesql @String1
DELETE FROM @databases WHERE DBid = @DbId
print @string
END






SELECT database_id, mirroring_state_desc,mirroring_failover_lsnFROM sys.database_mirroringWHERE mirroring_role_desc IN ('PRINCIPAL','MIRROR')