log shipping and SSIS job monitoring

Dear all,

what kinds of tools you guys use to monitoring the status of log shipping and SSIS job. e.g. which has failed and how many times it failes last week, how stable it is and how lag behind on data transfer it is .
LVL 1
marrowyungSenior Technical architecture (Data)Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jim HornMicrosoft SQL Server Data DudeCommented:
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.
marrowyungSenior Technical architecture (Data)Author Commented:
"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 ?
Vitor MontalvãoMSSQL Senior EngineerCommented:
First of all, do you have any monitorig tool in your company? Tool like SCOM, Patrol or Nimsoft?

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
SolarWinds® IP Control Bundle (IPCB)

Combines SolarWinds IP Address Manager and User Device Tracker to help detect IP conflicts, quickly identify affected systems, and help your team take near instantaneous action. Help improve visibility and enhance reliability with SolarWinds IP Control Bundle.

marrowyungSenior Technical architecture (Data)Author Commented:
good question, we have idera sql diagnostic manager, but it seems it can't monitor log shipping ?
marrowyungSenior Technical architecture (Data)Author Commented:
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 ?
Vitor MontalvãoMSSQL Senior EngineerCommented:
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.
marrowyungSenior Technical architecture (Data)Author Commented:
"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.
Vitor MontalvãoMSSQL Senior EngineerCommented:
You can always query sysjobhistory table.
marrowyungSenior Technical architecture (Data)Author Commented:
so basically for your current company, how you guys deal with this ?
Vitor MontalvãoMSSQL Senior EngineerCommented:
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.
marrowyungSenior Technical architecture (Data)Author Commented:
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

Open 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

Open 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

Open in new window

marrowyungSenior Technical architecture (Data)Author Commented:
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
 ?
marrowyungSenior Technical architecture (Data)Author Commented:
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

Open 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

Open in new window

marrowyungSenior Technical architecture (Data)Author Commented:
for log shipping job, anyway to prolong to log result history so that the job history can show more, for a week e.g. ?
marrowyungSenior Technical architecture (Data)Author Commented:
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%'

Open in new window


but this one:

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

Open in new window


any good one single script can show the REAL information ?
Vitor MontalvãoMSSQL Senior EngineerCommented:
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"
Vitor MontalvãoMSSQL Senior EngineerCommented:
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. :)
marrowyungSenior Technical architecture (Data)Author Commented:
"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.
marrowyungSenior Technical architecture (Data)Author Commented:
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 ?

SQL job history
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 ?

job history
arnoldCommented:
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....
marrowyungSenior Technical architecture (Data)Author Commented:
"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 :

log shipping secondary properties.
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 ?
arnoldCommented:
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
marrowyungSenior Technical architecture (Data)Author Commented:
"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
marrowyungSenior Technical architecture (Data)Author Commented:
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 ?
arnoldCommented:
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.
marrowyungSenior Technical architecture (Data)Author Commented:
"
 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.
marrowyungSenior Technical architecture (Data)Author Commented:
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
arnoldCommented:
You keep referencing "monitoring log shipping status"  All you are actually monitoring is the status of the jobs that perform the task. 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.
Does your firm monitor network, systems, if the network goes down, the server goes down, there is nothing you can do as the DBA, someone has to address the failure first.
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. it can not restore file3 when file2 was not applied.  I think under these circumstances, log shipping will need to be reestablished from scratch full DB restore on the remote site, .......  

Everything you want to have reported is possible, you first have to identify the means available to you either via a centralized monitoring/notification system including the option of performing polling that will provide a graphical representation of the load/performance/memory usage of the systems on which SQL is running.

Or by adding notification jobs as referenced in the link.  Relying on a single method, i.e. email notification of success/failure. will not provide the historic reference without you having to go and search your emails. The history within the SQL is also limited to 10000 rows and the number of weeks if set for the job files.

A monitoring system as the commercial or the open source ones provide for a historic and are .......
arnoldCommented:
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.
.....
marrowyungSenior Technical architecture (Data)Author Commented:
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 ?
arnoldCommented:
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.....
marrowyungSenior Technical architecture (Data)Author Commented:
"Log shipping jobs are not part of maintenance jobs. "
no SQL job,
 need to monitor SQL job. so any suggestion ?
arnoldCommented:
I do not understand your last comment.
marrowyungSenior Technical architecture (Data)Author Commented:
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 ?
arnoldCommented:
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.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.