September 22, 2017 at 2:32 am
Hi There,
Can someone guide me on creating a consolidated Alert job or alert messages in which the process of Log shipment like (Copy and Restore to the Secondary Server from Primary Server) to the concern stakeholders.?
Regards
Adil
September 22, 2017 at 9:33 pm
Could any one help me with a script or automated process in which the status of Log Shipping of databases from both primary
and secondary servers could be sent via alert mail to the stakeholders and concern individuals?
September 25, 2017 at 5:02 am
adilahmed1989 - Friday, September 22, 2017 9:33 PMCould any one help me with a script or automated process in which the status of Log Shipping of databases from both primaryand secondary servers could be sent via alert mail to the stakeholders and concern individuals?
All the information in relation to LogShipping is stored in the LogShipping tables in MSDB.
You can create your own alerts to meet your stakeholders needs as they will be different to what other companies stakeholders required.
Look through this link as a starting point on the objects in msdb.
September 25, 2017 at 7:19 am
adilahmed1989 - Friday, September 22, 2017 9:33 PMCould any one help me with a script or automated process in which the status of Log Shipping of databases from both primaryand secondary servers could be sent via alert mail to the stakeholders and concern individuals?
There are a few different scripts and jobs posted for monitoring log shipping - you may want to see if any of those work for you:
SQL Server Log Shipping Monitoring and Email Notification
Automating SQL Server Transactional Log Shipping Alerts
Sue
September 25, 2017 at 11:32 pm
anthony.green - Monday, September 25, 2017 5:02 AMadilahmed1989 - Friday, September 22, 2017 9:33 PMCould any one help me with a script or automated process in which the status of Log Shipping of databases from both primaryand secondary servers could be sent via alert mail to the stakeholders and concern individuals?All the information in relation to LogShipping is stored in the LogShipping tables in MSDB.
You can create your own alerts to meet your stakeholders needs as they will be different to what other companies stakeholders required.
Look through this link as a starting point on the objects in msdb.
Thanks for the information; would go through it and let know if any issues on it.
September 25, 2017 at 11:33 pm
Sue_H - Monday, September 25, 2017 7:19 AMadilahmed1989 - Friday, September 22, 2017 9:33 PMCould any one help me with a script or automated process in which the status of Log Shipping of databases from both primaryand secondary servers could be sent via alert mail to the stakeholders and concern individuals?There are a few different scripts and jobs posted for monitoring log shipping - you may want to see if any of those work for you:
SQL Server Log Shipping Monitoring and Email Notification
Automating SQL Server Transactional Log Shipping AlertsSue
Thanks for the information madam, will go through it and let know if any issues on it.
September 28, 2017 at 5:51 am
adilahmed1989 - Monday, September 25, 2017 11:33 PMSue_H - Monday, September 25, 2017 7:19 AMadilahmed1989 - Friday, September 22, 2017 9:33 PMCould any one help me with a script or automated process in which the status of Log Shipping of databases from both primaryand secondary servers could be sent via alert mail to the stakeholders and concern individuals?There are a few different scripts and jobs posted for monitoring log shipping - you may want to see if any of those work for you:
SQL Server Log Shipping Monitoring and Email Notification
Automating SQL Server Transactional Log Shipping AlertsSue
Thanks for the information madam, will go through it and let know if any issues on it.
Dear All,
I am trying to execute a stored procedure in which Log shipment status could be sent to a specific email address but i am getting the following error on executing the SP.
Below is the script :
USE [APTEST]
GO
CREATE PROCEDURE [dbo].[usp_GetLogShippingStatus]
@mode BIT = 0
AS
BEGIN
SET NOCOUNT ON
SET XACT_ABORT ON
DECLARE @Recipients VARCHAR(275)
DECLARE @MailSubject VARCHAR(275)
DECLARE @Xml VARCHAR(MAX)
DECLARE @Mailtext VARCHAR(MAX)
DECLARE @Server VARCHAR(25)
DECLARE @Curdate DATETIME
DECLARE @MailString VARCHAR(MAX)
DECLARE @Note NVARCHAR(1000)
DECLARE @Filedate DATETIME
DECLARE @Dbname SYSNAME
DECLARE @Latency SYSNAME
DECLARE @Filename NVARCHAR (500)
DECLARE @Tempname NVARCHAR (500)
DECLARE @Primary SYSNAME
DECLARE @Secondary SYSNAME
DECLARE @SQLVersion VARCHAR(20)
DECLARE @Sql VARCHAR(500)
SET @Curdate = GETDATE()
SET @Server = @@SERVERNAME
SET @Recipients = 'XYZ@gmail.com'
IF OBJECT_ID('tempdb..#TABLE_LS_MONITOR') IS NOT NULL
DROP TABLE #TABLE_LS_MONITOR
CREATE TABLE #TABLE_LS_MONITOR
(
SN INT IDENTITY(1,1)
,PRIMARY_SERVER SYSNAME NULL
,PRIMARY_DATABASE SYSNAME NULL
,SECONDARY_SERVER SYSNAME NULL
,SECONDARY_DATABASE SYSNAME NULL
,LSSTATUS VARCHAR(10)
,LAST_BACKUP_FILE NVARCHAR(500) NULL
,BACKUP_THRESHOLD INT NULL
,LAST_BACKUP_TIME DATETIME
,TIME_SINCE_LAST_BACKUP INT NULL
,LAST_RESTORED_FILE NVARCHAR(500) NULL
,RESTORE_THRESHOLD INT NULL
,LAST_RESTORE_TIME DATETIME
,TIME_SINCE_LAST_RESTORE INT NULL
,LAST_RESTORED_LATENCY INT NULL
)
DECLARE LSServers CURSOR FOR
SELECT PRIMARY_SERVER, SECONDARY_SERVER, SQLVersion FROM LSServers;
OPEN LSServers
FETCH NEXT FROM LSServers
INTO @Primary, @Secondary, @SQLVersion
WHILE @@FETCH_STATUS = 0
BEGIN
IF @SQLVersion in ('SQL2016','SQL2014','SQL2012','SQL2008','SQL2008R2','SQL2005')
BEGIN
SET @Sql = 'SELECT p.primary_server,p.primary_database, s.secondary_server,
s.secondary_database, p.last_backup_file, p.backup_threshold,
p.last_backup_date, s.last_restored_file, s.restore_threshold, s.last_restored_date,
s.last_restored_latency
FROM ' + @Primary + '.msdb.dbo.log_shipping_monitor_primary p INNER JOIN '
+ @Secondary + '.msdb.dbo.log_shipping_monitor_secondary s
ON p.primary_server = s.primary_server and p.primary_database = s.primary_database'
INSERT INTO #TABLE_LS_MONITOR (PRIMARY_SERVER, PRIMARY_DATABASE, SECONDARY_SERVER,
SECONDARY_DATABASE, LAST_BACKUP_FILE, BACKUP_THRESHOLD, LAST_BACKUP_TIME,
LAST_RESTORED_FILE, RESTORE_THRESHOLD, LAST_RESTORE_TIME, LAST_RESTORED_LATENCY)
EXEC(@Sql)
END
IF @SQLVersion = 'SQL2000'
BEGIN
SET @Sql = 'SELECT p.primary_server_name,p.primary_database_name,
s.secondary_server_name, s.secondary_database_name, p.last_backup_filename,
p.backup_threshold, p.last_updated, s.last_loaded_filename, s.out_of_sync_threshold,
s.last_loaded_last_updated
FROM ' + @Primary + '.msdb.dbo.log_shipping_primaries p INNER JOIN '
+ @Primary + '.msdb.dbo.log_shipping_secondaries s
ON p.primary_id = s.primary_id'
INSERT INTO #TABLE_LS_MONITOR (PRIMARY_SERVER, PRIMARY_DATABASE, SECONDARY_SERVER,
SECONDARY_DATABASE, LAST_BACKUP_FILE, BACKUP_THRESHOLD,
LAST_BACKUP_TIME, LAST_RESTORED_FILE, RESTORE_THRESHOLD, LAST_RESTORE_TIME)
EXEC(@Sql)
END
FETCH NEXT FROM LSServers
INTO @Primary, @Secondary, @SQLVersion
END
CLOSE LSServers;
DEALLOCATE LSServers;
UPDATE #TABLE_LS_MONITOR SET TIME_SINCE_LAST_BACKUP = DATEDIFF(mi, LAST_BACKUP_TIME,
@Curdate), TIME_SINCE_LAST_RESTORE = DATEDIFF(mi, LAST_RESTORE_TIME, @Curdate)
UPDATE #TABLE_LS_MONITOR SET LAST_BACKUP_FILE = SUBSTRING(LAST_BACKUP_FILE,
LEN(LAST_BACKUP_FILE) - CHARINDEX('\', REVERSE(LAST_BACKUP_FILE))+2, LEN(LAST_BACKUP_FILE)),
LAST_RESTORED_FILE = SUBSTRING(LAST_RESTORED_FILE, LEN(LAST_RESTORED_FILE) - CHARINDEX('\',
REVERSE(LAST_RESTORED_FILE))+2, LEN(LAST_RESTORED_FILE))
WHERE CHARINDEX(N'\',LAST_BACKUP_FILE)!=0 OR CHARINDEX(N'\',LAST_RESTORED_FILE)!=0
DECLARE LSCURSOR CURSOR FOR
SELECT PRIMARY_DATABASE, LAST_RESTORED_FILE
FROM #TABLE_LS_MONITOR WHERE LAST_RESTORED_LATENCY IS NULL;
OPEN LSCURSOR
FETCH NEXT FROM LSCURSOR
INTO @Dbname, @Filename
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @Tempname = RIGHT (@Filename, LEN (@Filename) - (LEN(@Dbname) + LEN ('_tlog_')))
IF (CHARINDEX ('.',@Tempname,0) > 0)
SELECT @Tempname = LEFT (@Tempname, CHARINDEX ('.',@Tempname,0) - 1)
SELECT @Filedate = CONVERT (DATETIME,SUBSTRING (@Tempname, 1,8),112)
IF (LEN (@Tempname) = 12)
BEGIN
SELECT @Filedate = DATEADD (hh, CONVERT (INT, SUBSTRING (@Tempname,9,2)),@Filedate)
SELECT @Filedate = DATEADD (mi, CONVERT (INT, SUBSTRING (@Tempname,11,2)),@Filedate)
END
UPDATE #TABLE_LS_MONITOR SET LAST_RESTORED_LATENCY = datediff(mi, @Filedate,
LAST_RESTORE_TIME) WHERE LAST_RESTORED_LATENCY IS NULL
FETCH NEXT FROM LSCURSOR
INTO @Dbname, @Filename
END
CLOSE LSCURSOR;
DEALLOCATE LSCURSOR;
UPDATE #TABLE_LS_MONITOR SET LSSTATUS = CASE
WHEN TIME_SINCE_LAST_BACKUP > BACKUP_THRESHOLD THEN 'BAD'
WHEN TIME_SINCE_LAST_RESTORE > RESTORE_THRESHOLD THEN 'BAD'
WHEN LAST_RESTORED_LATENCY > RESTORE_THRESHOLD THEN 'BAD'
ELSE 'GOOD' END
SET @Mailtext ='<html>
<style type="text/css">
table.gridtable {
font-family: verdana,arial,sans-serif;
font-size:12px;
color:#000000;
border-width: 1px;
border-color: #666666;
border-collapse: collapse;
}
table.gridtable th {
border-width: 1px;
font-size:11px;
border-style: solid;
border-color: #666666;
}
table.gridtable td {
border-width: 1px;
font-size:11px;
border-style: solid;
border-color: #666666;
}
</style>
<body>
<table class="gridtable">
<tr bgcolor = ''''#808080''''>
<th> Primary_Server </th> <th> Primary_DB </th> <th> Secondary_Server </th>
<th> Secondary_DB </th> <th> Status </th> <th> Last Backup File</th>
<th> Backup Threshold </th> <th> TimeSince LastBackup </th>
<th> Last Restored File </th> <th> Restore Threshold </th>
<th> TimeSince LastRestore </th> <th> LastRestored Latency</th>
</tr>'
SET @Note = '<b>Note:</b><br/>
Time unit is minute for all of the time measures used here.<br/>
</body></html>'
IF @mode = 0
BEGIN
SET @Xml = CAST(( SELECT 'tr/@bgcolor'= CASE LSSTATUS WHEN 'GOOD'
THEN '#90EE90'
ELSE '#FF4500' END,
td = PRIMARY_SERVER, '', td = PRIMARY_DATABASE , '',
td = SECONDARY_SERVER, '',
td = SECONDARY_DATABASE, '',
td = LSSTATUS, '', td = LAST_BACKUP_FILE, '', td = BACKUP_THRESHOLD, '',
td = TIME_SINCE_LAST_BACKUP, '',
td = LAST_RESTORED_FILE, '', td = RESTORE_THRESHOLD, '',
td = TIME_SINCE_LAST_RESTORE, '',
td = LAST_RESTORED_LATENCY, ''
FROM #TABLE_LS_MONITOR
FOR XML PATH('tr'), ELEMENTS ) AS NVARCHAR(MAX))
SET @Mailtext = @Mailtext + @Xml +'</table><br/>' + @Note
SET @MailSubject = 'Transaction Log Shipping Status'
SET @MailString = 'SQLDBAExperts.msdb.dbo.sp_send_dbmail @profile_name = ''sqldbmail'',
@recipients = ''' + @Recipients + ''',@subject = ''' + @MailSubject + ''',
@body = ''' + @Mailtext + ''', @body_format = ''HTML'' '
EXEC (@MailString)
END
ELSE
BEGIN
IF EXISTS(SELECT 1 FROM #TABLE_LS_MONITOR WHERE LSSTATUS = 'BAD')
BEGIN
SET @Xml = CAST(( SELECT 'tr/@bgcolor'= CASE LSSTATUS WHEN 'GOOD'
THEN '#90EE90' ELSE '#FF4500' END, td = PRIMARY_SERVER, '', td = PRIMARY_DATABASE , '',
td = SECONDARY_SERVER, '', td = SECONDARY_DATABASE, '',
td = LSSTATUS, '', td = LAST_BACKUP_FILE, '', td = BACKUP_THRESHOLD, '',
td = TIME_SINCE_LAST_BACKUP, '', td = LAST_RESTORED_FILE, '', td = RESTORE_THRESHOLD, '',
td = TIME_SINCE_LAST_RESTORE, '', td = LAST_RESTORED_LATENCY, ''
FROM #TABLE_LS_MONITOR WHERE LSSTATUS = 'BAD'
FOR XML PATH('tr'), ELEMENTS ) AS NVARCHAR(MAX))
SET @Mailtext = @Mailtext + @Xml +'</table><br/>' + @Note
SET @MailSubject = 'Transaction Log Shipping is out of sync'
SET @MailString = 'SQLDBAExperts.msdb.dbo.sp_send_dbmail @profile_name = ''sqldbmail'',
@recipients = ''' + @Recipients + ''', @subject = ''' + @MailSubject + ''', @body = '''
+ @Mailtext + ''', @body_format = ''HTML'' '
EXEC (@MailString)
END
END
DROP TABLE #TABLE_LS_MONITOR
END
GO
exec usp_GetLogShippingStatus 0
and below is the error :
Msg 102, Level 15, State 1, Line 5
Incorrect syntax near '-'.
Kindly help me out in this 🙂
September 28, 2017 at 8:20 am
Could you post the rest? Or you can try to run whatever you are using to execute usp_GetLogShippingStatus and then double click on the error in the messages tab when executing your statements in SSMS. It will show you where the error is in whatever you are running.
Sue
September 29, 2017 at 12:04 am
Sue_H - Thursday, September 28, 2017 8:20 AMIt looks like there is more than just this statement you are executing:
exec usp_GetLogShippingStatus 0Could you post the rest? Or you can try to run whatever you are using to execute usp_GetLogShippingStatus and then double click on the error in the messages tab when executing your statements in SSMS. It will show you where the error is in whatever you are running.
Sue
Thanks Madam for helping out; I found that the Primary and Secondary server name which i was referring to had "-" which was prompting an error.; however now i am facing another error like :
Msg 468, Level 16, State 9, Line 6
Cannot resolve the collation conflict between "Latin1_General_CI_AI" and "SQL_Latin1_General_CP1_CI_AS" in the equal to operation.On Further Investigating, i found that collation of MSDB database on Secondary server is different from that of Primary server. The collation of MSDB database on Secondary Server is "Latin1_General_CI_AI"
Any help on resolving this issue would be appreciated.
Regards,
Adil
September 29, 2017 at 10:27 am
Now i am receiving following error on running the monitoring job.
Executed as user: dbo. Access to the remote server is denied because the current security context is not trusted. [SQLSTATE 42000] (Error 15274). The step failed.
Any Help on it would be appreciated.
Regards,
Adil
September 29, 2017 at 11:17 am
adilahmed1989 - Friday, September 29, 2017 10:27 AMNow i am receiving following error on running the monitoring job.Executed as user: dbo. Access to the remote server is denied because the current security context is not trusted. [SQLSTATE 42000] (Error 15274). The step failed.
Any Help on it would be appreciated.
Regards,
Adil
The account for the job and the Linked server needs access to the second server.
In terms of the other error, you have mismatched collations. Even with workarounds, I would guess you will have other future problems. msdb gets its collation from the server collation and you can't change collations for msdb. You can find how to work around the issue and set certain columns to other collations in the following article:
SQL SERVER – Cannot resolve collation conflict for equal to operation
Sue
September 29, 2017 at 11:25 am
Sue_H - Friday, September 29, 2017 11:17 AMadilahmed1989 - Friday, September 29, 2017 10:27 AMNow i am receiving following error on running the monitoring job.Executed as user: dbo. Access to the remote server is denied because the current security context is not trusted. [SQLSTATE 42000] (Error 15274). The step failed.
Any Help on it would be appreciated.
Regards,
AdilThe account for the job and the Linked server needs access to the second server.
In terms of the other error, you have mismatched collations. Even with workarounds, I would guess you will have other future problems. msdb gets its collation from the server collation and you can't change collations for msdb. You can find how to work around the issue and set certain columns to other collations in the following article:
SQL SERVER – Cannot resolve collation conflict for equal to operationSue
Dear madam,
Thanks for your input i was able to solve the collation issue by apphending Collate database default to the join condition.
However i would need help on the account access issue, any further detailed help would be appreciated.
Regards,
Adil
July 11, 2023 at 5:06 am
Hi,
While exec usp_GetLogShippingStatus
Msg 102, Level 15, State 1, Line 2
Incorrect syntax near '+'.
Pls help
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply