November 17, 2011 at 1:36 pm
We are going to migrate a SQL 2000 database to SQL 2008. There is a file used by this database that is too large to fit on the luns used on SQL 2008. Before migrating we need to split it up into two (or more) files .
I was told that adding an empty new file to the primary file group and later runnning DBCC REINDEX would evenly distribute the data accross all the files. IS this correct?
November 17, 2011 at 1:56 pm
Keep. In mind that only makes. Sense for tables having a clustering index and for non-clustering indexes.( reindex)
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
November 17, 2011 at 3:41 pm
I was also thinking that this might help DBCC SHRINKFILE ('file a', EMPTY FILE ).
But if I add 2 new files(file b,c) and then use this DBCC command, how do I make sure that it the data is going to migrate from file a to file b first and when when file b fills up, it should fill up file c?
November 18, 2011 at 12:12 am
You'll have to shink the current file(s) because, as you state, you didn't configure your luns big enough to host that size. ( Which is IMHO awkward )
Check you current file and current data size.
I would try full db maintenance on the current db, then see how much data size is actually in use and how much is free in the current file.
If then a shrink can help to migrate your current db, there is no need to add the extra file.
( no need at this time and for this purpose )
Normally, your sanadmins can easily enlarge a lun ! Just ask them to enlarge it to the size you need ( + some extra of course ! )
After migration, I would perform full db maintenance once more, to be sure your new environment starts with the most optimal object organisation.
(Keep in mind, you db file size may grow during this operation ! especially after you have performed a shrink operation !!)
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply