SQL Overview IV - DBA's Morning Review

  • Comments posted to this topic are about the item SQL Overview IV - DBA's Morning Review

    David Bird

  • Pretty good article and code... Well done!

  • Excellent article. Thanks for the resources provided in the end. 🙂

  • The zip files seems to be unavailable.

  • Very nice article 😎


  • Download the zip package under the Resources heading.

    The one under Additional Resources does not work. Sorry

    David Bird

  • I was wondering what do you suggest for monitoring SQL job that are hanging or running non stop?

  • That problem has bitten me more a than a few times. I have yet to develop an approach to handle all jobs. For database backups, missing a backup could be caused by the job hanging. You would be notified of missing backups using the reports in this article.

    For a specific job, I have used the following SQL in a job on the job's instance to check if the job has finished within the last day.


    (select 1

    from msdb.dbo.sysjobhistory jh inner join msdb.dbo.sysjobs j on j.job_id = jh.job_id

    where DATEADD (ss, (jh.run_duration/10000 * 3600) +

    (jh.run_duration/100 %100 * 60) +

    jh.run_duration %100, +

    CAST(CAST(jh.run_date AS char(8)) + ' ' + -- Convert run_date to DateTime data type

    STUFF(STUFF( -- Insert : into Time

    RIGHT('000000' + -- Add leading Zeros

    CAST(jh.run_time AS varchar(6)) ,6) , 3, 0, ':'), 6, 0, ':') AS datetime) )

    > dateadd (dd,-1,getdate())

    and j.name = 'DBA-Job that sometimes Hangs'

    and jh.step_id = 0



    EXEC msdb.dbo.sp_send_dbmail

    @profile_name = 'Default',

    @recipients = 'me@company.com',

    @subject = '*** ALERT - Archive SPT Logs Not Finished ****',

    @body = 'Check SERVER and see why the job DBA-Job has not finished' ;


    To monitor all jobs for this type of problem is a goal of mine that I have yet to tackle.

    David Bird

  • great article!

    But, I think some other points might need to be monitor,

    for example, cpu usage, disk io, etc..; system event log, cluster log, etc..;

    DB file growth, table space growth, etc..;

    duration growthof jobs, index usage, health of data buffer, etc..

    In addition, we can cycle errlog when finding errlog size is too big,

    we can use logparser to filter the errorlog that is old history date before two days, to increase the ERRORLOG reading performance.

  • Wyfccc thanks for some great ideas.

    I will looking into the Log Parser tool but if its executable needs to be on each server. It may not be worth using on all servers.

    David Bird

  • Thanks for quick response,

    Please let me know when successful with the all jobs monitoring...

    Thank you!

  • The download has been updated to correct a few bugs, courtesy of Mr. Bird.

  • Mr. Bird^_^

    I learned a lot from your article. thanks a lot.

    If it is allowed to use sp_cmdshell, we can use findstr, that is a windows command, to find the error message we cared in SQL ERRORLOG. It is more efficient.

  • Instead of having the log parser on each server, you may have each server configured so sql server error messages are forwarded to a single server where the log parser tool is executed.

  • I would just like to say THANK YOU. Not having to look at each server for failed jobs is a huge time saver.

    Luke C
    MCSE: Data Platform, MCP, MCTS, MCITP - Database Administrator & Database Developer

Viewing 15 posts - 1 through 15 (of 24 total)

You must be logged in to reply to this topic. Login to reply