September 22, 2008 at 3:36 pm
Hi all,
I have a database of 300GB with following configuration SAN configuration:
mdf on W drive (290GB, updatable)
ndf on X drive (read only tables, 10GB)
ldf on Y drive (100GB)
To do:
We have decided to move this database on to three new logical dirves each at 130GB and get rid of W drive.
S: 130GB
T: 130GB
U: 130GB
How do I go about doing that. it is not as simple as emptying the W drive into S,T U drives.
Are there any options with restore statement. That is restoring the database to new database
with multiple files.
I thought to check with gurus before I use my last resort, which is creating a new database on three new drives and move tables to this database by creating the clustered index onto the filegorup, once all tables are moved. I can offline the old database on W drive.
Thanks!
sqldba
September 22, 2008 at 3:52 pm
There's no option to split a DB as part of a restore.
You'll have to create two new files, place them into two new filegroups and then move tables over into the new filegroups by rebuilding the cluster on to the new filegroup. Once you've got the mdf down small enough, then you can detach the DB, move the mdf and then reattach the database.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 23, 2008 at 11:32 am
Hi Gail,
Thanks for the response, It does help. I wanted to make sure that I have no other option but to move tables around via clustered index.
sqldba
September 23, 2008 at 11:46 am
One other thought,
Instead of creating two new file groups, Can I add two files to the same primary filegroup point to new disks, then use dbcc shrinkfile(filename, empty) into these new files. I know we cannot control which data goes where as SQL server will use proportional fill algorithm. But as a next step we can detach the ornginal file and move it to new drive, that way database will span over one logical filegroup and can take advantage of SAN technology with parallel disk usage.
Has anyone use this technique?
If I take the apporach of moving tables (with clustered index) to different filegroup, it will still be hit by same users with queries causing the slow performance, but if the table is split across multiple disks,the queries will hit different disks for data.
Regards!
SQLDBA
September 23, 2008 at 12:14 pm
sqldba (9/23/2008)
One other thought,Instead of creating two new file groups, Can I add two files to the same primary filegroup point to new disks, then use dbcc shrinkfile(filename, empty) into these new files.
You can try. I'm not sure if it's possible to empty the primary file, since after you do a shrinkfile with the empty option, no more data will be allocated to that file (as per BoL)
EMPTYFILE
Migrates all data from the specified file to other files in the same filegroup. Because the Database Engine no longer allows data to be placed in the empty file, the file can be removed by using the ALTER DATABASE statement.
If I take the apporach of moving tables (with clustered index) to different filegroup, it will still be hit by same users with queries causing the slow performance, but if the table is split across multiple disks,the queries will hit different disks for data.
Generally, unless your system is seriously disk bottlenecked, the placement of files on disk and tables within those files does not have a huge effect on performance
If there are multiple queries running against a table, it is likely to be in memory and won't have to be fetched from disk.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply