Managing MS SQL Server mdf files

Posted on 2006-05-15
Last Modified: 2013-12-03

I have a database that is currently using two data files and one log file. I need to be able to utilize 1 data file and 1 log file.

When I go to properties, and then choose Data Files I see that there are two seperate files in two different file groups. The structure currently looks like this:

DataFile1= P:\Path\Dbname.mdf
space allocated 11 MB
File Group = PRIMARY

DataFile2= P:\Path\Dbname_MISC_DATA.mdf
space allocated = 2000 MB
File Group = MISC_DATA

I'm relatively new to MS SQL. I'm currently under the belief that if I simply delete the MISC_DATA file and reallocate my space over to the PRIMARY data file, I will cause some problems. I'm also thinking that there may be a way in QA to run a sql script that would 'merge' this data into my PRIMARY file, re-size the space that's allocated and THEN it would be safe to remove the MISC_DATA.mdf.  Any assistance consolidating these files is greatly appreciated !!!
Question by:romieb69
    LVL 27

    Expert Comment

    >> if I simply delete the MISC_DATA file and reallocate my space over to the PRIMARY data file, I will cause some problems.
    Yes, that is not a good choice.

    One option would be to create a duplicate database (dbName2) with the same structure (but only using one file) - copy the data from dbname into dbname2. Verify that everything is there. Delete dbname and rename dbname2 to dbname.

    It depends on how much free space is available.

    Author Comment

    That sounds like a reasonable solution. Diskspace is relatively limited but I might be able to free up some realestate. After copying the database structure over to dbName2... is it at that point that I remove the MISC_DATA mdf and then resize my primary mdf?  Then copy over the data?
    LVL 27

    Accepted Solution


    In this scenario - create dbname2 with the same data structure as dbname. When you create dbName2 it has the single .mdf that you want.

    If you were creating from different servers you could use the COPY DATABASE wizard in EM (Tools:Wizards:Managment). It will not work if the source and destination are the same server.

    Copy the data from dbname to dbname2 (you could use DTS for this).
    Script out the various database objects - views/stored procs/function/users/ etc and run them on dbname2.

    You open various tables and verify the data.
    Verify that all of the stored procs/functions/users/and other database objects are copied to  dbName2.

    When you are satisfied that everything is correct make a full backup of dbname (just in case) and then delete it.

    Then rename the database from dbname2 to dbname

    You do not need to resize or merge the dbname mdf files because you will delete them when you delete dbname.

    EXEC sp_dboption 'dbname2', 'Single User', 'TRUE'
    EXEC sp_renamedb 'dbName2', 'dbname'
    EXEC sp_dboption 'dbname', 'Single User', 'FALSE'
    LVL 50

    Expert Comment

    >I'm relatively new to MS SQL.

    why do you feel you need to consolidate to 1 db file and 1 log file?

    do you understand why it was created like this originally?


    Author Comment

    Because a backup of this db is delivered to a client each day though an automated process (DTS). They have conveniently let me know last minute that having 2 data files and one log file is a problem for their automated import procedures.  Whatever the reasons for creating two data files is a moot point now.
    LVL 28

    Expert Comment

    hi romieb69,

    Consider creating new database say database2  
    now generate script from your existing database which will include table, views, fk, pk , index everything,

    replace [MISC_DATA] in the script with [primary]

    run this script on database2
     incase you have any fk on your database2  run

    declare @TableName varchar(100)

    declare TablesList cursor for select name from sysobjects where xtype = 'U'

    open TablesList

    fetch next from TablesList into @TableName

    while @@fetch_status = 0
          exec ('alter table [' + @TableName + '] nocheck constraint all')
          fetch next from TablesList into @TableName

    close TablesList
    deallocate TablesList

    use dts to move all data in database2

    again run the above script by changing 'nocheck' with 'check'

    drop your old database

    take backup of your databse2

    restore it as your primary database
    and finally delete database2

    Author Comment

    A duplicate of the database was very helpful... I found other steps to be necessary...
    Alter Database:

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    IT, Stop Being Called Into Every Meeting

    Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

    Suggested Solutions

    Title # Comments Views Activity
    Help with SQL joins 9 31
    C# primary key 9 46
    SQL Server 2012 express 24 21
    Case statement to sum values - T-SQL 3 28
    Introduction In my previous article ( I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
    Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
    Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
    Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed

    779 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    18 Experts available now in Live!

    Get 1:1 Help Now