Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1061
  • Last Modified:

Convering a dual MDF/NDF database to a single MDF

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
atorex
Asked:
atorex
  • 4
  • 4
  • 2
1 Solution
 
Steve WalesSenior Database AdministratorCommented:
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
 
atorexAuthor Commented:
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
 
Steve WalesSenior Database AdministratorCommented:
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.

 
atorexAuthor Commented:
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
 
Steve WalesSenior Database AdministratorCommented:
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
 
atorexAuthor Commented:
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
 
Steve WalesSenior Database AdministratorCommented:
Can you post a small section of the output with the extra columns I mentioned ?
0
 
atorexAuthor Commented:
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
 
Scott PletcherSenior DBACommented:
>> 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
 
Scott PletcherSenior DBACommented:
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

Become an Android App Developer

Ready to kick start your career in 2018? Learn how to build an Android app in January’s Course of the Month and open the door to new opportunities.

  • 4
  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now