Solved

Convering a dual MDF/NDF database to a single MDF

Posted on 2013-01-14
10
821 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
 

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
Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

 

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:
ScottPletcher 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:ScottPletcher
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

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

746 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

13 Experts available now in Live!

Get 1:1 Help Now