reorg database files

I want to reorganize the data files for optimum
performance. I have described below the existing
and the intended scenario that I wish to attain.
** existing database scenario **
database files
mydb.mdf (50gb) - primary data file
mydblog.ldf (1gb) - log file
** intended database scenario **
database files:
mydb1.mdf (10gb) - primary data file
mydb2.ndf (10gb) - secondary data file
mydb3.ndf (10gb) - secondary data file
mydb4.ndf (10gb) - secondary data file
mydb5.ndf (10gb) - secondary data file
mydblog.ldf (1gb) - log file
Are there tools that can me help do this?
Thank you in advance.Using T-SQL tools, you can do this:
Use ALTER DATABASE to create new files and place them in filegroups.
Now, use sp_spaceused to determine which tables and/or indexes you want to
move to the new filegroups.
You can use ALTER TABLE with ON FileGroupName to move data around. Notice
that the filegroup is actually home to an index, but a clustered index is
the table, of course. From the BOL:
ON {filegroup | DEFAULT}
Specifies the storage location of the index created for the constraint. If
filegroup is specified, the index is created in the named filegroup. If
DEFAULT is specified, the index is created in the default filegroup. If ON
is not specified, the index is created in the filegroup that contains the
table. If ON is specified when adding a clustered index for a PRIMARY KEY or
UNIQUE constraint, the entire table is moved to the specified filegroup when
the clustered index is created.
Once you have successfully moved tables by recreating the clustered indexes,
you can use DBCC SHRINKFILE to shrink the original large file down to the
appropriate size.
Russell Fields
"fragb" <anonymous@.discussions.microsoft.com> wrote in message
news:0ca401c47b0c$fb6e7280$a401280a@.phx.gbl...
> I want to reorganize the data files for optimum
> performance. I have described below the existing
> and the intended scenario that I wish to attain.
> ** existing database scenario **
> database files
> mydb.mdf (50gb) - primary data file
> mydblog.ldf (1gb) - log file
> ** intended database scenario **
> database files:
> mydb1.mdf (10gb) - primary data file
> mydb2.ndf (10gb) - secondary data file
> mydb3.ndf (10gb) - secondary data file
> mydb4.ndf (10gb) - secondary data file
> mydb5.ndf (10gb) - secondary data file
> mydblog.ldf (1gb) - log file
> Are there tools that can me help do this?
> Thank you in advance.
>|||None that I know of that would do much in that situation. If you want to
spread your data evenly across a filegroup with multiple files from one that
has a single file you pretty much have to export all the data. Then
truncate all the tables and reimport it back again. It's not that difficult
of a task but obviously you will need to take your users off line for some
period of time. In the past when I have done this I basically scripted out
the database and all the objects in such a way that I could recreate the
database schema with the new files and all the tables , sp's UDF's ect but
leaving off the triggers, RI and Indexes. Then after you BCP out all the
data you can drop the DB and recreate it without those to make it easier and
faster to load. Then Bulk Insert the data and add back the RI, Triggers
etc. Just make sure you have good and tested backups first.
Andrew J. Kelly SQL MVP
"fragb" <anonymous@.discussions.microsoft.com> wrote in message
news:0ca401c47b0c$fb6e7280$a401280a@.phx.gbl...
> I want to reorganize the data files for optimum
> performance. I have described below the existing
> and the intended scenario that I wish to attain.
> ** existing database scenario **
> database files
> mydb.mdf (50gb) - primary data file
> mydblog.ldf (1gb) - log file
> ** intended database scenario **
> database files:
> mydb1.mdf (10gb) - primary data file
> mydb2.ndf (10gb) - secondary data file
> mydb3.ndf (10gb) - secondary data file
> mydb4.ndf (10gb) - secondary data file
> mydb5.ndf (10gb) - secondary data file
> mydblog.ldf (1gb) - log file
> Are there tools that can me help do this?
> Thank you in advance.
>|||Hi,
The CREATE TABLE & ALTER TABLE statements only allow you to specify the
FILEGROUP on which you wish to create the table. Not the actual data
file within the Filegroup.
In your intended scenario, you do not draw a distinction between Data
Files, and FileGroups. A possible alternative is :
FileGroupPRIMARY mydb1.mdf (10gb)
FileGroup02 mydb2.ndf (10gb)
FileGroup03 mydb3.ndf (10gb)
FileGroup04 mydb4.ndf (10gb)
FileGroup05 mydb5.ndf (10gb)
mydblog.ldf (1gb)
I use Power Designer - Data Architect to model my databases. After I
make changes to the model (ie, change the FileGroup for a table), Data
Architect compares my Model against the database on the server, and
generates a "Modify" script, which I then run against the database.
It generates the typical script to move to a different Filegroup :
alter table dbo.tbl_Customer
drop constraint PK_Customer
go
alter table dbo.tbl_Customer
add constraint PK_Customer primary key clustered (CustomerId)
on "NEW_FILEGROUP"
go
thanks
Ian
fragb wrote:
> I want to reorganize the data files for optimum
> performance. I have described below the existing
> and the intended scenario that I wish to attain.
> ** existing database scenario **
> database files
> mydb.mdf (50gb) - primary data file
> mydblog.ldf (1gb) - log file
> ** intended database scenario **
> database files:
> mydb1.mdf (10gb) - primary data file
> mydb2.ndf (10gb) - secondary data file
> mydb3.ndf (10gb) - secondary data file
> mydb4.ndf (10gb) - secondary data file
> mydb5.ndf (10gb) - secondary data file
> mydblog.ldf (1gb) - log file
> Are there tools that can me help do this?
> Thank you in advance.
>