May 31, 2008 at 5:05 pm
Comments posted to this topic are about the item SQL Overview IV - DBA's Morning Review
David Bird
June 2, 2008 at 4:31 am
Pretty good article and code... Well done!
June 2, 2008 at 6:15 am
Excellent article. Thanks for the resources provided in the end. 🙂
June 2, 2008 at 7:40 am
The zip files seems to be unavailable.
June 2, 2008 at 8:22 am
June 2, 2008 at 8:36 am
Download the zip package under the Resources heading.
The one under Additional Resources does not work. Sorry
David Bird
June 2, 2008 at 2:29 pm
I was wondering what do you suggest for monitoring SQL job that are hanging or running non stop?
June 2, 2008 at 2:55 pm
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.
IF NOT EXISTS
(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
)
BEGIN
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' ;
END
To monitor all jobs for this type of problem is a goal of mine that I have yet to tackle.
David Bird
June 3, 2008 at 4:28 am
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.
June 3, 2008 at 10:18 am
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
June 3, 2008 at 10:31 am
Thanks for quick response,
Please let me know when successful with the all jobs monitoring...
Thank you!
June 3, 2008 at 12:27 pm
The download has been updated to correct a few bugs, courtesy of Mr. Bird.
June 3, 2008 at 11:32 pm
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.
June 6, 2008 at 5:30 am
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.
June 14, 2008 at 8:46 am
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