1

I recently was given a SAN with a couple of RAID arrays to apply to my database server to increase performance. I have (and will only ever have) one user database called DDAS, in addition to the system databases that install along with SQL Server.

My intent was to have DDAS pointing to its own drive, tempdb pointing to its own drive and transaction logs pointing to their own drive. The user account running SQL Server has Full Control access to all the drives, I made sure of it before doing anything.

I successfully moved tempdb using the following SQL statements:

ALTER DATABASE [tempdb] MODIFY FILE ( NAME = N'tempdev', FILENAME = N'F:\tempdb.mdf')
ALTER DATABASE [tempdb] MODIFY FILE ( NAME = N'templog', FILENAME = N'F:\templog.ldf')

I restarted SQL Server, voila, tempdb is relocated (can I safely delete the old tempdb files from their original location? Maybe that's another question...)

Now, because DDAS is still in development, I was able to delete all data from all the tables before doing anything to move it to its own drive. So now the file is ~5MB in size. I perform the same statements using the DDAS files:

ALTER DATABASE [DDAS] MODIFY FILE ( NAME = N'DDAS', FILENAME = N'E:\DDAS.mdf')
ALTER DATABASE [tempdb] MODIFY FILE ( NAME = N'DDAS_log', FILENAME = N'G:\DDAS_log.ldf')

I restart SQL Server and it doesn't work... I get DDAS (Recovery Pending) on the database in Management Studio indefinitely. I deleted the database completely because I have a script that will create all the objects etc... intending to just try to re-create it on the drive it is supposed to be on. I attempted to create the database with the following statement:

IF NOT EXISTS (SELECT * FROM sys.databases WHERE name = 'DDAS')
BEGIN
    CREATE DATABASE [DDAS]
    CONTAINMENT = NONE
    ON  PRIMARY 
        ( NAME = N'DDAS', FILENAME = N'E:\DDAS.mdf' , SIZE = 1024000KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024000KB  ) 
    LOG ON      
        ( NAME = N'DDAS', FILENAME = N'G:\DDAS_log.ldf' , SIZE = 1024KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)
    END
GO

but I get this error now:

what

I used this query to list file names being used by SQL Server:

SELECT DB_NAME(database_id) AS DatabaseName, 
       name AS LogicalFileName, 
       physical_name AS PhysicalFileName 
FROM sys.master_files AS mf

and got this result:

result

How is this possible? I tried restarting SQL Server a number of times to no avail. I also checked that the original file on the C:\ drive that DDAS was using has been deleted, as well as its transaction log. There's no trace of DDAS anywhere other than this error message. I also have no open connections to DDAS that might have somehow lingered through all of those restarts.

If it matters: SQL Server Enterprise 2014 x64

closed as off-topic by Max Vernon, Philᵀᴹ, mustaccio, swasheck, ypercubeᵀᴹ Jan 15 '16 at 20:28

This question appears to be off-topic. The users who voted to close gave this specific reason:

  • "Too localized - this could be because your code has a typo, basic error, or is not relevant to most of our audience. Consider revising your question so that it appeals to a broader audience. As it stands, the question is unlikely to help other users (regarding typo questions, see this meta question for background)." – Max Vernon, Philᵀᴹ, mustaccio, swasheck, ypercubeᵀᴹ
If this question can be reworded to fit the rules in the help center, please edit the question.

  • Also, please don't make your growth a percentage... Make it a MB value that makes sense. If you have a drive dedicated to tempdb, which is a good thing, you should probably create tempdb with 8 files sized to almost entirely fill the drive. – Max Vernon Jan 15 '16 at 15:44
3

If i am able to figure out correctly from below query

ALTER DATABASE [tempdb] MODIFY FILE ( NAME = N'DDAS_log', FILENAME = N'G:\DDAS_log.ldf')

I believe tempdb to be [DDAS] here..

Also, in the CREATE DATABASE Query

IF NOT EXISTS (SELECT * FROM sys.databases WHERE name = 'DDAS')
BEGIN
    CREATE DATABASE [DDAS]
    CONTAINMENT = NONE
    ON  PRIMARY 
        ( NAME = N'DDAS', FILENAME = N'E:\DDAS.mdf' , SIZE = 1024000KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024000KB  ) 
    LOG ON      
        ( NAME = N'DDAS', FILENAME = N'G:\DDAS_log.ldf' , SIZE = 1024KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)
    END
GO

Above the logical file name of log file seems wrong, instead of LOG ON
( NAME = N'DDAS'....) it should have been NAME = N'DDAS_log'..

For more info please refer CREATE DATABASE

Not the answer you're looking for? Browse other questions tagged or ask your own question.