October 7, 2008 at 10:50 am
I have a 350 GB DB with one data file currently. We are moving it to a new SQL Server instance on new hardware and I would like to split the data file up into four different data files.
Is there any way to take:
stuff.mdf
stuff.ldf
and restore it to:
stuff1.mdf
stuff2.ndf
stuff3.ndf
stuff4.ndf
stuff.ldf
and have the database spread itself across the three new secondary data files?
I'm not asking how to create secondary data files, they are already created and waiting. I'm asking how, or if it's possible, to restore a single data file to multiple data files.
I tried this (STUFF_data is the original name):
RESTORE DATABASE STUFF FROM DISK = 'G:\MSSQL\STUFF_backup.bak' WITH REPLACE,
move 'STUFF_data' to 'E:\mssql\data\STUFF.mdf',
move 'STUFF_data' to 'E:\mssql\data\STUFF2.ndf',
move 'STUFF_data' to 'K:\mssql\data\STUFF3.ndf',
move 'STUFF_data' to 'K:\mssql\data\STUFF4.ndf',
move 'STUFF_log' to 'F:\mssql\logs\STUFF_log.ldf'
go
Of course, all it does is move the complete data file from STUFF_data into 'K:\mssql\data\STUFF4.ndf' and the other three data files aren't created at all, much less having the data spread amongst them.
Is there a way or am I just wasting my time? Other suggestions to accomplish the same goal??? Let me know and thank you in advance for your time.
October 7, 2008 at 2:07 pm
you would have to restore
stuff.mdf
stuff.ldf
then create the extra filegroups\files and move objects from the primary file\group to the new files\groups
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
October 8, 2008 at 7:35 am
That's what I sadly figured. Thank you for your reply.
If anyone else has done anything different, please let me know!
Scarab
October 8, 2008 at 8:10 am
Concur with the above.
Rebuilding the clustered index will move the data to a new filegroup if you specify it.
October 8, 2008 at 8:12 am
So, the secondary data files don't necessarily need to be created the same size (i.e. TEMPDB) because it's not going to write to them evenly anyway because they will have different database object in them.
Is this a correct statement?
October 10, 2008 at 5:30 pm
that would be a correct statement. The size of your secondary files are dictated by the size of the objec ts you are moving to that filegroup.
October 13, 2008 at 7:23 am
Can you give me information on performing an Empty File/Shrink File on the existing primary data file in order for it to move those objects equally into the new secondary files? Anyone ever done this technique?
October 13, 2008 at 8:13 am
You can't do a shink file with empty on the primary file.
Are you looking to create multiple filegroups (in which case it's easy to move objects and the tables will always stay within the assigned filegroup) or multiple files within one filegroup (in which case SQL's proportional fill will add data to any one of the files, depending on which have more free space)
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
October 13, 2008 at 8:17 am
You wouldn't use shrinkfile to move data. It reclaims physical file space for the OS, does nothing to effect the data.
The problem is that unless you add data equally to the filegroups, this won't help. Quite a few people from the storage engine have shown that for user databases, you don't get better performance from multiple files. You might do this to archive data or for better DR recovery, but it's not for performance.
The data also won't write evenly across files, it writes to the objects.
Can you explain why you think you need to balance the data across files?
October 13, 2008 at 8:31 am
To answer the first question, the vendor will not allow me to move individual objects across the new files, and yes, those secondary data files would be on the same file group.
It is suppose to (according to the vendor) offer potential performance gains. Having never worked with multiple data files I'm not versed in the do's and don'ts or if it's worth it.
As it's setup now, there are two LUNS, each with dedicated disks. There would be two files on each LUN.
But it sounds as though you have to physically move specific objects around in order to get any benefit. Are you sure you can't perform a shrink/empty on a primary file group to equally distribute those objects throughout the other secondary files?
October 13, 2008 at 8:38 am
Splitting data into multiple files doesn't usually give much or any of a performance benefit, unless you're having IO performance issues. Are you encountering IO bottlenecks? Splitting into multiple files on the same drive/lun will gain you nothing.
What's your drive layout look like? Where's the data file, where's the log file? Where's TempDB?
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
October 13, 2008 at 8:44 am
The whole thing is on a SAN System
System DBs are on their own LUN with dedicated disks
TempDB is on it's own LUN with dedicated w/ 8 files
Log Files are on their own LUN with dedicated disks
Backups are written to their own LUN with dedicated disks
User DB in question will have two dedicated LUNS, let's say E: and F:
E: LUN
Dedicated 10 physical drives
File #1 *.mdf - Currently all 363 GB resides here.
File #2 *.ndf
F: LUN
Dedicated 10 physical drives
File #3 *.ndf
File #4 *.ndf
October 13, 2008 at 9:03 am
Looking at that, unless you're seeing IO bottlenecks (are you?) I doubt you'll see much if any improvement from the split. If you decide to split, then just add a second file on the other LUN.
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
October 13, 2008 at 9:06 am
No, not really any I/O bottlenecks.
So, it's perfectly OK to eventually have a 1 TB single data file?
October 13, 2008 at 9:31 am
It is as long as your DR system supports that as well. If not, I'd split only as needed for DR.
Performance wise, no reason a 1TB or a 10TB file should causes issues. You could have hot spots in there, in which case you might want to get faster disks, either for the whole file (more spindles) or separate this out to get less contention.
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply