February 26, 2013 at 8:24 am
Hello,
We are consolidating servers and I was asked to write a query that returns a list of jobs on a server so that we can get an idea of what jobs will need to be copied over to the new server. I have gotten pretty close with this query below.
select j.name, step_id, step_name,subsystem,database_name, category_id
from msdb..sysjobs j left join msdb..sysjobsteps t
on (j.job_id = t.job_id)
where enabled = 1
order by category_id
The problem is it's returning jobs that where created automatically for the replication agents and other system jobs. We are looking for only the user defined jobs. I feel like category_id may help but I have not been able to find the meaning behind the integer in category_id. I would appreciate any help, thanks!
February 26, 2013 at 10:22 am
The owner_sid column will give you information regarding who owns the jobs. Just exclude anything owned by the system. That will help for any jobs that people haven't reassigned to a system job.
Google-fu SUSER_SID()
February 26, 2013 at 11:32 am
Good idea,
My only concern with this is we change the ownership after we create a job to sa. It looks like the default syspolicy_purge_history job which was created by the system also runs under sa so I'm not sure if I'll see a distinction.
February 26, 2013 at 12:28 pm
Possibly if you compare the created date to the last modified date you can use that as a guideline. Any job that has a different last modified date to its created date could be one that you've changed ownership on.
February 27, 2013 at 3:43 am
Check msdb.dbo.syscategories
https://sqlroadie.com/
February 27, 2013 at 12:12 pm
Thanks all.
I ended up using category_id and saying if it was 0 or >= 98 and also making sure the name was not equaly to 'sys_policy....'. It gave me what I wanted. Now how do I close this thread or mark it as no more help needed?
February 27, 2013 at 12:41 pm
You don't as far as I know. You just let it go dead.
Glad to hear you found a solution.
February 27, 2013 at 9:06 pm
Cool, threads will remain open. May be, someone will have a question related to the same topic or someone will find a better solution. Threads don't get locked in SSC. It helps.
https://sqlroadie.com/
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply