Avatar of skull52
skull52
Flag for United States of America asked on

Display what code is executing in a SQL JOB

Is there a query that I can run to see what Query or Stored Procedure is being executed within a SQL maintenance plan or Job 
SQLDatabasesMicrosoft SQL Server 2008Microsoft SQL ServerSSIS

Avatar of undefined
Last Comment
skull52

8/22/2022 - Mon
skull52

ASKER
anyone offer a suggestion 
Raja Jegan R

Yes, you can simply query it from the msdb.dbo.sysjobsteps as shown below after changing the job_name with your job name.
select j.name Job_name, js.step_name, js.subsystem, js.command
from msdb.dbo.sysjobs j
join msdb.dbo.sysjobsteps js on j.job_id = js.job_id
where j.name = 'job_name'

Open in new window

Pavel Celba

If you would like to see commands which are really executed during the Job run then you have to use SQL Server Profiler. Available in SSMS Tools menu.
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
skull52

ASKER
Raja- That code does not work, displays nothing.

Pcelba - Not what I am looking for; the profiler only shows running processes. I want to look into all SQL Jobs/Maintenance plans to see what they are executing.
Pavel Celba

OK, then you need Raja's code w/o the last line.
skull52

ASKER
Nope, it still does not show what code is running. It gives some info about the job but not what I am looking for.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Pavel Celba

What shows in the command column?

skull52

ASKER

Pavel Celba

OK it is the SSIS job internals of which I am not able to describe or decode.

I've added SSIS topic to your question and maybe somebody will answer.
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
Raja Jegan R

Seems your SQL Server Agent Job is executing as part of SQL Server Maintenance Plans.
FYI, whenever you create a Maintenance Plan in SQL Server, it will internally create a SQL Server Agent job named Maintenance_plan_Name.Subplan_1 like what exactly your results have shown above and to view the Maintenance plan, kindly try the below steps:
1. In SSMS, connect to your Server instance -> Expand Management -> Expand Maintenance Plans and Edit your Maintenance Plan named DecoratorEmail.
2. Edit the Maintenance Plan to see the logics involved in the Maintenance plan.

Since you haven't shared across the Maintenance Plan script or image of the steps involved in that Maintenance plan, we wouldn't be able to clarify what is the logic being used inside your Maintenance Plan.
Hence I would request you to either share the Maintenance plan scripts if it isn't sensitive otherwise you might need to decode the scripts used inside the Maintenance plan one by one (GUI based step by step actions) by yourself and understand the logic being performed inside.
Kindly let me know for more details.
skull52

ASKER
Raja, Yes, I understand that; let me explain further, I have 86  Maintenance Plans/SQL Jobs running, and I am needing to document what each one does hence the need to look inside the Job to determine what T-SQL script it is running or Stored Procedure it is executing. Opening each one individually would be tedious, so I am looking for some way to look into the Maintenance Plans/SQL Jobs to see what they are executing without opening each one.
Pavel Celba

SSIS package definitions are stored in msdb.dbo.sysssispackages table so the answer to your requirement could be here and here.
Additional useful query could be here and here.

Mr. Google also helped...
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Raja Jegan R

>> I have 86  Maintenance Plans/SQL Jobs running, and I am needing to document what each one does hence the need to look inside the Job to determine what T-SQL script it is running or Stored Procedure it is executing.

Okay, taking a step back and answering to your question again..
You have 86 jobs and x no. of jobs run SSIS packages or Maintenance plans. So, the remaining jobs assuming they run Queries or scripts or Procedures, we can identify the logic using the script I've provided in my first comment.
select j.name Job_name, js.step_name, js.subsystem, js.command
from msdb.dbo.sysjobs j
join msdb.dbo.sysjobsteps js on j.job_id = js.job_id

Open in new window

If the script is using SSIS Packages or Maintenance plans, then I would recommend opening up those SSIS Packages or Maintenance plans one by one to see the actual logics involved.
Scripts or approaches shared by pcelba can help out but there is a catch in that approach as listed below:
1. In your SSIS Package or Maintenace plans, let's say you have an Execute SQL Script component used out and you can find out the script used in the component using the query shared by the links above, However if those components are written to execute only achieving certain logics or conditions, then it's not an easier approach to get it via those querieis.
2. Few cases, those components will be commented or disabled out and it would be difficult to get it from the queries.

If the components aren't disabled out or not coded to execute only under certain logics, then the queries shared by pcelba will be a good start but to let you know, packages developed under each and every SQL Server version will have a slight difference in XML schema structure and hence you might need to spend some time debugging it effectively.
FYI, I've tried to read SSIS packages that way and this is from my personal experience since in my environment we have packages developed in SQL Server 2008 R2, 2012, 2014 and 2016 versions deployed on SQL Server 2016 Server instance. Even though Visual studio tries to ugprade SQL Server 2008 R2 packages to later version, the schema slightly differs compared to the packages developed directly in SQL Server 2016.
ASKER CERTIFIED SOLUTION
skull52

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question