Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Convering a dual MDF/NDF database to a single MDF

Posted on 2013-01-14
10
Medium Priority
?
1,009 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
[X]
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
  • 4
  • 4
  • 2
10 Comments
 
LVL 23

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 23

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
Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

 

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 23

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 23

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 70

Accepted Solution

by:
Scott Pletcher earned 2000 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 70

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

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

Question has a verified solution.

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

     When we have to pass multiple rows of data to SQL Server, the developers either have to send one row at a time or come up with other workarounds to meet requirements like using XML to pass data, which is complex and tedious to use. There is a …
Hi all, It is important and often overlooked to understand “Database properties”. Often we see questions about "log files" or "where is the database" and one of the easiest ways to get general information about your database is to use “Database p…
This course is ideal for IT System Administrators working with VMware vSphere and its associated products in their company infrastructure. This course teaches you how to install and maintain this virtualization technology to store data, prevent vuln…
Are you ready to place your question in front of subject-matter experts for more timely responses? With the release of Priority Question, Premium Members, Team Accounts and Qualified Experts can now identify the emergent level of their issue, signal…

636 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