1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
| DECLARE
@state VARCHAR(30)
DECLARE
@DbMirrored INT
DECLARE
@DbId INT
DECLARE
@String VARCHAR(100)
DECLARE
@databases TABLE (DBid INT, mirroring_state_desc VARCHAR(30))
-- get status for mirrored databases
INSERT
@databases
SELECT
database_id, mirroring_state_desc
FROM
sys.database_mirroring
WHERE
mirroring_role_desc IN ('PRINCIPAL','MIRROR')
AND
mirroring_state_desc NOT IN ('SYNCHRONIZED','SYNCHRONIZING')
-- 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 @string = 'Host: '+@@servername+'.'+CAST(DB_NAME(@DbId) AS VARCHAR)+ ' - DB Mirroring is '+@state +' - notify DBA'
EXEC msdb.dbo.sp_send_dbmail 'valid_mail_profile', 'DBA@xxx.com', @body = @string, @subject = @string
DELETE FROM @databases WHERE DBid = @DbId
END
--also alert if there is no mirroring just in case there should be mirroring :)
SELECT
@DbMirrored = COUNT(*)
FROM
sys.database_mirroring
WHERE
mirroring_state IS NOT NULL
IF
@DbMirrored = 0
BEGIN
SET @string = 'Host: '+@@servername+' - No databases are mirrored on this server - notify DBA'
EXEC msdb.dbo.sp_send_dbmail 'valid_mail_profile', 'DBA@xxx.com', @body = @string, @subject = @string
END
|