Wanted to share something I ran into recently in case it saves someone else a debugging session.
The default "BFEnterprise Full Database Index Reorganization" SQL Agent job was reporting Succeeded every night, no exceptions. Meanwhile, BESAdmin.exe /checkdbindexfrag showed one index sitting at 98% fragmentation for weeks.
Turned out the job was hitting Lock request time out period exceeded on a handful of tables (FIXLETRESULTS, VERSIONS, EXTERNAL_OBJECT_DEFS were the recurring offenders in my environment), catching the error internally, and moving on to the next index — all while the job's overall status stayed green. The only way to actually see it was reading the message column in sysjobhistory line by line, not just checking run_status.
Once I confirmed the pattern was recurring rather than a one-off, I put together:
-
A PowerShell script that checks the job's last-run output for that specific error and writes a small rolling status log
-
A Scheduled Task that runs it nightly
-
A BigFix Analysis (Relevance + a few properties) that surfaces the result, so I don't have to remember to go check SQL Agent manually
Full writeup with the working script, the exact BESAdmin commands, and the Relevance (including a few rounds of debugging I had to go through to get the string-parsing right) is here:
https://www.linkedin.com/pulse/bigfix-monitor-sql-reindex-job-brad-sexton-nmmme
Curious if anyone else has hit this same lock-timeout pattern on their reindex job, or has a different approach to monitoring it. Happy to share the raw script directly here too if useful.
