Solved

Convering a dual MDF/NDF database to a single MDF

Posted on 2013-01-14
10
845 Views
1 Endorsement
Last Modified: 2013-01-18
I have taken over an SQL server housting a database for a third party application, we are lookingv to move the database from the old server to a new server with more hosrsepower.
The database has a split MDF/NDF and the application vendor wants it set back to the application default of a single MDF, with that said what would be the best and safest process to convert the data files to a single primary file?
1
Comment
Question by:atorex
  • 4
  • 4
  • 2
10 Comments
 
LVL 22

Expert Comment

by:Steve Wales
ID: 38775174
Did a little digging on this this morning.

Have a look at this query to identify which objects are in the NDF:

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=149687

Then, using the Method 1 tip from this blog:

http://blogs.msdn.com/b/sqlserverfaq/archive/2011/08/02/moving-data-from-the-mdf-file-to-the-ndf-file-s.aspx

You can recreate clustered indexes in the objects in the new file group.

Another article here on moving objects between file groups with several different options explained:

http://www.mssqltips.com/sqlservertip/1112/filegroups-in-sql-server-2005/

Keep running the first query until everything is moved.

Of course, make sure you do this in test first and when it comes time to do it in prod that you give yourself a sufficient outage window!
0
 

Author Comment

by:atorex
ID: 38782518
Using the above links I have found that the NDF only contains indexes however they are duplicate indexes from the MDF.
how can I resolve this, this database is growing and any attempt to purge takes a massive amount of time, and its I/O is off the charts. Can I delete or detach this file all the indexes also exist in the MDF?
0
 
LVL 22

Expert Comment

by:Steve Wales
ID: 38782578
An index only exists in one place.  If it's in the NDF, it's not in the MDF.

You also need to understand that the Clustered Index is effectively the table since it is the physical ordering of the data in the table.

To the best of my knowledge, you can't just detach a single file (and even then you wouldn't want to do it even if you could because then you have objects defined in the database that just suddenly don't exist any more).

Short of recreating the indexes as per the last link in the original answer, I don't know that there is another solution.

I'm curious though, if your system is I/O bound, why the vendor would want everything back into a single MDF file - since be spreading the load over multiple files (assuming they are on separate devices or RAID Arrays) is one possible way to improve your I/O and lower the load on the MDF.
0
Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

 

Author Comment

by:atorex
ID: 38782604
here is a clip of the query result, I may have miss communicated it but as you can see the same data exists on the MDF and NDF

CostPriceItems      PRIMARY      D:\MSSQL\Data\TM_TM_30625.mdf
CostPriceItems      PRIMARY      D:\MSSQL\Data\TM_TM_Data2_30625.NDF
ProductPriceItems      PRIMARY      D:\MSSQL\Data\TM_TM_30625.mdf
ProductPriceItems      PRIMARY      D:\MSSQL\Data\TM_TM_Data2_30625.NDF
SupplierProductcostpriceitems      PRIMARY      D:\MSSQL\Data\TM_TM_30625.mdf
SupplierProductcostpriceitems      PRIMARY      D:\MSSQL\Data\TM_TM_Data2_30625.NDF
ProductLoyaltyItems      PRIMARY      D:\MSSQL\Data\TM_TM_30625.mdf
ProductLoyaltyItems      PRIMARY      D:\MSSQL\Data\TM_TM_Data2_30625.NDF
ProductTradingRegions      PRIMARY      D:\MSSQL\Data\TM_TM_30625.mdf
ProductTradingRegions      PRIMARY      D:\MSSQL\Data\TM_TM_Data2_30625.NDF
ProductPriceCheckHistories      PRIMARY      D:\MSSQL\Data\TM_TM_30625.mdf
ProductPriceCheckHistories      PRIMARY      D:\MSSQL\Data\TM_TM_Data2_30625.NDF
ReplenishmentRunItems      PRIMARY      D:\MSSQL\Data\TM_TM_30625.mdf
ReplenishmentRunItems      PRIMARY      D:\MSSQL\Data\TM_TM_Data2_30625.NDF
ChainOfCustodies      PRIMARY      D:\MSSQL\Data\TM_TM_30625.mdf
ChainOfCustodies      PRIMARY      D:\MSSQL\Data\TM_TM_Data2_30625.NDF

this is the query I ran to get the above result.

SELECT      tbl.name [table], fg.name [filegroup], sf.filename
FROM sys.tables AS tbl
INNER JOIN sys.indexes AS idx ON idx.object_id = tbl.object_id and idx.index_id < 2
INNER JOIN sys.partitions AS p ON p.object_id=CAST(tbl.object_id AS int) AND p.index_id=idx.index_id
INNER JOIN sys.indexes AS indx ON p.object_id = indx.object_id and p.index_id = indx.index_id
LEFT OUTER JOIN sys.destination_data_spaces AS dds ON dds.partition_scheme_id = indx.data_space_id and dds.destination_id = p.partition_number
LEFT OUTER JOIN sys.partition_schemes AS ps ON ps.data_space_id = indx.data_space_id
LEFT OUTER JOIN sys.partition_range_values AS prv ON prv.boundary_id = p.partition_number and prv.function_id = ps.function_id
LEFT OUTER JOIN sys.filegroups AS fg ON  fg.data_space_id = dds.data_space_id or fg.data_space_id = indx.data_space_id
LEFT OUTER JOIN sys.partition_functions AS pf ON  pf.function_id = prv.function_id
LEFT OUTER JOIN sys.sysfilegroups sfg on sfg.groupname = fg.name
LEFT OUTER JOIN sysfiles sf on sf.groupid = sfg.groupid
0
 
LVL 22

Expert Comment

by:Steve Wales
ID: 38782793
You are not showing the index names on the about query.  You're showing only the table name.  Add idx.name to the output of that query and probably even idx.type_desc to show if it's clustered or not.

Also, why limit the first join by idx.index_id < 2 ?  That will pick up Heaps and clustered indexes but not any of the non clustered indexes.
0
 

Author Comment

by:atorex
ID: 38782819
Ok, so the result is tables but forgive my ignorance!!!! why would the tables show on both files. We have 319 tables on this database and that query result listed 638 2 for each table one on each file, am I missing something or should it be a group of tables in one file and another group in the second file?
0
 
LVL 22

Expert Comment

by:Steve Wales
ID: 38782897
Can you post a small section of the output with the extra columns I mentioned ?
0
 

Author Comment

by:atorex
ID: 38782924
Here we go

CostPriceItems      PRIMARY      D:\MSSQL\Data\TM_TM_30625.mdf      HEAP      NULL
CostPriceItems      PRIMARY      D:\MSSQL\Data\TM_TM_Data2_30625.NDF      HEAP      NULL
ProductPriceItems      PRIMARY      D:\MSSQL\Data\TM_TM_30625.mdf      HEAP      NULL
ProductPriceItems      PRIMARY      D:\MSSQL\Data\TM_TM_Data2_30625.NDF      HEAP      NULL
SupplierProductcostpriceitems      PRIMARY      D:\MSSQL\Data\TM_TM_30625.mdf      HEAP      NULL
SupplierProductcostpriceitems      PRIMARY      D:\MSSQL\Data\TM_TM_Data2_30625.NDF      HEAP      NULL
ProductLoyaltyItems      PRIMARY      D:\MSSQL\Data\TM_TM_30625.mdf      HEAP      NULL
ProductLoyaltyItems      PRIMARY      D:\MSSQL\Data\TM_TM_Data2_30625.NDF      HEAP      NULL
ProductTradingRegions      PRIMARY      D:\MSSQL\Data\TM_TM_30625.mdf      HEAP      NULL
ProductTradingRegions      PRIMARY      D:\MSSQL\Data\TM_TM_Data2_30625.NDF      HEAP      NULL
ProductPriceCheckHistories      PRIMARY      D:\MSSQL\Data\TM_TM_30625.mdf      HEAP      NULL
ProductPriceCheckHistories      PRIMARY      D:\MSSQL\Data\TM_TM_Data2_30625.NDF      HEAP      NULL
ReplenishmentRunItems      PRIMARY      D:\MSSQL\Data\TM_TM_30625.mdf      HEAP      NULL
ReplenishmentRunItems      PRIMARY      D:\MSSQL\Data\TM_TM_Data2_30625.NDF      HEAP      NULL
ChainOfCustodies      PRIMARY      D:\MSSQL\Data\TM_TM_30625.mdf      HEAP      NULL
ChainOfCustodies      PRIMARY      D:\MSSQL\Data\TM_TM_Data2_30625.NDF      HEAP      NULL
FiscalStamps      PRIMARY      D:\MSSQL\Data\TM_TM_30625.mdf      HEAP      NULL
FiscalStamps      PRIMARY      D:\MSSQL\Data\TM_TM_Data2_30625.NDF      HEAP      NULL
0
 
LVL 69

Accepted Solution

by:
Scott Pletcher earned 500 total points
ID: 38783000
>> am I missing something or should it be a group of tables in one file and another group in the second file? <<

You're missing something.

You assign a table, index or other stored object to a filegroup, not to an individual file.  SQL automatically spreads the data across all files in that filegroup.  By default, SQL tries to roughly "proportionately" fill each file.  That is, the first new data to file 1, the next new data to file 2, the next new data to file 1, the next new data to file 2, etc..

Since the PRIMARY filegroup has two files:
D:\MSSQL\Data\TM_TM_30625.mdf
D:\MSSQL\Data\TM_TM_Data2_30625.NDF

some data from each table & index in the PRIMARY filegroup gets written to each file.
0
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 38793555
I think you should be able to remove one file of a file group by using the appropriate SQL commands:


USE <db_name>

EXEC sp_helpfile
-- copy / write down / remember the logical file name (first column)
-- of the file to be removed


-- fill that logical name into the commands below

DBCC SHRINKFILE ( <logical_file_name_to_be_removed>, EMPTYFILE )

ALTER DATABASE <db_name>
REMOVE FILE <logical_file_name_to_be_removed>
0

Featured Post

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
MS SQL Inner Join - Multiple Join Parameters 2 34
Ssis not sending failure message 2 26
SQL Help 27 45
How to search for strings inside db views 4 28
In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
This Micro Tutorial will give you a basic overview how to record your screen with Microsoft Expression Encoder. This program is still free and open for the public to download. This will be demonstrated using Microsoft Expression Encoder 4.
This video shows how to use Hyena, from SystemTools Software, to bulk import 100 user accounts from an external text file. View in 1080p for best video quality.

803 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