July 21, 2016 at 11:33 am
So i have several queries which give me the list of job information for each database names.
I do not want to see db name column
I am looking for query which should not display more than two rows for each computer/instance
1) first row should have system backup job name and start/end time
2) second row should have user backup job name and start/end time
What i am looking for is Query which will display this result:
Computer/Instance Name Job Name Job Start time Job End time Total time it took to ran this job
this not necessary should be in same format.
My end result what i am looking for is:
computer/instance name, system job name and its start/end time and duration it ran and user job name and its start/end time and duration it ran
July 21, 2016 at 11:48 am
rk1980factor (7/21/2016)
So i have several queries which give me the list of job information for each database names.I do not want to see db name column
I am looking for query which should not display more than two rows for each computer/instance
1) first row should have system backup job name and start/end time
2) second row should have user backup job name and start/end time
What i am looking for is Query which will display this result:
Computer/Instance Name Job Name Job Start time Job End time Total time it took to ran this job
this not necessary should be in same format.
My end result what i am looking for is:
computer/instance name, system job name and its start/end time and duration it ran and user job name and its start/end time and duration it ran
Are you talking about workstation backups or database backups?
If database backups, can you tell us the difference between a system backup and a user backup?
If workstation backups, are they written to a table when they're performed?
July 21, 2016 at 11:54 am
so there are more than 100 sql servers - database servers
i have list of server name and instance name which i registered in ssms.
On each server there are many jobs created:
Out of those many jobs two of the jobs which do following:
1) One job runs and takes backup of all system databases (master,msdb,model)
2) second job runs and takes backup of all user databases (all user databases)
I want to get information for those two jobs from all the servers
what should be the query ?
July 21, 2016 at 12:05 pm
So this is basically a query of job history. Something along these lines should get you started.
SELECT h.server, j.name, h.run_date, h.run_time, h.run_duration
FROM msdb.dbo.sysjobs j
INNER JOIN msdb.dbo.sysjobhistory h ON h.job_id = j.job_id
WHERE j.name = 'your job name'
AND step_name = '(Job outcome)'
ORDER BY j.name;
It queries the job history table for the history of a particular job.
BTW, it isn't necessary to send me email with your post. I can see it just fine on the site.
July 21, 2016 at 12:08 pm
sorry about that but looks like that query didn't work, it gives me blank result
July 21, 2016 at 12:09 pm
so i know why it came as blank result becasue it ask me to enter job name etc.
July 21, 2016 at 12:13 pm
rk1980factor (7/21/2016)
so i know why it came as blank result becasue it ask me to enter job name etc.
Yes, you have to replace the job name "your job name" with the name of your job that does the backup.
Please, don't just take my code (or anyone's code, for that matter) and run it on your server without looking at it first. Make sure you understand it before you run it.
July 21, 2016 at 12:13 pm
i think below query might work but it shows the full history. I just want to see job name and last run date/time and duration
SELECT h.server, j.name, h.run_date, h.run_time, h.run_duration
FROM msdb.dbo.sysjobs j
INNER JOIN msdb.dbo.sysjobhistory h ON h.job_id = j.job_id
ORDER BY j.name;
July 21, 2016 at 12:17 pm
rk1980factor (7/21/2016)
i think below query might work but it shows the full history. I just want to see job name and last run date/time and durationSELECT h.server, j.name, h.run_date, h.run_time, h.run_duration
FROM msdb.dbo.sysjobs j
INNER JOIN msdb.dbo.sysjobhistory h ON h.job_id = j.job_id
ORDER BY j.name;
Add in the filter for step_name.
AND step_name = '(Job outcome)'
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply