I am having trouble moving BLOBs (text fields).
I have a 200Gb database in a single data file and want to split it into 4
even sized files (I have my reasons). To achieve this I plan on adding 3 data
files to the Primary filegroup. From there I will issue a DBCC dbreindex on
all my tables, which effectively recreates the index (or table if its a
clustered index) across all datafiles and I get my even distribution.
My plan comes unstuck when I encounter text fields and other BLOBs (as the
clustered index just contains pointers doesnt it'). How can I move them? Do
I have to do a select into or bcp? Can I just issue a shrink file on the
oringal large file or does that just push pages from the end of that data
file to the beginning and not to other datafiles'
Any suggestions would be great, I don't really want to create another
filegroup just yet.
Thanks :)"Malarb" <Malarb@.discussions.microsoft.com> wrote in message
news:BA0A6D10-4377-4247-A2A3-195FB60B308B@.microsoft.com...
>I am having trouble moving BLOBs (text fields).
> I have a 200Gb database in a single data file and want to split it into 4
> even sized files (I have my reasons). To achieve this I plan on adding 3
> data
> files to the Primary filegroup. From there I will issue a DBCC dbreindex
> on
> all my tables, which effectively recreates the index (or table if its a
> clustered index) across all datafiles and I get my even distribution.
> My plan comes unstuck when I encounter text fields and other BLOBs (as the
> clustered index just contains pointers doesnt it'). How can I move them?
> Do
> I have to do a select into or bcp? Can I just issue a shrink file on the
> oringal large file or does that just push pages from the end of that data
> file to the beginning and not to other datafiles'
> Any suggestions would be great, I don't really want to create another
> filegroup just yet.
I would create a new filegroup with the desired number and layout of files,
mark it as primary, and move all your objects there. Then drop the old
filegroup.
David|||You will need to create a new filegroup so you can create a new table on
this filegroup. Copy the old data to the new table. Drop the old table.
Rename the new tb to the old name. And then shrink the db to reclaim the
space.
--
-oj
"Malarb" <Malarb@.discussions.microsoft.com> wrote in message
news:BA0A6D10-4377-4247-A2A3-195FB60B308B@.microsoft.com...
>I am having trouble moving BLOBs (text fields).
> I have a 200Gb database in a single data file and want to split it into 4
> even sized files (I have my reasons). To achieve this I plan on adding 3
> data
> files to the Primary filegroup. From there I will issue a DBCC dbreindex
> on
> all my tables, which effectively recreates the index (or table if its a
> clustered index) across all datafiles and I get my even distribution.
> My plan comes unstuck when I encounter text fields and other BLOBs (as the
> clustered index just contains pointers doesnt it'). How can I move them?
> Do
> I have to do a select into or bcp? Can I just issue a shrink file on the
> oringal large file or does that just push pages from the end of that data
> file to the beginning and not to other datafiles'
> Any suggestions would be great, I don't really want to create another
> filegroup just yet.
> Thanks :)|||Hi,
I have the same problem, but i´m not a dba expert. How is sintaxe to create
a new table and accurate the it´s will in the new files group.
Could use the following command to creat a copy table'
select * into COPY_TABLE
from SOURCE_TABLE
WHERE 1=0
Thanks
"oj" wrote:
> You will need to create a new filegroup so you can create a new table on
> this filegroup. Copy the old data to the new table. Drop the old table.
> Rename the new tb to the old name. And then shrink the db to reclaim the
> space.
> --
> -oj
>
> "Malarb" <Malarb@.discussions.microsoft.com> wrote in message
> news:BA0A6D10-4377-4247-A2A3-195FB60B308B@.microsoft.com...
> >I am having trouble moving BLOBs (text fields).
> >
> > I have a 200Gb database in a single data file and want to split it into 4
> > even sized files (I have my reasons). To achieve this I plan on adding 3
> > data
> > files to the Primary filegroup. From there I will issue a DBCC dbreindex
> > on
> > all my tables, which effectively recreates the index (or table if its a
> > clustered index) across all datafiles and I get my even distribution.
> >
> > My plan comes unstuck when I encounter text fields and other BLOBs (as the
> > clustered index just contains pointers doesnt it'). How can I move them?
> > Do
> > I have to do a select into or bcp? Can I just issue a shrink file on the
> > oringal large file or does that just push pages from the end of that data
> > file to the beginning and not to other datafiles'
> >
> > Any suggestions would be great, I don't really want to create another
> > filegroup just yet.
> >
> > Thanks :)
>
>|||select/into does not allow you to specify the filegroup. You will need to
create a new table then do update.
e.g.
create table newtab(...) on filegroup
update newtab
set ...= old...
from newtab,old
where newtab.pk=old.pk
-oj
"Fabio Rebelo" <FabioRebelo@.discussions.microsoft.com> wrote in message
news:07896DF0-B18B-4ACB-A854-EFBEE387E32D@.microsoft.com...
> Hi,
> I have the same problem, but i´m not a dba expert. How is sintaxe to
> create
> a new table and accurate the it´s will in the new files group.
> Could use the following command to creat a copy table'
> select * into COPY_TABLE
> from SOURCE_TABLE
> WHERE 1=0
> Thanks
>
> "oj" wrote:
>> You will need to create a new filegroup so you can create a new table on
>> this filegroup. Copy the old data to the new table. Drop the old table.
>> Rename the new tb to the old name. And then shrink the db to reclaim the
>> space.
>> --
>> -oj
>>
>> "Malarb" <Malarb@.discussions.microsoft.com> wrote in message
>> news:BA0A6D10-4377-4247-A2A3-195FB60B308B@.microsoft.com...
>> >I am having trouble moving BLOBs (text fields).
>> >
>> > I have a 200Gb database in a single data file and want to split it into
>> > 4
>> > even sized files (I have my reasons). To achieve this I plan on adding
>> > 3
>> > data
>> > files to the Primary filegroup. From there I will issue a DBCC
>> > dbreindex
>> > on
>> > all my tables, which effectively recreates the index (or table if its a
>> > clustered index) across all datafiles and I get my even distribution.
>> >
>> > My plan comes unstuck when I encounter text fields and other BLOBs (as
>> > the
>> > clustered index just contains pointers doesnt it'). How can I move
>> > them?
>> > Do
>> > I have to do a select into or bcp? Can I just issue a shrink file on
>> > the
>> > oringal large file or does that just push pages from the end of that
>> > data
>> > file to the beginning and not to other datafiles'
>> >
>> > Any suggestions would be great, I don't really want to create another
>> > filegroup just yet.
>> >
>> > Thanks :)
>>
