How to monitor Log Backups for MS SQL Server

[Former Member]

Hello all.

We are running Solution Manager 7.1 on SP Stack 10 and have downloaded the latest template content.

When configuring the templates for DB monitoring, we noticed that although there is a metric that monitors the age of the last DB backup, there is no such metric to monitor the age of the last successful Log backup, or even a metric to alert us on a failed backup / log backup.

Has any of you ever had to create a custom metric to monitor either the age of the latest successful log backup for MS SQL Server, or a custom metric to alert on failed backups / log backups?

If yes, could you provide some instructions on how to create such a metric?

Thank you in advance for your answers.

Phevos

[Former Member · Accepted solution]

Hi Phevous,

see this links are usefully

SysMon_Create custom metrics and alerts - Technical Operations - SCN Wiki

http://wiki.scn.sap.com/wiki/download/attachments/269157890/How+to+create+custom+CCMS+metrics+in+MAI...

http://wiki.scn.sap.com/wiki/download/attachments/269157890/Monitored+Processes+Metrics.pdf?original...

Regards,

Chinna

[Former Member · Accepted solution]

Hello Chinna.

Thank you for those links. I have gone through them in the past few days and that is the reason I ended up posting here

My main problem is I can't find a corresponding MTE in RZ20 in order to create the custom metric. Sorry for not mentioning that in my original post.

Once again thank you for your reply.

Phevos

[divyanshu_srivastava3]

Hi Phevos,

You can use custom monitoring using log files to read data.

Refer my thread:

Use the setup metioned above technical monitoring custom template.

Divyanshu

[Former Member]

Divyanshu, thank you for your answer.

I had a similar idea to check the DB log file for failed log backups. The problem is that when I opened the log file from the dbacockpit, I found no errors/warnings for the days that our log backups did not run or failed. Here is a small part of that log to show you what I mean. In this example, the log backup was not running from 25th of July until the 28th of July when we fixed the log backups again, after receiving a disk space warning (log disk drive was almost full):

The last successful log backup is mentioned in the log: "20140724091524    Backup    Log was backed up"

Then as the days go by, there is no backup failure entries whatsoever to look for them in the log:

20140725000023    spid18s    This instance of SQL Server has been using a process ID of 1304 since 5/27/2014 11:50:00 PM (local) 5/28/2014 6:50:00

20140725000023    spid18s    AM (UTC). This is an informational message only; no user action is required.

20140726000020    spid21s    This instance of SQL Server has been using a process ID of 1304 since 5/27/2014 11:50:00 PM (local) 5/28/2014 6:50:00

20140726000020    spid21s    AM (UTC). This is an informational message only; no user action is required.

20140727000023    spid22s    This instance of SQL Server has been using a process ID of 1304 since 5/27/2014 11:50:00 PM (local) 5/28/2014 6:50:00

20140727000023    spid22s    AM (UTC). This is an informational message only; no user action is required.

20140728000022    spid17s    This instance of SQL Server has been using a process ID of 1304 since 5/27/2014 11:50:00 PM (local) 5/28/2014 6:50:00

20140728000022    spid17s    AM (UTC). This is an informational message only; no user action is required.

Until finally on the 28th we restored the log backup functionality:

20140728042814    Backup    Log was backed up. Database: XXX, creation date(time): 2010/12/22(16:45:29)

So, I find it difficult to deduce an appropriate string to look for in the log, in order to generate an alert when my log backups are not working.

Any suggestions? Am I looking at the wrong place perhaps?

[divyanshu_srivastava3]

Hi Phevos,

A quick question for you.

On which OS have you installed your Solution Manager ?

Divyanshu

[Former Member]

Hello Divyanshu.

Solman 7.1 SP Stack 10, on Windows Server 2008 R2 and MSSQL Server 2008.

If you need any extra info please let me know.

Also please note our scenario is central monitoring for a series of landscapes including almost every possible SAP installation (ERP, BI, PI, CRM etc) and various OS / DB combos (Windows 2003, 2008, 2012), Red hat Linux, Linux Suse etc, MS SQL Server, Oracle, DB2 and HANA.

We have configured and monitor over 200 systems. So far we have been concentrated in plugging in all the monitored systems and now is the time of fine tuning and evaluation of our existing templates/monitors.