SQL Server Performance

[Solved]Automating the "Database tuning Advisor"

Discussion in 'SQL Server 2005 Performance Tuning for DBAs' started by Dale L, Jun 14, 2007.

  1. Dale L New Member

    Im looking for a way of automating the Database Tuning Advisor tool of SQL Server 2005 with the help of sql agent so that i can schedule it on a regular basis to scan a trace file and then make it export the results in a text file.

    The system would need no human interaction at all and the trace file would have have the same name (even if the information within it does change).

    Is there a way for me to script it so it would work?
    Are there any existing command that i could use to help me?
    Is it even possible?


    Dale
  2. satya Moderator

    I don't think it is possible for DTA and you could lookup at latest Books online for SQL2005 for such commandline operations.

    Satya SKJ
    Microsoft SQL Server MVP
    Writer, Contributing Editor & Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.
  3. Dale L New Member

    I just found out this morning that yes it is possible for someone to automate the analysis and i thought i would share that information with you.

    You have to use the command "dta" wich run the analysis on the server.

    It let you choose the file name (wich can be a trc,sql or xml), the session name, the file it will export the result to and a lot of other paremeter.

    I just tried it and it work as fine as running the application directly.

    To get more information about that command :


    quote:
    Dta -?


    Dale

    n.b : a little precision, you got to use "EXEC master.sys.xp_cmdshell *command*" to make it work
  4. satya Moderator

    Dale<br />Thanks, learned something new...that I'm not aware or never come across then.[<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br /><b>Satya SKJ</b><br />Microsoft SQL Server MVP<br />Writer, Contributing Editor & Moderator<br /<a target="_blank" href=http://www.SQL-Server-Performance.Com>http://www.SQL-Server-Performance.Com</a><br /><center><font color="teal"><font size="1">This posting is provided AS IS with no rights for the sake of <i>knowledge sharing. <hr noshade size="1">Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.</i></font id="size1"></font id="teal"></center>
  5. MohammedU New Member

Share This Page