SQL SERVER – Error: Fix: Msg 5133, Level 16, State 1, Line 2 Directory lookup for the file failed with the operating system error 2(The system cannot find the file specified.) – Part 2

Errors are a great starting point for learning. Especially, my inbox is always flooded with atleast 2-3 error messages almost every other day. People keep asking me how to solve them, what to do when we encounter an error and so on. Long time back I wrote a simple blog which talks about error message 5133 while creating database. You can read it here:

SQL SERVER – Error : Fix : Msg 5133, Level 16, State 1, Line 2 Directory lookup for the file failed with the operating system error 2(The system cannot find the file specified.)

One of the readers sent me email that she is simply running Create Database <DatabaseName> command and still get the above error. In my blog I mentioned “There must be some typo or error in filepath” but in this case there is no path given. This time I wanted to reproduce the error and was intrigued to why they were getting this error with the simple TSQL Create database command.

I spent some time researching and spoke to few friends about this. They informed that there is a setting in SQL Server which has default path of database files which is picked if nothing is specified. And it made complete sense. Based on this information,  I was finally able to reproduce the error by following his instructions. Here is the error:

Msg 5133, Level 16, State 1, Line 14
Directory lookup for the file “E:\InvalidPath\SQLAuth.mdf” failed with the operating system error 2(The system cannot find the file specified.).
Msg 1802, Level 16, State 1, Line 14
CREATE DATABASE failed. Some file names listed could not be created. Check related errors.

SQL SERVER - Error: Fix: Msg 5133, Level 16, State 1, Line 2 Directory lookup for the file failed with the operating system error 2(The system cannot find the file specified.) - Part 2 CreateDB-01

Solarwinds

If you look at the path it is “E:\InvalidPath\SQLAuth.mdf”. The path is picked from the below registry value:

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL12.SQL2014\MSSQLServer

SQL SERVER - Error: Fix: Msg 5133, Level 16, State 1, Line 2 Directory lookup for the file failed with the operating system error 2(The system cannot find the file specified.) - Part 2 CreateDB-02

In my case, I have SQL 2014 named instance called SQL2014 – that’s why we are seeing MSSQL12.SQL2014

Here is the T-SQL way to get the values.

SELECT SERVERPROPERTY('InstanceDefaultDataPath') AS [Default_Data_path] SERVERPROPERTY('InstanceDefaultLogPath') AS  [Default_log_path]

SQL SERVER - Error: Fix: Msg 5133, Level 16, State 1, Line 2 Directory lookup for the file failed with the operating system error 2(The system cannot find the file specified.) - Part 2 CreateDB-03

We can change it via SQL Server Management Studio too. You can right click on server node and go to properties. Then choose “Database Settings” tab.

SQL SERVER - Error: Fix: Msg 5133, Level 16, State 1, Line 2 Directory lookup for the file failed with the operating system error 2(The system cannot find the file specified.) - Part 2 CreateDB-04

Following is the T-SQL way to achieve the change.

USE [master] GO
EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer',
N'DefaultData', REG_SZ, N'E:\InvalidPath'
GO
EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer',
N'DefaultLog', REG_SZ, N'E:\InvalidPath'
GO

NOTE: Once you have changed the value, SQL Service restart is needed so that SQL can pick-up changed values.

Reference: Pinal Dave (https://blog.sqlauthority.com)

Solarwinds
Previous Post
SQL SERVER – Error: Msg 701, Level 17, State 103. There is insufficient system memory in resource
Next Post
Developer – 3 Tips Every SQL Expert Needs to Know to Land the Perfect Job (Part 1 of 3)

Related Posts

5 Comments. Leave new

  • Nakul Vachhrajani
    January 28, 2015 1:23 am

    Please note that if the readers are not comfortable with working in the registry, these default paths can also be changed from the SSMS UI. Refer my post for more:

    Reply
    • Thanks Nakul for pointing that. I mentioned the same as text “We can change it via SQL Server Management Studio too. You can right click on server node and go to properties. Then choose “Database Settings” tab”

      Will add image as well.

      Reply
  • aiden carter (@aidencarter16)
    November 18, 2016 1:28 am

    The Long path tool is the very best program for error, unlock solution.
    Try it and solved your problem.
    I used the long path tool and I solved my error, unlocks problem solution.

    Reply
  • Precisely what i was looking for, thanks a lot.

    Reply

Leave a Reply

Menu