如何从针对此Server \Database运行的其他服务器\ AutoSys中查找其他作业 [英] How To Find Other Jobs from Other Servers\AutoSys that are Running Against this Server\Database
问题描述
我被要求执行以下操作...
从在这一台服务器上访问数据库的任何其他服务器列出所有预定作业及其所有者。
这可能吗？ 我怎么能抓住那个？从其他服务器或针对此server \database运行的AutoSys作业中查找其他作业。
lcerni
我不熟悉AutoSys。
但是应该可以捕获和分析SQL Server作业，即使它们是从其他服务器运行。
SQL Server计划作业始终使用以"SQLAgent - TSQL JobStep"开头的PROGRAM_NAME运行，后跟作业ID的十六进制表示。例如：
SQLAgent - TSQL JobStep（作业0xFB668E27919DA3489E3DD97061F25B31：第1步）
此外，此类会话的HOST_NAME显然等于执行它们的服务器名称。
假设您可以捕获在服务器上运行的命令（使用SQL事件探查器或扩展事件），并假设您可以访问这些命令通过链接服务器的远程服务器，您可以构建一个SQL查询，该查询使用位于这些远程服务器上的msdb.dbo.sysjobs系统
表，并使用它将捕获的二进制作业ID转换为实际的作业名称。 / p>
一般来说，这些是您需要采取的步骤：
1。使用SQL事件探查器或扩展事件捕获正在服务器上运行的命令，其APPLICATION NAME以"SQLAgent"开头。
2。使用字符串函数从捕获的会话的APPLICATION NAME中提取二进制作业ID。您需要使用 convert（binary（16），JobIdString，1）将这些ID转换为有效的二进制值（将"JobIdString"
替换为相关的字符串值，并且不要忘记使用"1"样式作为第3个参数，这很关键。）
3。使用上述会话中捕获的主机名称来标识这些作业所源自的服务器。确保为这些服务器设置了链接服务器。
4。从这些链接服务器查询msdb.dbo.sysjobs表以查找相应的作业名称。您需要使用 CONVERT（binary（16），job_id）才能匹配您在步骤2中找到的二进制ID。
其他资源：
就像我说的，我怕我我不熟悉AutoSys所以我不知道如何帮助你。希望您可以根据他们的PROGRAM NAME和HOST NAME会话字段进行类似的扣除，但这是假设AutoSys已经在那里放置了有用的信息
。
I have been requested to do the following...
List all scheduled jobs and their owners from any other servers that are accessing the database on this one server.
Is that possible? How could I capture that? To find other jobs from other servers or AutoSys jobs that are running against this server\database.
lcerni
I'm not familiar with AutoSys.
But it should be possible to capture and analyze SQL Server jobs even if they're being run from other servers.
SQL Server scheduled jobs always run with a PROGRAM_NAME that starts with "SQLAgent - TSQL JobStep", followed by a hexadecimal representation of the job's ID. For example:
SQLAgent – TSQL JobStep (Job 0xFB668E27919DA3489E3DD97061F25B31 : Step 1)
Also, the HOST_NAME of such sessions obviously equals to the server name that's executing them.
Assuming you can capture such commands being run on your server (using either SQL Profiler, or Extended Events), and assuming you have access to these remote servers via Linked Servers, you can construct a SQL query that uses the msdb.dbo.sysjobs system table located on these remote servers, and use it to translate the binary job id captured, to the actual job name.
In general, these are the steps you need to take:
1. Use SQL Profiler or Extended Events to capture commands being run on your server, whose APPLICATION NAME starts with "SQLAgent".
2. Use string functions to extract the binary Job ID from the APPLICATION NAME of the captured sessions. You'll need to use convert(binary(16), JobIdString, 1) to convert these IDs to a valid binary value (replace "JobIdString" with the relevant string value, and don't forget to use the "1" style as the 3rd parameter, it's critical).
3. Use the HOST NAME captured in the sessions above to identify the servers from which these jobs are originating. Make sure you have Linked Server(s) set up for these servers.
4. Query the msdb.dbo.sysjobs table from these Linked Servers to find the corresponding job names. You'll need to use CONVERT(binary(16), job_id) in order to match the binary IDs you've found in step 2.
Additional resources:
Using Extended Events to Capture Queries
Like I said, I'm afraid I'm not familiar with AutoSys so I don't know how to help you with those. Hopefully you can make similar deductions based on their PROGRAM NAME and HOST NAME session fields, but that's assuming AutoSys have put useful information there.
这篇关于如何从针对此Server \Database运行的其他服务器\ AutoSys中查找其他作业的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
