I realize it has been a while since I had setup my maintenance plan to backup the user databases and transaction logs, so I went in to see how they were doing. But I see they are not there anymore.
I also had a weekly maintenance plan to shrink the user databases.
The alerts you have setup have thresholds too low. They don't take into account a database in "full" mode.
And you should never get an alert on the tempdb system database.
I went out and looked at the physical size of these databases and log sizes. They are very small compared to normal operation. The alert settings need to be changed.
I am changing the user databases to "simple" recovery model for now. Probably need to turn off the hourly transaction log backup for now.
I have run the shrink database on the user databases and on tempdb.I'm glad database size is a concern, but we should look at sp_spaceused to see if we just plain need more space or if we're potentially over-indexed. And shrinking tempdb is documented to cause corruption.
I also see that the SQL backups are only for three days. Are these being swapped out so that there are actually more days being stored somewhere else (offsite maybe)?.
Looking at the larger picture, there is a VSS aware 3rd party backup system in place. SQL Server backups exist for the purpose of near-line recoverablity and point-in-time recoverability. Remember, we're space constrained, and you've just implimented a Shrink Database to address it, so we've only got so much space for backup retention.
Please do not change any general or options properties on the user databases. I will maintain these. Just let me know if you need something changed as some items can have repercussions on the functionallity of applications.
The biggest point here is that user applications are two completely separate systems sharing the SQL server. They really need separate backup and maintenance plans as the data is used and maintained differently.
Taking a holistic view of the application and server together, these backup plans may need crash consistency between databases. We've already shown that there are some unique issues with the compatilbity_level and "other general settings".
The SQL server is two service packs behind. SQL Server 2005 should be on SP4.SQL Server 2005, even at Service Pack 4 with be out of maintenance from Microsoft as of 12/2011. And this doesn't jive with the compatibility_level request from earlier. If I'm looking to future proof my application and move to SQL Server 2012, I won't be able to attach my database, let alone restore it.
How can we be productive about these recommendations as consultant generalists and DBAs?
Get the stake holders into a stand-up meeting. Get the overall goals for the application written down. What's our time to recovery needs? What's our point in time recovery needs? Who's responsible for what part of a disaster recovery? How about performance? What's the life-cycle of this application?