Learn more about SQL Server tools

mssqltips logo
giveaway
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Error running the SQL Server Database Tuning Advisor for non SA users


By:   |   Read Comments (3)   |   Related Tips: More > Performance Tuning


ALERT: Share your SQL Server knowledge and become a MSSQLTips author


Problem

A user with db_owner access was trying to run the Database Tuning Advisor (DTA) on a newly built SQL Server, unfortunately he was getting this error message: "Failed to open a new connection. In order to start tuning application needs to create some objects in MSDB database. You have to be a member of sysadmin fixed server role in order to initialize MSDB for tuning (DTAClient)." Do you have any suggestions on how to solve this issue? Check out this tip to learn more.

Solution

This error, "Failed to open a new connection. In order to start tuning application needs to create some objects in MSDB database. You have to be a member of sysadmin fixed server role in order to initialize MSDB for tuning (DTAClient)", occurs when the Database Tuning Advisor (DTA) is not initialized on that instance by a SQL Server System Administrator the first time the application is launched. Luckily, if the DTA has already been run on an instance by a SQL Server System Administrator you will not face this issue.

Just to take a step back, the Microsoft SQL Server Database Engine Tuning Advisor helps you select and create an optimal set of indexes, indexed views, and partitions without requiring an expert understanding of the structure of the database or the internals of Microsoft SQL Server. This is achieved by the Database Engine Tuning Advisor analyzing a workload and the physical implementation of one or more databases. A workload is a set of Transact-SQL statements that execute against a database or databases that you want to tune. Database Engine Tuning Advisor uses trace files, trace tables, or Transact-SQL scripts as workload input when tuning databases.

After analyzing a workload, the Database Engine Tuning Advisor can recommend adding, removing or modifying the physical design structures in your databases. The advisor can also recommend what statistics should be collected to improve the physical design structures. The physical design structures include clustered indexes, nonclustered indexes, indexed views, and partitions. The Database Engine Tuning Advisor is intended to recommend a set of physical design structures that reduces the query optimizers estimated cost of the workload.

On first use, the Database Engine Tuning Advisor must be started by a user with system administrator permissions to initialize the application. After initialization, both members of the sysadmin fixed server role and members of the db_owner fixed database role can use Database Engine Tuning Advisor to tune databases. Keep in mind however, that the db_owner role members can only tune databases that they own.

To initialize Database Engine Tuning Advisor, a user with SQL Server System Administrator (sa) permissions performs either of the following actions:

  • Use the Database Engine Tuning Advisor graphical user interface to connect to an instance of Microsoft SQL Server 2005 or later
  • Use the dta utility to tune the first workload

SQL Server Database Engine Tuning Advisor Objects in the MSDB Database

Below is a screen shot of the objects created in the MSDB database by the SQL Server Database Engine Tuning Advisor:

SQL Server Database Engine Tuning Advisor Objects in the MSDB Database
Next Steps


Last Update:


signup button

next tip button



About the author
MSSQLTips author Manvendra Singh Manvendra Singh has over 5 years of experience with SQL Server and has focused on Database Mirroring, Replication, Log Shipping, etc.

View all my tips
Related Resources





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


SQL tips:

*Enter Code refresh code     



Thursday, February 06, 2014 - 5:26:46 PM - BvU Back To Top

 

Hi found the solution of the error specified in my previous post.

I had to enable shared memory protocol of SQL Server Native Client configuration.

Thanks

BvU


Thursday, February 06, 2014 - 4:21:02 PM - BvU Back To Top

Thanks for this helpful post.

 

However I'm experiencing trouble to connect to SQL Server using DTA.

my login is member of the sysadmin server role, I alos tried to run dtashell using Windows 7 Administrator

privileges.

The error I receive is:

Failed to initialize MSDB database for tuning (Exitcode: -1). (DTAClient)

 

The objects above are not in my msdb list of tables.

Appreciate any help!

- Bodo


Tuesday, September 03, 2013 - 10:07:39 AM - Obula Back To Top

Windows -> Start -> type "dtashell" in the searchbox,

if you can see dtashell in the results, give right click on  it and select "Run as administrator".
now connect to the DTA using any type of authentication which has sysadmin server role. (sysadmin is not mandatory).

--

Obula


Learn more about SQL Server tools