I often get questions in e-mails, or at user group meetings, for advice on writing T-SQL database maintenance scripts. There seem to be a lot of DBAs who need more flexibility than what the Database Maintenance Wizard offers to perform database maintenance, which means they must user either T-SQL or PowerShell scripts in order to get this flexibility. Unfortunately, many of the DBAs who ask me this question don’t have a lot of time, or T-SQL experience, so they don’t know where to start.
The advice I generally give to them is to not reinvent the wheel, but to see what others have done, and to emulate them, especially to those DBAs with lesser experience. For example, the following websites offer T-SQL database maintenance scripts that are freely available for others to use with little or no modification, or to use as a learning tool in order to see how other DBAs have tackled this issue.
ola.hallengren.com
sqlfool.com/2009/06/index-defrag-script-v30
www.grics.qc.ca/YourSqlDba/index_en.shtml
All of the scripts from the above websites are very good, and are highly recommended. Of course, I don’t recommend you run any free script on any of your production servers as they are. If you download any of the above T-SQL scripts, first take some time to understand how they work, and see if they are appropriate for your needs. Often, you may need to modify them for your specific circumstances. In addition, you will want to test any script on a test server before rolling them out into production.
If you know of other useful database maintenance scripts available free on the web, please feel free to share their URLs here.