log shipping and SSIS job monitoring

I usually custom log SSIS packages by inserting rows into a specific table at certain points in the process, then to monitor just query that table.  That way I can get processing time, number of rows inserted, updated, deleted, pretty much anything I want to log.

This was created in 2008, before the 2012 capabilities, which I believe provide data in canned reports but not to the extent I can custom insert into my log table.

"I usually custom log SSIS packages by inserting rows into a specific table at certain points in the process, then to monitor just query that table."

good ! you program it yourselves ? any step by step example on how to implement it?

how about log shipping monitoring ?

good question, we have idera sql diagnostic manager, but it seems it can't monitor log shipping ?

what if we at this stage, would like to see how many failure of any job, including log shipping everyday and weeks, possible ?

getting daily summary on all jobs is good, for example, how often the job failes, so we can see daily job failure and then weekly history so that we can determine any jobs is not good as it keep failing.

any method to separate jobs and SSIS jobs only ?

Without a Monitoring Tool you can still monitor with SSMS but it will be a manual task.
You can set up the notification system for failed jobs.

"You can set up the notification system for failed jobs. "

I knew this, but how can we give summary for each day and each week ?

this is one of the main concern, specially the failure job of the daily and week.

You can always query sysjobhistory table.

so basically for your current company, how you guys deal with this ?

I think this tell you how to enlarge the monitor period:

http://blogs.msdn.com/b/poojakamath/archive/2014/02/18/the-job-history-of-all-sql-server-scheduled-jobs-are-getting-deleted-automatically-on-a-daily-basis.aspx

this to check status of the job?

http://www.sqlmatters.com/Articles/Checking%20the%20status%20of%20SQL%20Server%20Agent%20jobs.aspx

so basically for your current company, how you guys deal with this ?

We use a monitoring tool (Patrol) as we also used in my previous company (SCOM). When you have hundreds of SQL Server instances and thousands of databases you'll see that only a monitoring tool can help you saving time to find issues.

oh you use one tools to monitoring everything? this is good!

I am just thinking any existing build-in tools to help on this.!

yeah, email alert can do this and I am not sure why my new company don't use database email, probably they relies on Idera SQL Diagnostic Manager to send the result!

but do you believe that this tools,  it just can't monitor log shipping:

https://www.idera.com/productssolutions/sqlserver/sqldiagnosticmanager/best-sql-monitoring-tools ?

I heard that SSIS has it's own job, any script to separate SSIS job from the rest of SQL agent job  and list how failure it is ? I think this is good enough from script point of view and we can insert the output to a table ,right? any script example for that?

by the built-in log shipping report from SSMS, we can't find out which is primary and which is secondary, right?

by this link:

https://stanleyjohns.wordpress.com/2012/04/04/log-shipping-monitoring-and-status/

"Task: Are there any current issues with the log shipping?
use msdb
select * from log_shipping_monitor_error_detail
 This table will return a list of any errors associated with log shipping. You can filter by the database_name if you are concerned with any DB in particular. I usually order using log_time desc. Once again, the message column will give some helpful information."

when I run this:

use msdb
select * from log_shipping_monitor_error_detail order by log_time desc

 it should database_name is NULL, what is that mean ?

also when I do this:

use msdb
select * from log_shipping_monitor_primary

select * from log_shipping_primary_secondaries

Select allOpen in new window

it return empty result, so we have no primary and secondaries server?

this seems no use:

use msdb
select * from log_shipping_monitor_error_detail

Select allOpen in new window

as it just return all the log information, nothing actionable.

so it seems this is the only thing good to monitor about log shipping as we are checking how many error we have:

use msdb
select * from log_shipping_monitor_error_detail order by log_time desc

Select allOpen in new window

Jim,

how can we monitor the SSIS job?

by this: http://troywitthoeft.com/simple-ssis-package-monitoring-for-sql-server-2008/ ?

BI System Monitor and the SSIS Log Analyzer?

seems we need to enable SSIS log for this to run:

https://msdn.microsoft.com/en-us/library/ms141212(v=sql.100).aspx
 ?

any script to find out the log shipping primary, secondary and monitoring server (if log shipping use monitor server)

I use this :

use msdb
select * from log_shipping_monitor_primary

select * from log_shipping_primary_secondaries

Select allOpen in new window

but funny thing is it returns nothing for me even we are running log shipping. the server I am running this is the log shipping DR server, nothing happen but this:

use msdb

select * from log_shipping_monitor_history_detail

use msdb
select * from log_shipping_monitor_error_detail order by log_time desc

Select allOpen in new window

for log shipping job, anyway to prolong to log result history so that the job history can show more, for a week e.g. ?

by trying this:

https://www.mssqltips.com/sqlservertip/2553/different-ways-to-monitor-log-shipping-for-sql-server-databases/

both this don't give me an error:

--Query to check the job history error messages if any
SELECT * 
FROM [msdb].[dbo].[sysjobhistory]
WHERE [message] like '%Operating system error%'
--Query to check the Log Shipping errors
SELECT * 
FROM [msdb].[dbo].[log_shipping_monitor_error_detail]
WHERE [message] like '%Operating system error%'

Select allOpen in new window

but this one:

--Execute it on Primary/Secondary server
EXEC xp_readerrorlog 0,1,"Error",Null

Select allOpen in new window

any good one single script can show the REAL information ?

but do you believe that this tools,  it just can't monitor log shipping:

I don't know Idera but looks like they have a trial version so I recommend you to download and install it so you can perform the necessary tests.

I heard that SSIS has it's own job, any script to separate SSIS job from the rest of SQL agent job  and list how failure it is ?

I don't understand what you're trying to say here but from SSIS jobs you can use Jim's solution and create your own logs.

by the built-in log shipping report from SSMS, we can't find out which is primary and which is secondary, right?

I can't confirm this right now since I don't have any Log Shipping solution available.

it should database_name is NULL, what is that mean ?

Did you read MSDN article about log_shipping_monitor_error_detail? It exists one version of the table in each server (primary and secondary) and the errors are related to which server you are running the query. Also says for
database_name field: "The name of the database associated with this error record. Primary database for backup, secondary database for restore, or empty for copy"

but funny thing is it returns nothing for me even we are running log shipping. the server I am running this is the log shipping DR server, nothing happen but this:

both this don't give me an error:

You should be happy then. Means there were no errors in you Log Shipping. :)

"I don't know Idera but looks like they have a trial version so I recommend you to download and install it so you can perform the necessary tests."

sorry we are using it but I just not sure why company has this one but still want me to monitor the log shipping, replication and disk alert are here everyday.. but not log shipping ??? ,.... which means?

"I don't understand what you're trying to say here but from SSIS jobs you can use Jim's solution and create your own logs."

I need to monitor SQL jobs and SSIS jobs separately, so how can I know the job is related to SSIS?  and I want to monitor it too and in a separate script/method.

and how can I implement Jim's solution? I never monitor SSIS before and I don't know how ! any step by step guide?

"the errors are related to which server you are running the query. "

some MS links say we should run it on monitor server to get the full picture, right?

"database_name field: "The name of the database associated with this error record. Primary database for backup, secondary database for restore, or empty for copy" "

basically don't understand this.

"You should be happy then. Means there were no errors in you Log Shipping. :) "

no, what I said above is, diff script shows diff result, this is what I am worrying about.

hi, any update?

What I am going to do today change the SQL job history record on success/failure for 2 weeks, should I only change in the SQL Agent history properties ?


so by default, how many day SQL agent will keep the job history ?

and we see it has limit size of job history log setting, what if I change remove the agent history for older than 1 weeks and it exceed the job history log size? which setting override the other one ?

so then the log length/history can keep longer I expecting ?

If you as earlier commenters responded, if you want to maintain a historic easily accessible record, the use of a monitoring software is recommended.  The firm might have the ones mentioned.
Do not strictly concentrate of monitoring tools for your own duties only.  but to monitor the HW.


Because something is being logged on a monitoring server does not mean it is being resolved.  If you have a monitoring setup in the firm, make sure you have access to its reporting data in the least. As well as potentially have it configured to notify you of events.
Check with whoever administers the monitoring server to determine what options you have to add the sql events to the monitoring handler. i.e. you can configure your jobs to generate a failure notification on failure, while generating a success notification on success.  The monitoring side can be configured to expect a success notification within a specific window and if that email is not received treat the event as failed.

some monitors can poll the sql server and status of jobs, and generate notifications.
The monitoring server is built to retain the data beyond the 2/4/8 weeks you might set in the maintenance of file job. remove backup job logs, etc.

You only find out that the duration to keep events/history is too short is when something happens, and you need to go back further.


Quick look at Idera and the one you reference, not sure whether it will do what you want or are looking for.
They have other products that have to be purchased to address every other question you posted.

Sitescope HP, CA eHealth, IBM netcool, nagios, zabbix, etc.

SNMP with event to SNMPTRAP .......
Monitroring can be based on polling data i.e. a server checks on status, or using agents to querye status, or using proactive notifications. i.e. email or snmptrap....

"If you as earlier commenters responded, if you want to maintain a historic easily accessible record, the use of a monitoring software is recommended.  The firm might have the ones mentioned.
 Do not strictly concentrate of monitoring tools for your own duties only.  but to monitor the HW."

yeah, as I replied, we have idera sql diagnostic manager, but it seems this tools don’t monitor log shopping but mirror and replication only, right ?

https://www.idera.com/productssolutions/sqlserver/sqldiagnosticmanager/best-sql-monitoring-tools

Idera don't reply my email on this and I think they don't offer log shipping at all but mirror and replication.

that's why the replier said, then I have to monitor it manually.

then I am asking how can I monitor it using script to get most information out of it.  I read log shipping report but that one is not good as it just said NOW it is good or bad and some links here from MS say ,we should see the report from the log shipping monitor server to get the most information.

and I tried to find out script to find out the primary server and log shipping monitor server, when I open the log shipping secondary DB and see log shipping properties, I see this :


it seems that the log shipping monitor server does not exists but why no secondary server show up and the server I open the properties page open is the log shipping DR server.

I am not sure why.

ok, how can I keep more job history and monitor SQL server 2008 SSI jobs separately ?

i think in the various discussions that the "monitor" might be interpreted too literally.

Configuring the successful/failed job notifications is one way to get notified ...

Though for replication/log shipping, success might be too much as they will generate emails frequently often every 15 minutes..the other.

See whether the firm has system/network monitoring tools in use that can be extended to also monitor and notify .......... SQL

"Though for replication/log shipping, success might be too much as they will generate emails frequently often every 15 minutes..the other."

I want only failure if I can at this moment, so script return no error, then good!

"See whether the firm has system/network monitoring tools in use that can be extended to also monitor and notify .......... SQL "

yea.. SQL job, right? I am worrying about idera SQL manager, can't see why it don't monitor log shipping.

I test the script in that link too but none of them are working fine and I don't know what it suppose to return:

http://dba.stackexchange.com/questions/87228/how-can-i-automate-the-process-of-log-shipping-monitoring

if I adopt the script in this link:

http://www.sqlmatters.com/Articles/Checking%20the%20status%20of%20SQL%20Server%20Agent%20jobs.aspx

however, will the result from the script limited by the number of job history set in the SQL agent properties ?

and I have to change the SQL agent setting first before getting more result from the script ?

how many days by default the job history will keep instead of how many rows ?

Using a job to monitor sql jobs is fine with the examples from the script. but as at times happens, the agent terminates/or is terminated and is forgotten.

The scripts are designed to respond with the last run from each of the job history
I think it is 4 weeks but is not cleaned up by default. Properties of sql agent can be configured with defaults.
The removal of the records is part of a job that one has to setup to clear them out
if not enabled on the sql agent side.

"
 The scripts are designed to respond with the last run from each of the job history"

ok.tks.

"The removal of the records is part of a job that one has to setup to clear them out
 if not enabled on the sql agent side. "

what is that mean ? your statement this time is too short.

any monitoring tools which can monitor log shipping status and all history as well as all SQL job, including SSIS job.

this jobs should allow us to GROUP diff job and monitor by group so that I can separate jobs and monitor separately .

tks

IN SSMS, properties of SQL Agent, it has options that can be configured. One option deals with how many rows are dedicated to job history, I think it defaults to 10000 usually, there is a second section that deals with how long log files from jobs are kept, usually not set, defaults to 4 weeks. Check box needs to be activated for that to be handled by the SQL agent internal process.
If you want certain job files kept for different duration, i.e. you have 5,15 minute, hourly, daily jobs, weekly jobs, monthly jobs. using a 4 week interval will mean that your monthly jobs will only have one file, two at the most. and your log shipping jobs will have several dozen files for each job.
Using a separate job whose purpose it is to maintain (delete) outdated jobs run log files.
i.e.
shorter than every hour, jobs will be kept twice the log shipping, 6 days.
hourly, for a week,
daily keep for four weeks.
weekly, keep for 2 months
monthly , keep for 6 months.
.....

Arnold,

yeah, currently I found that I have only 2 choice, monitor the failure of the log shipping job (the primary and secondary one has 3 x job each, right?) and the application log of that box.

"You keep referencing "monitoring log shipping status"  All you are actually monitoring is the status of the jobs that perform the task"

at this moment yes. we just enlarge no. of week the history will kept in SQL agent properties to 8 weeks. this can do the job, right?


"On the primary server you are looking for errors that it could not or did not generate the transaction log. On the receiving side, you are looking for errors that it either could not copy the data or could not apply the change.

the server I can check one is the secondary one, the one receive log and restore, has 3 jobs for the db log shipping.

"You have to know how long a log shipping transaction log is kept in the share on the primary server.  Commonly, it is set to 72 hours, meaning you have three days once there is an issue on the receiving side to fix the issue before the logs will start being removed meaning log shipping breaks."

good point and how can I know this days? oh from the maintenance plan, we use 31 days.

" I think under these circumstances, log shipping will need to be reestablished from scratch full DB restore on the remote site, .......  "
yeah

"Or by adding notification jobs as referenced in the link. "

sorry, which link ?

so you are saying keep history record then buy tools as they show you the history ? any tools you see/use is good on this ?

"IN SSMS, properties of SQL Agent, it has options that can be configured. One option deals with how many rows are dedicated to job history, I think it defaults to 10000 usually, there is a second section that deals with how long log files from jobs are kept, usually not set, defaults to 4 weeks."

BTW, which setting take over which ? e.g. if 10000 can't store 4 weeks of data?

"Using a separate job whose purpose it is to maintain (delete) outdated jobs run log files.
 i.e.
 shorter than every hour, jobs will be kept twice the log shipping, 6 days.
 hourly, for a week,
 daily keep for four weeks.
 weekly, keep for 2 months
 monthly , keep for 6 months.
 ..... "

need programming I think ?

Log shipping jobs are not part of maintenance jobs.  
You need an encompassing monitoring/polling setup. Please check with IT/Network to see what tools the firm has that monitor the network and devices. And then see if they can also configure their tools to monitor the applications SQL, etc.

As well as notify.....

Jobs can delete by suffix, or by a pattern match based on starting name.....

"Log shipping jobs are not part of maintenance jobs. "
no SQL job,
 need to monitor SQL job. so any suggestion ?

I do not understand your last comment.

I am not only worry about SQL job,
  need to monitor all SQL jobs with diff catalog:

1) general job.
2) log shipping job.
3) SSIS job

so any suggestion ?

An sql agent job is a job no matter the underlying function/s.

I.e. Person a gas to report what they do.
Whether the person picks up a box, or take a box and move it to another location or accept a package bring delivered. Each thing is recorded/reported in the same way you configure it.