none
How can we separate the MDF file with the ldf and mdf file from a single mdf file ?

    Question

  • I have a query on the MDF files . We have mistakenly created a DB with mdf and ldf with the same file name as examp.mdf and examp.mdf , By this as mdf and ldf locations are pointed same file the mdf file is huge in space, How can we separate the mdf file to mdf and ldf files now , we are using sql server 2005 version.Please suggest me on this
    Ravishankar Maduri MCTS,MCPD,MCP
    Sunday, December 04, 2011 1:44 PM

Answers

  • Having two files with the same name is not legal in SQL. It will fail.

    CREATE DATABASE [test2] ON PRIMARY
    ( NAME = N'test2', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10.SQL2008\MSSQL\DATA\test2.mdf' , SIZE = 2304KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
    LOG ON
    ( NAME = N'test2_log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10.SQL2008\MSSQL\DATA\test2.mdf' , SIZE = 576KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)
    GO
    Error:

    Msg 5170, Level 16, State 1, Line 2

    Cannot create file 'C:\Program Files\Microsoft SQL Server\MSSQL10.SQL2008\MSSQL\DATA\test2.mdf' because it already exists. Change the file path or the file name, and retry the operation.

    Msg 1802, Level 16, State 4, Line 2

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

     
     
     

    Didn't get enough help here? Submit a case with the Microsoft Customer Support team for deeper investigation - http://support.microsoft.com/select/default.aspx?target=assistance
    Tuesday, December 06, 2011 5:59 AM
    Moderator

All replies

  • Hi,

    Can you post the output of this query please?

    select * from sys.database_files
    

    It may help to understand your question.

    Thanks,

    Janos


    There are 10 type of people. Those who understand binary and those who do not.
    My Blog
    Monday, December 05, 2011 11:17 PM
  • Having two files with the same name is not legal in SQL. It will fail.

    CREATE DATABASE [test2] ON PRIMARY
    ( NAME = N'test2', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10.SQL2008\MSSQL\DATA\test2.mdf' , SIZE = 2304KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
    LOG ON
    ( NAME = N'test2_log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10.SQL2008\MSSQL\DATA\test2.mdf' , SIZE = 576KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)
    GO
    Error:

    Msg 5170, Level 16, State 1, Line 2

    Cannot create file 'C:\Program Files\Microsoft SQL Server\MSSQL10.SQL2008\MSSQL\DATA\test2.mdf' because it already exists. Change the file path or the file name, and retry the operation.

    Msg 1802, Level 16, State 4, Line 2

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

     
     
     

    Didn't get enough help here? Submit a case with the Microsoft Customer Support team for deeper investigation - http://support.microsoft.com/select/default.aspx?target=assistance
    Tuesday, December 06, 2011 5:59 AM
    Moderator
  • Ravi,

    You can't do that. Neither system nor SQL Server will allow to create same files located at same place. Yes, your log and data files both can have same extension and same name if they are in different locations on the system. Try to locate these two files.

    Thursday, September 13, 2012 1:38 PM