SQL Server 2005- Is it possible to schedule Data Tuning Advisor?
I came across interesting post in SSP looking for a way of automating the Database Tuning Advisor tool of SQL Server 2005 with the help of SQLServer Agent. As the originator looking to schedule it on a regular basis to scan a trace (Profiler) file and export the results to a file. I believe this is a good way to avoiding any manual intervention in fine tuning or get to know about such suggestions from DTA.
Yes it is possible (I was not aware of that) and it is documented in the BOL (SQL 2005) to automate the analysis by using DTA command line statement. This will enable that job to opt for a file from a .trc, .sql or .xml file
The dta utility does not delete the contents of user-specified tuning log tables if the session is deleted. When tuning very large workloads, we recommend that a table be specified for the tuning log. Since tuning large workloads can result in large tuning logs, the sessions can be deleted much faster when a table is used.
Refer to the Books online for SQL Server 2005 (updated) for further details on DTA utility.
Tip: limit the session using -A option in DTA in order to limit the disk & resources usage on SQL Server.