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 |
|
homebrew
Posting Yak Master
114 Posts |
 Posted - 2006-05-17 : 16:24:42
|
| I have a 175 Gig database. We're switching to new storage soon, and I'd like to spread the database over multiple files (and drives). I know I can add new files, limit File1.mdf to 175 so that it doesn't grow beyond 175 G, and then as the DB grows, File2.ndf gets the additional data.Given that the current DB has just 1 file of 175 G, is there a way to set up the DB on the new storage so that File1.mdf is 100 G, File2.ndf is 75 G with max of 100 G, and File3.ndf is 0 Gig until File 2 is full ? |
|
|
mcrowley
Aged Yak Warrior
771 Posts |
Posted - 2006-05-18 : 09:50:55
|
| You can create the database on the new storage with the two files (File1.mdf, File2.ndf), but you would then have to bulk copy or use DTS to get the data into that database. An easier way may be to restore the database to the new server in all its 175GB glory, then rebuild indexes onto a new filegroup. This will reduce the used space in the single large file, and if the indexes are on a different physical disk, you may see a performance gain, but only if you were choking on disk read performnance before. You could potentially try to shrink the 175 GB file down after you get the indexes out, but personally I would not bother, unless the size of the file is actually painful beyond being a bit ugly.As for File3.ndf, SQL Server will plant data in files based on how much space is left in them. If you supply the database with a brand new empty file, all of the data will go there, until it looks similar to the existing files, and there is nothing you can do about it. |
 |
|
|
DMcCallie
Yak Posting Veteran
62 Posts |
Posted - 2006-05-18 : 11:17:35
|
| You can create an additional .ndf and use the DBCC SHRINKFILE with the EMPTYFILE option to "move" the data around (this can be done from EM - All Tasks, Shrink Database, Files, Empty the file.) Then shrink the primary .mdf, create another .ndf and use the emptyfile option again until you get everything where you want it. It's not fun; however, this may be what you are looking for. I would suggest to create a test database (or backup/restore a SMALLER DB to test this with before trying this with a production DB... |
|
|
|
homebrew
Posting Yak Master
114 Posts |
Posted - 2006-06-08 : 15:55:00
|
| DBCC SHRINKFILE with the EMPTYFILE sounds like what I want, but it's not working. I created additional files, both in the PRIMARY filegroup and SECONDARY. I ran the command, and it completed, but there was no difference in the database. What did I miss ? |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2006-06-08 : 17:16:10
|
quote:
Originally posted by homebrew
DBCC SHRINKFILE with the EMPTYFILE sounds like what I want, but it's not working. I created additional files, both in the PRIMARY filegroup and SECONDARY. I ran the command, and it completed, but there was no difference in the database. What did I miss ?
In your original post you said your database had one file. If that is the case, how could you have a filegroup named SECONDARY?To cut down on the confusion, run this query in query analyzer in your database, and post the output results so that we can see what you really have.select [DATABASE_NAME] = db_name(), [DATABASE_FILEID] = a.fileid, [DATABASE_DB_NAME] = a.name, [DATABASE_FILENAME] = a.filename, [FILE_SIZE] = convert(int,round((a.size*1.000)/128.000,0)), [DATABASE_GROUPID] = a.groupid, [DATABASE_GROUPNAME] = isnull(b.groupname,''), [MAX_SIZE] = convert(int,round( (case a.maxsize when -1 then null else a.maxsize end*1.000)/128.000 ,0))from sysfiles a left join sysfilegroups b on ( a.groupid = b.groupid ) CODO ERGO SUM |
|
|
|
homebrew
Posting Yak Master
114 Posts |
Posted - 2006-06-09 : 02:56:32
|
| Sorry for the confusion. My LIVE database has one Primary file. I was experimenting today with a test database and added files to it. (I should have re-read my old post !)From what I've read since, I cannot 'EMPTY' the .MDF, I can only manually move tables & indexes. If I have data in a second file (.ndf), then I can use the EMPTY option to move data from file_2.ndf to file_3.ndf. (I think) |
|
|
|
|
|
|
|
|
Splitting a Database into multiple files