YourSQLDba

If your organization has a policy against database maintenance plans or would like to move away from database maintenance plans before Microsoft deprecates them, you should take a look at YourSQLDba. YourSQLDba is a T-SQL alternative to database maintenance plans. Because YourSQLDba is based on T-SQL, it’s much easier to implement than SQL Server Integration Services (SSIS) packages used for database maintenance. It also provides much better logging than database maintenance packages and includes the ability to dynamically select the databases in which database maintenance will take place.

YourSQLDba was written by Maurice Pelchat of the Societe GRICS in Canada and is distributed under the GNU General Public License. Maurice wrote the tool to make the deployment, maintenance, and troubleshooting of routine database maintenance activities on SQL Server machines easier. Let’s look at the tasks YourSQLDba can be configured to do.

What YourSQLDba Can Do
YourSQLDba, a multi-faceted stored procedure called YourSQLDba_DoMaint, includes a variety of features that are useful for automating backups, consistency checks, and other preventative maintenance tasks. Its default configuration uses two SQL Server Agent tasks, YourSQLDba_LogBackups and YourSQLDba_FullBackups_and_Maintenance, to back up all transaction logs every 15 minutes and, for logs that have changed dramatically in size, compresses oversized log files. In addition, the default configuration cleans up server and agent logs, checks database integrity, updates distribution statistics, rebuilds and reorganizes indexes (based on their fragmentation), and performs a full database backup of each database. It concludes its default operations by backing up MSDB so that you can easily keep track of all database backups and SQL Server Agent job histories.

In addition, YourSQLDba can be configured to

• automate full database backups and recurring transaction log backups
• maintain detailed logs containing information about its actions and any errors encountered
• update statistics at regular intervals (defaults to once per week)
• reorganize or rebuild indexes, choosing the best defragmentation option (whether to rebuild or simply reorganize) at the time of execution
• check database integrity
• keep an up-to-date backup of MSDB, which records all backup history at the end of each full or log backup cycle
• clean up the various history logs in SQL Server, including the backup log, SQL Server Agent, and SQL Server maintenance job histories
• recycle the SQL Server error log each day (it can store up to 30 days in its archives)
• connect network drives to the database engine for backup purposes and make them automatically reconnect at startup
• provide simplified system stored procedures to perform manual backups, duplicate databases, and database restores
• schedule agent tasks for maintenance
• configure Database Mail for maintenance reporting
• send email notifications regarding maintenance activity
• send email notifications when an especially problematic maintenance issue arises
• disable databases with integrity problems immediately
• disable databases that have failed more than three consecutive backups (The counter is reset after a manual backup.)

Because YourSQLDba is a stored procedure, you must compile it on every SQL Server instance in which you want to deploy it. When deployed, it creates a small database called YourSQLDba, as well as several small objects. Also, YourSQLDba is smart in upgrade situations, replacing all objects that have changed since the last time you deployed the script. It creates a single startup stored procedure called CreateNetworkDrive in the Master database. It also creates a database mail profile and SQL Server Agent operator.

System Requirements
YourSQLDba uses simple T-SQL to perform database maintenance tasks, and makes calls to xp_cmdshell and sqlcmd.exe. Therefore, it works seamlessly with both SQL Server 2008 and SQL Server 2005 in 32-bit and 64-bit environments. YourSQLDba runs on any OS that supports SQL Server 2008 or 2005.