For Example:

I have not been able to find a list of distinct or possible LastStatus values. That is why I use key words like Failure or error. If any knows of that list feel free to add a comment on where to find it.
I have also taken this query and made a report that can be run from SSRS. I have granted permissions on the report to our Helpdesk. This allows them to run it as part of their morning processes. They can then be proactive in trouble shooting failed report subscriptions.
One other note about subscriptions, most end users who create their own subscriptions schedule them to run just before they come in for the day. So you will find that most subscriptions are running first thing in the morning. You may choose to monitor this and ask end users to run spread them out over larger time period to balance the load.

There are a few tools and methods to get this done. I’ll start with the best way to document your database. First, write your definitions for each object in plain English. You must write for a non-technical audience. You never know who will end of up with your document. The best location to store each object definition is right within the database. There are multiple reasons for this, the first is that you don’t have to worry about maintaining documentation in multiple locations, the second is there are some good tools that can be used to auto generate your documentation. For example SQL Doc by Red Gate can create a document in html, chm or doc format.

There are two ways to add the definitions to the database. The first way is through the SSMS GUI. For example if you want to add the description for a column you right click on the table name and select Modify. This will open the table definition in the GUI. Select the column that you are going to add the description for. In the lower window, (column properties) there will be an area to enter the description. Add you description and save your changes. I’m not really a fan of this method for two reasons, first, it’s very easy to make an error and say change a datatype without meaning to. The second reason is I believe is every DBA should know the T-SQL behind the GUI. It would also be faster to create one large script and run it all in at once. In order to accomplish that you would use sp_addextendedproperty, see BOL for the syntax and options for this procedure. As you may have guessed, when you add a description to the database it stores them in the system table sysproperties. If you work at a company like mine where your database is in a constant state of development, then ask your developers to include sp_addextendedproperty scripts with their DDL scripts. This will cut down on the leg work you might have to do trying to figure out the description for each column.

Once you’ve completed the discovery phase and clearly documented the database objects then it’s time to create a diagram. There are many tools on the market that can auto generate your diagrams for you. I think one of the easier tools to use is Visio. These tools will include table relationships IF you have created FKs in your database. If you don’t have those relationships defined then the process becomes a bit more involved as you will have to go back and identify those relationships. (That would be a whole new blog on the importance of using FKs) Again, be as detailed as you can here. Make sure you include the type of relationship, is it 1 to 1, 1 to many, many to 1 or many to many. Without this information the developer(s) is at a serious disadvantage and must spend time to figure it out. Depending on the number of tables in your database it may be too large to put it all in one diagram. You may choose to diagram your db by module. For example if you work in the Insurance Industry you may have some like Quotes, Policies, Claims, Documents and WorkFlow. I would create diagram for each module with notation on how Quotes link to Policies, or Policies to Claims.

The importance of this level of documentation speaks for itself. Think how easy it would be to just hand a developer or new DBA a document and diagram to your Sales or Insurance database and say “go to work.” The time saved in trying to figure everything out could add up to weeks. And that is weeks of more production from them!

Some of the folders are obvious but let me go through a few of them. In my executable folder I save every .exe file that I have purchased or downloaded. I don’t usually keep executables if we haven’t installed them. That cuts down on the size and old exe versions. In the DR folder is everything I need for a successful DR cutover. If you are in the middle of a disaster you won’t look very good if you’re googleing for scripts or instructions on how to failover your db to the DR server. Having all that information at your fingertips will not only make you more productive but your boss will appreciate how effective you are.

Every script I write no matter how large or small is saved in the SQL Scripts folder by application. I can’t stress the importance of saving everything your write. You never know when you will need that script or a version of it again. You can save a ton of time if you don’t have to rewrite scripts. Also again, in an emergency you must have everything you’ll need at your fingertips to react quickly and effectively. I have a subfolder just dedicated to SQL Server perf mon scripts. I use them daily! In the SSAS, SSIS, SSRS folder I save all my projects from BIDS. I have these folder linked to source control too so those projects are checked into SVN. In the Bugs and Fixes folder I save everything from every issue I’ve opened with Microsoft or other vendors. This includes email conversations, scripts and the eventual resolution. It makes correcting the problem much faster if it ever arises again. The HA folder is everything I need to setup and maintain High Availability.

The last thing I do on each database server is to setup a mapped drive to this share and create a local DBA share. In the local DBA share I keep scripts that are specific to that database server. Basically it’s a smaller version of the DBA repository.

The DBA repository has become my most valuable tool and has saved the day more than once. Having the repository will help you keep a cool head in an emergency and help you get the answers you need quickly.

So what would be a better way to monitor and alert a DBA when there is a change in the state of Database mirroring? I prefer to use Alerts for events. Event notifications can be created directly in the SQL Server Database Engine or by using the WMI Provider for Server Events. A DBA can specify which db mirroring event they wish to moitor. Here is a table of events to monitor for:

Now that we know the Event and the State here is how to add an Alert to notify you that the state of DB mirroring has changed.

This is an alert that I created on the principal server. I also have created a similar alert on the mirror server where I look for state = 5. These two alerts will notify me if the connection between the Principal and Mirror is lost due to network or some other failure.
To receive notification when this event happens it is simple to just create an operator and have the event email the operator if and when the event conditions are met.

What other Mirror Events should every DBA monitor? I find the unsent and unrestored log to be two very import events to receive notifications for. For those events just simply create a new event for the event ID in the table below and set you monitor threshold.

You can also script this by using sp_add_alert as follows:

Good Luck and Happy monitoring!