Failed to create database to it's default location via T-SQL, But do able to do the same from GUI on CLUSTER Any Help ?

  • Hello,

    I am trying to create one database on my instance from t-sql, it is failing. While doing the same from GUI, it does create it.

    Default data and log locations in the registery as well as in the instance properties under database settings:

    DATA: E:\SQLDATA

    LOG : L:\SQLLOG

    When trying to run the below statement..

    CREATE DATABASE test

    getting error: 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 'E:\SQLLOG\test_log.LDF'.

    Msg 1802, Level 16, State 4, Line 1

    CREATE DATABASE failed. Some file names listed could not be created. Check related errors.

    Further checking,

    I am currently on Cluster server, connecting to the Virtual server name and is on Node A. Node A has the new registery settings for DATA and LOG location i.e. E:\SQLDATA and L:SQLLOG

    But when I check on passive node (B); Default data and log locations are: E:\SQLDATA and E:\SQLLOG

    Now, this doesn't make any sense to me - while creating the database from T-SQL, why SQL Server using the registery settings for passive node B and throwing this error? and when doing via GUI, it uses the registery settings on A node.

    Make sense? Please advice.

    Thanks,

    AB

  • Our On-Site Microsoft support Engineer looked at it and suggested us to failover and failback SQL instance since, changing of default database location requires restart of SQL Server.

    Interesting "changing of default database location requires restart of SQL Server" wasn't aware of this...seems silly but I guess if it writes to the registry it would need to.

    Thanks,

    AB

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply