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

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.