Case Notes: TFS 2010 Configuration Process fails with Error: Database 'Tfs_DefaultCollection' already exists.

  • Article

Recently I was working on a case where a customer was unable to pass the configuration part that usually follows the TFS installation routines.

Symptoms:  

The errors he was getting read like this:

[Info @07:54:06.224] [2011-10-19 07:35:14Z][Informational] Microsoft.TeamFoundation.Framework.Server.CollectionServicingException: CreateDatabase.sql Line 2 Error: Database 'Tfs_DefaultCollection' already exists. Choose a different database name. ---> System.Data.SqlClient.SqlException: Database 'Tfs_DefaultCollection' already exists. Choose a different database name.
at Microsoft.TeamFoundation.Framework.Server.TeamFoundationSqlResourceComponent.MapException(SqlException ex, QueryExecutionState queryState)
at Microsoft.TeamFoundation.Framework.Server.TeamFoundationSqlResourceComponent.HandleException(SqlException ex)
at Microsoft.TeamFoundation.Framework.Server.TeamFoundationSqlResourceComponent.Execute(ExecuteType executeType, CommandBehavior behavior)
at Microsoft.TeamFoundation.Framework.Server.TeamFoundationSqlResourceComponent.ExecuteNonQuery(Boolean bindReturnValue)
at Microsoft.TeamFoundation.Framework.Server.SqlScriptResourceComponent.ExecuteSql(String sqlScriptResourceName, String[] lines, SqlParameter[] sqlParameters)….
 

Analysis:

The obvious assumption is that the database indeed already exists. However, that was not the case. I could use any unique database name for the configuration job getting the  same error. After the job failed one could observe that the database has been created successfully and just the config job didn’t appear to see it.

While tracking it further down with SQL Profiler I’ve seen there was one SQL Connection associated to the admin account (the one that started the config job) creating the database.

create

Another SQL Connection associated to the TFSService Account was later testing for the existence of the collection database by evaluating this query:

create2

At the execution time of this query the database already existed but the query did not respond appropriately (didn’t see it) so the question was: why the heck?

  1: if (not(exists(select * from sys.sysdatabases where name = @dbname)))

Problem isolation:

To verify the correctness of my assumption I ran the query on sysdatabases with SQL Manager twice:

Query result when executed as admin:

sql3

Query result when executed as TFSService:

sql4

 

Solution:

After showing the results to the customer and searching through the SQL Docs we figured out that the customer once ran the following statement to hide his databases from other SQL Server users:

  1: REVOKE VIEW ANY DATABASE TO public

After we re-granted view permission to the public group all databases were visible to any user again and the TFS configuration job succeeded.

  1: GRANT VIEW ANY DATABASE TO public

Case closed Thumbs up