Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
ColdFusion
Starting Member
3 Posts |
 Posted - 2007-11-06 : 08:51:55
|
al file groups into single file group and single file.Hello, folks!I have been reading this forum for several years (I think about 5 or so) constantly finding answers to all of my questions. So Thank You guys for all the time you spend here sharing your knowledge.However, looks like it is time to ask for some help/advice.Here is the situation.We (company I work for/client of the company I work for) had a (sort of) web site which created very heavy load to the database (small web interface + heavy use of web services, "I want a blah quote" application. Dealing with performance and space allocation problems I've splitted data into several DB files (and did some other thing not important for my current problem). That helped somehow for some time. Eventually problem has been solved by migrating DB to MSSQL9 and by complete rebuild of existing web application. So old "not good" application/DB was left and no longer in active use, however client time to time goes to the old app (1-2 times per month) to extract some data for analysis.Here is the problem.1. I want to join all of that data into a single data file. (the thing I don't know how to do)2. To free up hard drive by moving this database to external usb drive. I know! I know that it must NOT be done under any normal circumstances. However, this database is literally read only and there is mostly no use of it, so performance is no longer a subject of consideration. But it will help me to free up valuable space on hard drive array.So, bottom line.Would you be so kind to show me direction to dig in, which will help me to resolve problem #1?  Thank you!Alex |
|
|
kap_gemini
Starting Member
5 Posts |
Posted - 2007-11-06 : 10:54:08
|
| Alex,First of all you need to move the data out of the file to another file.You can use dbcc shrinkfile with emptyfile option.(it will Migrates all data from the specified file to other files in the same filegroup)After executing the DBCC SHRINKFILE with the EMPTYFILE option, use the ALTER DATABASE statement with REMOVE FILE to get rid of the file that you no longer need.Thanks KapilKapil Gupta |
 |
|
|
ColdFusion
Starting Member
3 Posts |
Posted - 2007-11-06 : 11:53:36
|
| Hello Kapil!It isn't a problem to move data from/to different files within a file group. But how to move data/file from one file group to another?Thank you!Alex |
|
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2007-11-06 : 22:11:44
|
| You can move table to another file group by creating clustered index in new file group, but you can't move file around. |
|
|
|
|
|
|
|
|
Migration of several DB files spreaded among sever