Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Merge data files in MSSQL 2000

Posted on 2004-11-30
8
Medium Priority
?
325 Views
Last Modified: 2008-02-01
Hello,

I have a large db (~40GB) that has been divided into four parts because diskspace was running low on the hard disks. The db is now on four different volumes :( . We now have larger hard disks :) , so is there a way to merge all four data files to one data file?

Best Regards,
Benny
0
Comment
Question by:strobe0
  • 4
  • 4
8 Comments
 
LVL 18

Expert Comment

by:ShogunWade
ID: 12707312
When you say it was divided in four parts, do you mean into 4 databases or do you mean 1 database with four data files ?
0
 

Author Comment

by:strobe0
ID: 12707345
1 database with four data files.
0
 
LVL 18

Accepted Solution

by:
ShogunWade earned 500 total points
ID: 12707412
ok,  then the easiest thing is as follows :

1) increase the primary file size to a capacity to a little bit more than required to store all the data.
2) make sure that the other files are not set to autogrow
3) for each of the files, right click in EM the database and click properties,  goto the data files tab and delete  the secondary files.   the data will be moved to the primary file.
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:strobe0
ID: 12707876
Thanks a lot, do you know where I can found some documentation about this procedure?
0
 
LVL 18

Expert Comment

by:ShogunWade
ID: 12707901
I've never seen any published about it.   but i had a similar problem some time ago and this was the only way i could find to do it.
0
 

Author Comment

by:strobe0
ID: 12720255
When I try to remove the other data files the following error message occurs:
Error 5042: The file 'test_Data_two' cannot be removed because it is not empty.
What am I doing wrong?
0
 
LVL 18

Expert Comment

by:ShogunWade
ID: 12724839
ah sorry missed one vital step.    

2a)  goto Shrink Database click files, selectthe file you want to get rid of and then click the option to empty this into other files.

this will make the file empty and then deleted it as described prviously
0
 

Author Comment

by:strobe0
ID: 12727887
Thanks again! That was exactly what I needed!
Can this also be executed on transaction log files or can it only be used on data files?
0

Featured Post

NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

Question has a verified solution.

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

One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
This shares a stored procedure to retrieve permissions for a given user on the current database or across all databases on a server.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Suggested Courses

810 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