I have blogged about this briefly in the past, but I am going to post a full link set to all of the code that I use to automate monitoring of my production database servers. All of the code is available on the MSDN Forums SQL Examples Wiki site, and I might back post the series in my blog at a later date but I may not. The entire series is sub categorized under the main article Automating Common DBA Tasks.
Inside this the code is sub-categorized into TSQL monitoring through SQL Agent:
Configuring SQL Server 2000 Notification with CDOSys
Configuring SQL Server 2005/2008 Database Mail
Log file growth in SQL Server
Monitor free space in the database files
Monitor free space on the server hard disks
Monitor the SQL Server Error Log
Monitor long running SQL Agent Jobs
Monitor failed SQL Agent Jobs
and VBScript/WMI monitoring through Windows Task Scheduler:
Please feel free to contact me and let me know if you have any issues with using this code. It is not exactly identical to the code in my production servers since there is some proprietary information in my own production code, but the changes are only slightly minor, and I have done my best to test and validate the code I published, and it all should work as intended.