If you have multiple systems in your organization, and especially if you use multiple kinds of systems (such as exchange, Forefront and SQL Server, for instance) then you might want to check out Microsoft System Center, and in particular, System Center Operations Manager.You might know this product by it's old name of "MOM" (Microsoft Operations Manager), but it's a new animal now. I'm working on the "Management Packs" and "Health Models" for SQL Server for this product.
Basically it allows you to define a model of your system, including SQL Server. You can then use the Health Model to describe when it is working well, not working well, or failing. The interesting thing is that it comes with descriptions of why something happened as well as what you can do about it - sort of a "consultant in a box". I'll be blogging more about how you can use it as I develop these packs and models.