This wall of text was absolutely useless. The reason? The table While it is the quick and easy way in a lot of cases, we need to stop relying only on Thankfully, we also log step output to a file, especially when there is any kind of loop (like backing up all databases). When the output might exceed the capacity of agent history, you can send the output to files by going to the Advanced tab of any job step’s Properties dialog and populating an Output File:<\/p>\n Reviewing the output file is much easier and does not have any size limits; it is also easier to script and automate and build parsing tools around. And you can standardize this for all jobs, perhaps to feed the files into an org-wide system like Datadog.<\/p>\n Some people shy away from this option because you have to hard code the path and file name, and they have very different configurations from server to server, making it harder to automate. But this entry supports tokens, so you can tokenize everything from the local SQL Server log directory and the current date and time to the job and step names and IDs. For example, this path:<\/p>\n Might resolve to:<\/p>\n Tokens are a pretty big topic on their own, but you can get a start in the official docs here:<\/p>\n One thing you’ll want to make sure is that the SQL Server Agent account has sufficient permissions, including the important Without these permissions, the job will still work, but the file will not appear. If you have set an output file but the file never shows up, you can troubleshoot this by reviewing SQL Server Agent’s own error log.<\/p>\n One other note: SQL Server Agent won’t clean up these files for you, so they will pile up. This may become important as systems age, particularly if your jobs have a lot<\/em> of output.<\/p>\n Even when you log output to a file, it can still be tedious scrolling through a lot of noise in the log file. You can reduce the noise there with a few techniques:<\/p>\n One of the items you might think is missing from the previous list is You can completely suppress all of this output with the following:<\/p>\n But you need to be careful with this. Some code may suddenly break, particularly if you use linked servers. I added this line to the stored procedure I was troubleshooting, and the job then failed with:<\/p>\n I was reminded of this one the hard way.<\/p>\n Sometimes the problem isn’t that there is job step history that is incomplete; it’s that there isn’t enough job history available at all. While logging can give you greater control over both what is in job history and how long it’s kept in those files, you may also want to optimize how much history is kept in If you have a complex system with some jobs that run every minute and others that run weekly or monthly, this is a very generic brush that will mean some jobs keep way too much history, and others roll away almost immediately. You can be more granular by writing your own process to manage history per job<\/em> or even to only keep history for job executions that had steps that failed; I wrote about this technique in great detail in “Manage SQL Server Agent History More Effectively<\/a>.”<\/p>\n There are a lot of little ways you can make reviewing and troubleshooting job history easier and more sensible. Logging job output to a file is an approach that has a huge payoff and is very easy to automate. What techniques have you used to wrangle SQL Server Agent?<\/p>\n","protected":false},"excerpt":{"rendered":" If you haven’t migrated your workloads to a managed database platform yet, you’re probably still relying on SQL Server Agent for various maintenance and other scheduled tasks. Most of the time, these processes just work. But when it’s time to troubleshoot, it can be cumbersome to get to the root of some problems. In this……<\/p>\n","protected":false},"author":341115,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[143527,53],"tags":[4151],"coauthors":[158980],"class_list":["post-101067","post","type-post","status-publish","format-standard","hentry","category-database-administration-sql-server","category-featured","tag-sql-server"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/101067","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/users\/341115"}],"replies":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/comments?post=101067"}],"version-history":[{"count":34,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/101067\/revisions"}],"predecessor-version":[{"id":104826,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/101067\/revisions\/104826"}],"wp:attachment":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media?parent=101067"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/categories?post=101067"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/tags?post=101067"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/coauthors?post=101067"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}dbo.sysjobhistory<\/code> stores the output in the message<\/code> column, and this column is nvarchar(4000)<\/code>. If your output is longer than 4,000 characters, then all you’ll see here is a subset. In no case so far has that subset contained the important information I was looking for.<\/p>\nmsdb<\/code> and SSMS to troubleshoot jobs because, sometimes, it isn’t enough.<\/p>\nLog output to a file<\/h4>\n
![\"Job](\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2024\/01\/job-step-output-file.png\")
<\/p>\n $(ESCAPE_SQUOTE(SQLLOGDIR))\\\n $ESCAPE_SQUOTE(JOBNAME))_$(ESCAPE_SQUOTE(JOBID))\n _$(ESCAPE_SQUOTE(STEPID))_$(ESCAPE_SQUOTE(STRTDT))\n _$(ESCAPE_SQUOTE(STRTTM)).txt<\/pre>\n
C:\\Program Files\\Microsoft SQL Server\\150\\Log\\\n JobA_0x0A15AF54FEEECC4BB9A662C8C288D178_1_20240215_123456.txt<\/pre>\n
\n
SeAssignPrimaryTokenPrivilege<\/code> right; see this article for more info:<\/p>\n\n
What else can we do?<\/h4>\n
\n
@debug<\/code> flag to print the output by default, but override it from the job step. Or, at least, only output the bad stuff. Or suppress all output by default and only turn it on when really<\/em> debugging. Some useful tidits from Erik Darling here<\/a>.<\/li>\nSET NOCOUNT ON;<\/code> to eliminate DONE_IN_PROC<\/code> messages (like n row(s) affected<\/code>). I talked about NOCOUNT<\/code> here<\/a>.<\/li>\nDBCC<\/code> commands that support it, use the WITH NO_INFOMSGS<\/code> argument.<\/li>\n<\/ul>\nANSI_WARNINGS<\/h4>\n
ANSI_WARNINGS<\/code> – this was intentional, as I wanted to treat this option separately. The output I showed above had the following:<\/p>\n Warning: Null value is eliminated by an aggregate or\n other SET operation.<\/pre>\n
SET ANSI_WARNINGS OFF;<\/pre>\n
[SQLSTATE 01000] (Error 0) Heterogeneous queries require\n the ANSI_NULLS and ANSI_WARNINGS options to be set for \n the connection. This ensures consistent query semantics. \n Enable these options and then reissue your query.<\/pre>\n
On the other end<\/h4>\n
msdb<\/code>. You can control this instance-wide using the following SQL Server Agent Properties dialog:<\/p>\n![\"SQL](\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2023\/12\/agent-properties.png\")
<\/p>\nConclusion<\/h3>\n