none
Microsoft SQL Server Error 5170 when creating table

    Question

  • Hi,

    Last month I created a database named _temp. I imported data into it and then renamed the database to something else. Now I'm trying to create another database named _temp, but MSSMS throws an error:

    Create failed for Database '_temp'. (Microsoft.SqlServer.Smo)

    Additional Information:

         An exception occurred while excuting a Transact-SQL statement or batch.
         (Microsoft.SqlServer.ConnectionInfo)

              Cannot create file 'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQ\DATA\_temp.mdf' because it already exists. Change the file path or the file name, and retry the operation. CREATE DATABASE failed. Some file names listed could not be created. Check related errors. (Microsoft SQL Server, Error: 5170)

     I can't navigate to the file to delete it; the folder "MSSQL10.MSSQLSERVER" doesn't appear. The ideal solution would be to be able to create another _temp database. Anyway I can delete the old files that are blocking me?

    Thursday, April 04, 2013 8:50 PM

Answers

  • While you renamed the original database, the file names did not change.

    Two options:

    1) use different file names for this database:

    CREATE DATABASE _temp ON (NAME = '_temp',
       FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\_temp_of_20130404.mdf')
    LOG ON (NAME = '_temp_log',
       FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\_temp_of_20130404.ldf')

    2) Use ALTER DATABASE to change the name of the other databaseÖ

    ALTER DATABASE used_to_be_temp
       MODIFY (NAME = '_temp', FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\user_to_be_temp.mdf')

    ALTER DATABASE used_to_be_temp
       MODIFY (NAME = '_temp_log', FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\user_to_be_temp.ldf')


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    • Proposed as answer by Naomi NModerator Thursday, April 04, 2013 10:21 PM
    • Marked as answer by Woojamon Friday, April 05, 2013 6:05 PM
    Thursday, April 04, 2013 9:53 PM

All replies

  • Have you re-started SQL Server since that?

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    Thursday, April 04, 2013 9:06 PM
    Moderator
  • While you renamed the original database, the file names did not change.

    Two options:

    1) use different file names for this database:

    CREATE DATABASE _temp ON (NAME = '_temp',
       FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\_temp_of_20130404.mdf')
    LOG ON (NAME = '_temp_log',
       FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\_temp_of_20130404.ldf')

    2) Use ALTER DATABASE to change the name of the other databaseÖ

    ALTER DATABASE used_to_be_temp
       MODIFY (NAME = '_temp', FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\user_to_be_temp.mdf')

    ALTER DATABASE used_to_be_temp
       MODIFY (NAME = '_temp_log', FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\user_to_be_temp.ldf')


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    • Proposed as answer by Naomi NModerator Thursday, April 04, 2013 10:21 PM
    • Marked as answer by Woojamon Friday, April 05, 2013 6:05 PM
    Thursday, April 04, 2013 9:53 PM
  • Hi,

    Please try to run Management Studio in administrator mode. Are you running the query against a server that's not your local machine? If so, the folder my exist at the remote server, but not exist at your local machine.

    Hope this helps,


    Pedro Martins
    Portugal
    https://www.linkedin.com/in/rechousa

    Thursday, April 04, 2013 10:01 PM
  • Thanks, Erland!


    I think your solution #2 was originally what I was looking for. I tried it and I still couldn't create a new _temp database due to the same error.

    I tried your solution #1 except it wouldn't let me use "NAME = '_temp'". So I used the below command and it worked just fine. I was able to import my data using an import package (which looks for a database named _temp) and rename the _temp database to it's final name. Everything works fine and I think I'll use this solution from now on. Thanks!

    create

    database _temp on (name='_temp2013Apr', filename='C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\EOM2013Mar.mdf') log on (name='EOM2013Mar_log', filename='C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\_temp2013Apr.ldf')
    • Edited by Woojamon Friday, April 05, 2013 6:06 PM
    Friday, April 05, 2013 6:05 PM
  • Thak's a lot! It was awsome. Successful
    Tuesday, February 18, 2014 4:48 PM