You may receive error messages when you use the CREATE DATABASE statement to create a SQL Server database (836873)


The information in this article applies to:

  • Microsoft SQL Server 2000 (all editions)
  • Microsoft SQL Server 7.0

Important This article contains information about modifying the registry. Before you modify the registry, make sure to back it up and make sure that you understand how to restore the registry if a problem occurs. For information about how to back up, restore, and edit the registry, click the following article number to view the article in the Microsoft Knowledge Base:

256986 Description of the Microsoft Windows Registry

SYMPTOMS

When you try to create a Microsoft SQL Server database by running the CREATE DATABASE Transact-SQL statement, you may not be able to create the database, and you may receive the following error messages:
Server: Msg 5105, Level 16, State 2, Line 1
Device activation error. The physical file name '<database file name>' may be incorrect.
Server: Msg 5123, Level 16, State 1, Line 1
CREATE FILE encountered operating system error 3(The system cannot find the path specified.) while attempting to open or create the physical file '<Full path of the database file name>'.
Additionally, you may receive the following error message:
Server: Msg 1802, Level 16, State 1, Line 1
CREATE DATABASE failed. Some file names listed could not be created. Check previous errors.

CAUSE

This problem occurs because the DefaultData and DefaultLog registry values that correspond to the default data directory and to the default log directory exist, but the folder paths that are mentioned in the registry values are either blank or do not exist.

WORKAROUND

To work around this problem, use one of the following methods:

Set the default data directory value and the default log directory value to valid folder paths

You can set the default data directory value and the default log directory value by using either SQL Server Enterprise Manager or Registry Editor.

Use SQL Server Enterprise Manager

Note The instance of SQL Server Enterprise Manager that is included with SQL Server 7.0 does not support setting the default data directory and the default log directory. However, you can register your instance of SQL Server 7.0 in the instance of SQL Server Enterprise Manager that is included with SQL Server 2000, and you can then follow these steps to set the default data directory and the default log directory for your instance of SQL Server 7.0.
  1. Click Start, point to Programs, point to Microsoft SQL Server, and then click Enterprise Manager.
  2. In SQL Server Enterprise Manager, right-click your instance of SQL Server, and then click Properties.
  3. In the SQL Server Properties (Configure) - <Instance Name> dialog box, click the Database Settings tab.
  4. In the New database default location section, type a valid folder path in the Default data directory box and in the Default log directory box.
  5. Click OK.
  6. Stop your instance of SQL Server, and then restart your instance of SQL Server.
Use Registry Editor

Warning If you use Registry Editor incorrectly, you may cause serious problems that may require you to reinstall your operating system. Microsoft cannot guarantee that you can solve problems that result from using Registry Editor incorrectly. Use Registry Editor at your own risk.

Note The instance of SQL Server Enterprise Manager that is included with SQL Server 7.0 does not support setting the default data directory and the default log directory. However, you can modify the registry values that correspond to the default data directory and the default log directory to work around this problem.
  1. Click Start, and then click Run.
  2. In the Run dialog box, type Regedit in the Open box, and then click OK.
  3. In Registry Editor, select the following registry key for a default instance of SQL Server:

    HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\MSSQLServer

    In Registry Editor, select the following registry key for a named instance of SQL Server:

    HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\<Instance Name>\MSSQLServer

  4. In the right pane, right-click the DefaultData registry value, and then click Modify.
  5. In the Edit String dialog box, type the valid folder path in the Value data box, and then click OK.
  6. Repeat step 4 and step 5 for the DefaultLog registry value.
  7. Stop your instance of SQL Server, and then restart your instance of SQL Server.

Remove the registry values that correspond to the default data directory and the default log directory

Warning If you use Registry Editor incorrectly, you may cause serious problems that may require you to reinstall your operating system. Microsoft cannot guarantee that you can solve problems that result from using Registry Editor incorrectly. Use Registry Editor at your own risk.
  1. Click Start, and then click Run.
  2. In the Run dialog box, type Regedit in the Open box, and then click OK.
  3. In Registry Editor, locate and select the following registry key for a default instance of SQL Server:

    HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\MSSQLServer

    In Registry Editor, locate and select the following registry key for a named instance of SQL Server:

    HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\<Instance Name>\MSSQLServer

  4. In the right pane, delete the DefaultData registry value and the DefaultLog registry value.
  5. Stop your instance of SQL Server, and then restart your instance of SQL Server.

MORE INFORMATION

When you set up an instance of SQL Server, the Setup program does not create the registry entries that correspond to the default data directory and the default log directory. Therefore, when you view the New database default location section in SQL Server Enterprise Manager, the paths for Default data directory and Default log directory are blank.

For additional information, click the following article number to view the article in the Microsoft Knowledge Base:

272705 Default data and log directory display blank by default after you install SQL Server 2000

However, when you set the default data directory and the default log directory for the first time, the corresponding DefaultData and DefaultLog registry values are created in the following registry key for a default instance of SQL Server:

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\MSSQLServer

The DefaultData and DefaultLog registry values are created in the following registry key for a named instance of SQL Server:

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\<Instance Name>\MSSQLServer

The DefaultData registry value contains the folder path that is set for the default data directory. Similarly, the DefaultLog registry value contains the folder path that is set for the default log directory.

When you create a new database by using the CREATE DATABASE Transact-SQL statement, SQL Server tries to create the data file and the log file that correspond to the database in the folders that are specified in the DefaultData registry value and in the DefaultLog registry value. Therefore, if the DefaultData registry value and the DefaultLog registry value contain folder paths that do not exist, SQL Server cannot create the corresponding data file or log file for the database successfully, and you receive the error messages that are mentioned in the "Symptoms" section.
Modification Type:MajorLast Reviewed:2/20/2004
Keywords:kbTSQL kbDatabase kberrmsg kbUpdate kbfile kbRegistry kbprb KB836873 kbAudDeveloper
©2004 Microsoft Corporation. All rights reserved.