SQL Overview IV - DBA's Morning Review

  • David Bird

    SSCarpal Tunnel

    Points: 4669

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

    David Bird

  • SuperDBA-207096

    SSCrazy Eights

    Points: 8176

    Pretty good article and code... Well done!

  • Anipaul


    Points: 24681

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

  • Igor Santos

    Mr or Mrs. 500

    Points: 530

    The zip files seems to be unavailable.

  • Dugi


    Points: 17998

    Very nice article 😎


  • David Bird

    SSCarpal Tunnel

    Points: 4669

    Download the zip package under the Resources heading.

    The one under Additional Resources does not work. Sorry

    David Bird

  • ephremm

    SSC Rookie

    Points: 31

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

  • David Bird

    SSCarpal Tunnel

    Points: 4669

    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

  • wyfccc

    SSC Rookie

    Points: 41

    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.

  • David Bird

    SSCarpal Tunnel

    Points: 4669

    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

  • ephremm

    SSC Rookie

    Points: 31

    Thanks for quick response,

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

    Thank you!

  • Steve Jones - SSC Editor

    SSC Guru

    Points: 717406

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

  • wyfccc

    SSC Rookie

    Points: 41

    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.

  • eletuw


    Points: 2464

    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.

  • Luke C

    Right there with Babe

    Points: 790

    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 25 total)

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