Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

Convering a dual MDF/NDF database to a single MDF

Posted on 2013-01-14
10
866 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

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

Suggested Solutions

Audit has been really one of the more interesting, most useful, yet difficult to maintain topics in the history of SQL Server. In earlier versions of SQL people had very few options for auditing in SQL Server. It typically meant using SQL Trace …
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 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.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

839 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