Moving a Large Table into New datafile vs. New database

Hi,
We have a production database of size 40GB and growing at the rate 1.5
GB/month and one table is occupying about 20GB and all other tables are of
normal size. Till now every table is in the PRIMARY group and it has only on
e
datafile. Now we would like to split that database by moving that large
table out.
I was wondering if anyone could tell me what would be the best option in
terms of performance of SQLServer of the following options:
1. Create a new Filegroup and move the table into the newfile group.
2. Simply add a new datafile to existing PRIMARY group and do nothing.
3. Create a new database and move the table to the new database, and create
a view of the table in the first database pointing to the table in the new
database (Assuming the foreign key constraints are taken care with in
application).
Thankyou,
VMVM wrote:
> Hi,
> We have a production database of size 40GB and growing at the rate 1.5
> GB/month and one table is occupying about 20GB and all other tables
> are of normal size. Till now every table is in the PRIMARY group and
> it has only one datafile. Now we would like to split that database
> by moving that large table out.
> I was wondering if anyone could tell me what would be the best option
> in terms of performance of SQLServer of the following options:
> 1. Create a new Filegroup and move the table into the newfile group.
> 2. Simply add a new datafile to existing PRIMARY group and do nothing.
> 3. Create a new database and move the table to the new database, and
> create a view of the table in the first database pointing to the
> table in the new database (Assuming the foreign key constraints are
> taken care with in application).
> Thankyou,
> VM
First we would want to understand why you feel you need to move this
table out of the primary filegroup. The size is not a concern for me,
but it may be for you. Are you having backup issues? Also, if you create
a second filegroup, it would really need to be on a different array to
provide performance benefits. If the new filegroup is on the same drive
array, it will not help (backup flexibility aside).
If you do decide to move the table to another filegroup, the easiest way
to do this is to recreate the clustered index for the table on the new
filegroup (off hours). That will cause the table to move to the new
location. Non-clustered indexes will stay put, however. You can move
them as well by manually recreating them, but having the non-clustered
indexes on a separate array can help performance if the table is
accessed/updated frequently.
David Gugick
Quest Software
www.quest.com|||Hi David,
Thankyou for the reply. We have been observing some performace degradation
of our database recently including full database backup time taken. After
brainstorming the ways we can split one large database file into multiple
physical files and we came up with below given options.
I think we might create a new filegroup (as you said on a different array)
and move some of the data. Either we would move all the indexes on to the
new group, or, Since one of our table is half of the size of total database
and is frequently inserted and updated, we may move that one table onto new
filegroup. Creation of clustered index is also a great idea.
Thanks again,
VM
"David Gugick" wrote:

> VM wrote:
> First we would want to understand why you feel you need to move this
> table out of the primary filegroup. The size is not a concern for me,
> but it may be for you. Are you having backup issues? Also, if you create
> a second filegroup, it would really need to be on a different array to
> provide performance benefits. If the new filegroup is on the same drive
> array, it will not help (backup flexibility aside).
> If you do decide to move the table to another filegroup, the easiest way
> to do this is to recreate the clustered index for the table on the new
> filegroup (off hours). That will cause the table to move to the new
> location. Non-clustered indexes will stay put, however. You can move
> them as well by manually recreating them, but having the non-clustered
> indexes on a separate array can help performance if the table is
> accessed/updated frequently.
>
> --
> David Gugick
> Quest Software
> www.quest.com
>

Moving a Large Table into New datafile vs. New database

Hi,
We have a production database of size 40GB and growing at the rate 1.5
GB/month and one table is occupying about 20GB and all other tables are of
normal size. Till now every table is in the PRIMARY group and it has only one
datafile. Now we would like to split that database by moving that large
table out.
I was wondering if anyone could tell me what would be the best option in
terms of performance of SQLServer of the following options:
1. Create a new Filegroup and move the table into the newfile group.
2. Simply add a new datafile to existing PRIMARY group and do nothing.
3. Create a new database and move the table to the new database, and create
a view of the table in the first database pointing to the table in the new
database (Assuming the foreign key constraints are taken care with in
application).
Thankyou,
VMVM wrote:
> Hi,
> We have a production database of size 40GB and growing at the rate 1.5
> GB/month and one table is occupying about 20GB and all other tables
> are of normal size. Till now every table is in the PRIMARY group and
> it has only one datafile. Now we would like to split that database
> by moving that large table out.
> I was wondering if anyone could tell me what would be the best option
> in terms of performance of SQLServer of the following options:
> 1. Create a new Filegroup and move the table into the newfile group.
> 2. Simply add a new datafile to existing PRIMARY group and do nothing.
> 3. Create a new database and move the table to the new database, and
> create a view of the table in the first database pointing to the
> table in the new database (Assuming the foreign key constraints are
> taken care with in application).
> Thankyou,
> VM
First we would want to understand why you feel you need to move this
table out of the primary filegroup. The size is not a concern for me,
but it may be for you. Are you having backup issues? Also, if you create
a second filegroup, it would really need to be on a different array to
provide performance benefits. If the new filegroup is on the same drive
array, it will not help (backup flexibility aside).
If you do decide to move the table to another filegroup, the easiest way
to do this is to recreate the clustered index for the table on the new
filegroup (off hours). That will cause the table to move to the new
location. Non-clustered indexes will stay put, however. You can move
them as well by manually recreating them, but having the non-clustered
indexes on a separate array can help performance if the table is
accessed/updated frequently.
David Gugick
Quest Software
www.quest.com|||Hi David,
Thankyou for the reply. We have been observing some performace degradation
of our database recently including full database backup time taken. After
brainstorming the ways we can split one large database file into multiple
physical files and we came up with below given options.
I think we might create a new filegroup (as you said on a different array)
and move some of the data. Either we would move all the indexes on to the
new group, or, Since one of our table is half of the size of total database
and is frequently inserted and updated, we may move that one table onto new
filegroup. Creation of clustered index is also a great idea.
Thanks again,
VM
"David Gugick" wrote:
> VM wrote:
> > Hi,
> >
> > We have a production database of size 40GB and growing at the rate 1.5
> > GB/month and one table is occupying about 20GB and all other tables
> > are of normal size. Till now every table is in the PRIMARY group and
> > it has only one datafile. Now we would like to split that database
> > by moving that large table out.
> >
> > I was wondering if anyone could tell me what would be the best option
> > in terms of performance of SQLServer of the following options:
> >
> > 1. Create a new Filegroup and move the table into the newfile group.
> > 2. Simply add a new datafile to existing PRIMARY group and do nothing.
> > 3. Create a new database and move the table to the new database, and
> > create a view of the table in the first database pointing to the
> > table in the new database (Assuming the foreign key constraints are
> > taken care with in application).
> >
> > Thankyou,
> > VM
> First we would want to understand why you feel you need to move this
> table out of the primary filegroup. The size is not a concern for me,
> but it may be for you. Are you having backup issues? Also, if you create
> a second filegroup, it would really need to be on a different array to
> provide performance benefits. If the new filegroup is on the same drive
> array, it will not help (backup flexibility aside).
> If you do decide to move the table to another filegroup, the easiest way
> to do this is to recreate the clustered index for the table on the new
> filegroup (off hours). That will cause the table to move to the new
> location. Non-clustered indexes will stay put, however. You can move
> them as well by manually recreating them, but having the non-clustered
> indexes on a separate array can help performance if the table is
> accessed/updated frequently.
>
> --
> David Gugick
> Quest Software
> www.quest.com
>

Moving a Large Table into New datafile vs. New database

Hi,
We have a production database of size 40GB and growing at the rate 1.5
GB/month and one table is occupying about 20GB and all other tables are of
normal size. Till now every table is in the PRIMARY group and it has only one
datafile. Now we would like to split that database by moving that large
table out.
I was wondering if anyone could tell me what would be the best option in
terms of performance of SQLServer of the following options:
1. Create a new Filegroup and move the table into the newfile group.
2. Simply add a new datafile to existing PRIMARY group and do nothing.
3. Create a new database and move the table to the new database, and create
a view of the table in the first database pointing to the table in the new
database (Assuming the foreign key constraints are taken care with in
application).
Thankyou,
VM
VM wrote:
> Hi,
> We have a production database of size 40GB and growing at the rate 1.5
> GB/month and one table is occupying about 20GB and all other tables
> are of normal size. Till now every table is in the PRIMARY group and
> it has only one datafile. Now we would like to split that database
> by moving that large table out.
> I was wondering if anyone could tell me what would be the best option
> in terms of performance of SQLServer of the following options:
> 1. Create a new Filegroup and move the table into the newfile group.
> 2. Simply add a new datafile to existing PRIMARY group and do nothing.
> 3. Create a new database and move the table to the new database, and
> create a view of the table in the first database pointing to the
> table in the new database (Assuming the foreign key constraints are
> taken care with in application).
> Thankyou,
> VM
First we would want to understand why you feel you need to move this
table out of the primary filegroup. The size is not a concern for me,
but it may be for you. Are you having backup issues? Also, if you create
a second filegroup, it would really need to be on a different array to
provide performance benefits. If the new filegroup is on the same drive
array, it will not help (backup flexibility aside).
If you do decide to move the table to another filegroup, the easiest way
to do this is to recreate the clustered index for the table on the new
filegroup (off hours). That will cause the table to move to the new
location. Non-clustered indexes will stay put, however. You can move
them as well by manually recreating them, but having the non-clustered
indexes on a separate array can help performance if the table is
accessed/updated frequently.
David Gugick
Quest Software
www.quest.com
|||Hi David,
Thankyou for the reply. We have been observing some performace degradation
of our database recently including full database backup time taken. After
brainstorming the ways we can split one large database file into multiple
physical files and we came up with below given options.
I think we might create a new filegroup (as you said on a different array)
and move some of the data. Either we would move all the indexes on to the
new group, or, Since one of our table is half of the size of total database
and is frequently inserted and updated, we may move that one table onto new
filegroup. Creation of clustered index is also a great idea.
Thanks again,
VM
"David Gugick" wrote:

> VM wrote:
> First we would want to understand why you feel you need to move this
> table out of the primary filegroup. The size is not a concern for me,
> but it may be for you. Are you having backup issues? Also, if you create
> a second filegroup, it would really need to be on a different array to
> provide performance benefits. If the new filegroup is on the same drive
> array, it will not help (backup flexibility aside).
> If you do decide to move the table to another filegroup, the easiest way
> to do this is to recreate the clustered index for the table on the new
> filegroup (off hours). That will cause the table to move to the new
> location. Non-clustered indexes will stay put, however. You can move
> them as well by manually recreating them, but having the non-clustered
> indexes on a separate array can help performance if the table is
> accessed/updated frequently.
>
> --
> David Gugick
> Quest Software
> www.quest.com
>

moving a 80GB sql data from one drive to another

Hello:
We have a 80GB SQL database that has 6 files, including the transaction log.
The data files size range from 6GB to 53GB. We are trying to move it from
one drive to another. Tried two methods, but either does not work.
1. Detach the database, and copy the data files to the destination drive.
There was a Windows message saying that there was not enough resource (our
computer server has 2GB of Ram).
2. Create a new database in the destination drive. Then run SQL import
utility to import the objects and data to the new drive. It failed, but the
error message was not clear enough to point out what the problem is. It
seems most of the data was copied however, as the destination database size
is about 80GB.
Any idea?
Thanks,
QThe detach and attach should have worked fine. I don't know where the
resource error came from. Are you ruining anything else on the server at
that time? Did you try to copy the files one at a time?
Andrew J. Kelly SQL MVP
"Q" <Q@.discussions.microsoft.com> wrote in message
news:E9D6F236-F79A-45D9-A36A-1C8F8F2557AB@.microsoft.com...
> Hello:
> We have a 80GB SQL database that has 6 files, including the transaction
> log.
> The data files size range from 6GB to 53GB. We are trying to move it from
> one drive to another. Tried two methods, but either does not work.
> 1. Detach the database, and copy the data files to the destination drive.
> There was a Windows message saying that there was not enough resource (our
> computer server has 2GB of Ram).
> 2. Create a new database in the destination drive. Then run SQL import
> utility to import the objects and data to the new drive. It failed, but
> the
> error message was not clear enough to point out what the problem is. It
> seems most of the data was copied however, as the destination database
> size
> is about 80GB.
> Any idea?
> Thanks,
> Q|||Hi,
The 2 best approaches are:-
1. detach the database , copy the MDF, NDF, LDF to destination drive, Attach
the datbase. This method is not at all resouce intencive. So possibility of
memory utilization is not at all possible. CHeck if other process is
utilizing the memory.
2. Backup the database (BACKUP DATABASE), Copy the BAK file to destination,
restore the database (RESTORE DATABASE. This is actually a online operation
.
Thanks
Hari
SQL Server MVP
"Q" wrote:

> Hello:
> We have a 80GB SQL database that has 6 files, including the transaction lo
g.
> The data files size range from 6GB to 53GB. We are trying to move it fro
m
> one drive to another. Tried two methods, but either does not work.
> 1. Detach the database, and copy the data files to the destination drive.
> There was a Windows message saying that there was not enough resource (our
> computer server has 2GB of Ram).
> 2. Create a new database in the destination drive. Then run SQL import
> utility to import the objects and data to the new drive. It failed, but t
he
> error message was not clear enough to point out what the problem is. It
> seems most of the data was copied however, as the destination database siz
e
> is about 80GB.
> Any idea?
> Thanks,
> Q|||I tend to use ESEFILE to copy large files as the speed is much better than
xcopy/robocopy etc and you're less likely to hit system resource issues. You
can find it on an Exchange CD if you have one
http://support.microsoft.com/kb/248406/EN-US/
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Q" <Q@.discussions.microsoft.com> wrote in message
news:E9D6F236-F79A-45D9-A36A-1C8F8F2557AB@.microsoft.com...
> Hello:
> We have a 80GB SQL database that has 6 files, including the transaction
> log.
> The data files size range from 6GB to 53GB. We are trying to move it from
> one drive to another. Tried two methods, but either does not work.
> 1. Detach the database, and copy the data files to the destination drive.
> There was a Windows message saying that there was not enough resource (our
> computer server has 2GB of Ram).
> 2. Create a new database in the destination drive. Then run SQL import
> utility to import the objects and data to the new drive. It failed, but
> the
> error message was not clear enough to point out what the problem is. It
> seems most of the data was copied however, as the destination database
> size
> is about 80GB.
> Any idea?
> Thanks,
> Q|||Andrew:
Thanks for your reply. We were not running anything particular at that
time. Our server is mainly for SQL server. Perhaps I shall try to shut it
down before copying/moving the data file. I may have selected to copy all
the data files at the same time. That may have something to do with the
resource warning.
I will give it another try.
Thanks again!
Q
"Andrew J. Kelly" wrote:

> The detach and attach should have worked fine. I don't know where the
> resource error came from. Are you ruining anything else on the server at
> that time? Did you try to copy the files one at a time?
> --
> Andrew J. Kelly SQL MVP
>
> "Q" <Q@.discussions.microsoft.com> wrote in message
> news:E9D6F236-F79A-45D9-A36A-1C8F8F2557AB@.microsoft.com...
>
>|||Thanks Hari!
Q
"Hari Prasad" wrote:
[vbcol=seagreen]
> Hi,
> The 2 best approaches are:-
> 1. detach the database , copy the MDF, NDF, LDF to destination drive, Atta
ch
> the datbase. This method is not at all resouce intencive. So possibility o
f
> memory utilization is not at all possible. CHeck if other process is
> utilizing the memory.
> 2. Backup the database (BACKUP DATABASE), Copy the BAK file to destination
,
> restore the database (RESTORE DATABASE. This is actually a online operati
on.
> Thanks
> Hari
> SQL Server MVP
>
> "Q" wrote:
>|||Hello Jasper:
Thanks for your idea. Not sure if we have the exchance CD. I was thinking
about using a third party software from Western Digital. When I bought a
disk from them, it came with a software for copying files.
Thanks again for your ideas!
Q
"Jasper Smith" wrote:

> I tend to use ESEFILE to copy large files as the speed is much better than
> xcopy/robocopy etc and you're less likely to hit system resource issues. Y
ou
> can find it on an Exchange CD if you have one
> http://support.microsoft.com/kb/248406/EN-US/
> --
> HTH
> Jasper Smith (SQL Server MVP)
> http://www.sqldbatips.com
> I support PASS - the definitive, global
> community for SQL Server professionals -
> http://www.sqlpass.org
> "Q" <Q@.discussions.microsoft.com> wrote in message
> news:E9D6F236-F79A-45D9-A36A-1C8F8F2557AB@.microsoft.com...
>
>

moving a 80GB sql data from one drive to another

Hello:
We have a 80GB SQL database that has 6 files, including the transaction log.
The data files size range from 6GB to 53GB. We are trying to move it from
one drive to another. Tried two methods, but either does not work.
1. Detach the database, and copy the data files to the destination drive.
There was a Windows message saying that there was not enough resource (our
computer server has 2GB of Ram).
2. Create a new database in the destination drive. Then run SQL import
utility to import the objects and data to the new drive. It failed, but the
error message was not clear enough to point out what the problem is. It
seems most of the data was copied however, as the destination database size
is about 80GB.
Any idea?
Thanks,
QThe detach and attach should have worked fine. I don't know where the
resource error came from. Are you ruining anything else on the server at
that time? Did you try to copy the files one at a time?
--
Andrew J. Kelly SQL MVP
"Q" <Q@.discussions.microsoft.com> wrote in message
news:E9D6F236-F79A-45D9-A36A-1C8F8F2557AB@.microsoft.com...
> Hello:
> We have a 80GB SQL database that has 6 files, including the transaction
> log.
> The data files size range from 6GB to 53GB. We are trying to move it from
> one drive to another. Tried two methods, but either does not work.
> 1. Detach the database, and copy the data files to the destination drive.
> There was a Windows message saying that there was not enough resource (our
> computer server has 2GB of Ram).
> 2. Create a new database in the destination drive. Then run SQL import
> utility to import the objects and data to the new drive. It failed, but
> the
> error message was not clear enough to point out what the problem is. It
> seems most of the data was copied however, as the destination database
> size
> is about 80GB.
> Any idea?
> Thanks,
> Q|||Hi,
The 2 best approaches are:-
1. detach the database , copy the MDF, NDF, LDF to destination drive, Attach
the datbase. This method is not at all resouce intencive. So possibility of
memory utilization is not at all possible. CHeck if other process is
utilizing the memory.
2. Backup the database (BACKUP DATABASE), Copy the BAK file to destination,
restore the database (RESTORE DATABASE. This is actually a online operation.
Thanks
Hari
SQL Server MVP
"Q" wrote:
> Hello:
> We have a 80GB SQL database that has 6 files, including the transaction log.
> The data files size range from 6GB to 53GB. We are trying to move it from
> one drive to another. Tried two methods, but either does not work.
> 1. Detach the database, and copy the data files to the destination drive.
> There was a Windows message saying that there was not enough resource (our
> computer server has 2GB of Ram).
> 2. Create a new database in the destination drive. Then run SQL import
> utility to import the objects and data to the new drive. It failed, but the
> error message was not clear enough to point out what the problem is. It
> seems most of the data was copied however, as the destination database size
> is about 80GB.
> Any idea?
> Thanks,
> Q|||I tend to use ESEFILE to copy large files as the speed is much better than
xcopy/robocopy etc and you're less likely to hit system resource issues. You
can find it on an Exchange CD if you have one
http://support.microsoft.com/kb/248406/EN-US/
--
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Q" <Q@.discussions.microsoft.com> wrote in message
news:E9D6F236-F79A-45D9-A36A-1C8F8F2557AB@.microsoft.com...
> Hello:
> We have a 80GB SQL database that has 6 files, including the transaction
> log.
> The data files size range from 6GB to 53GB. We are trying to move it from
> one drive to another. Tried two methods, but either does not work.
> 1. Detach the database, and copy the data files to the destination drive.
> There was a Windows message saying that there was not enough resource (our
> computer server has 2GB of Ram).
> 2. Create a new database in the destination drive. Then run SQL import
> utility to import the objects and data to the new drive. It failed, but
> the
> error message was not clear enough to point out what the problem is. It
> seems most of the data was copied however, as the destination database
> size
> is about 80GB.
> Any idea?
> Thanks,
> Q|||Andrew:
Thanks for your reply. We were not running anything particular at that
time. Our server is mainly for SQL server. Perhaps I shall try to shut it
down before copying/moving the data file. I may have selected to copy all
the data files at the same time. That may have something to do with the
resource warning.
I will give it another try.
Thanks again!
Q
"Andrew J. Kelly" wrote:
> The detach and attach should have worked fine. I don't know where the
> resource error came from. Are you ruining anything else on the server at
> that time? Did you try to copy the files one at a time?
> --
> Andrew J. Kelly SQL MVP
>
> "Q" <Q@.discussions.microsoft.com> wrote in message
> news:E9D6F236-F79A-45D9-A36A-1C8F8F2557AB@.microsoft.com...
> > Hello:
> >
> > We have a 80GB SQL database that has 6 files, including the transaction
> > log.
> > The data files size range from 6GB to 53GB. We are trying to move it from
> > one drive to another. Tried two methods, but either does not work.
> >
> > 1. Detach the database, and copy the data files to the destination drive.
> > There was a Windows message saying that there was not enough resource (our
> > computer server has 2GB of Ram).
> > 2. Create a new database in the destination drive. Then run SQL import
> > utility to import the objects and data to the new drive. It failed, but
> > the
> > error message was not clear enough to point out what the problem is. It
> > seems most of the data was copied however, as the destination database
> > size
> > is about 80GB.
> >
> > Any idea?
> >
> > Thanks,
> >
> > Q
>
>|||Thanks Hari!
Q
"Hari Prasad" wrote:
> Hi,
> The 2 best approaches are:-
> 1. detach the database , copy the MDF, NDF, LDF to destination drive, Attach
> the datbase. This method is not at all resouce intencive. So possibility of
> memory utilization is not at all possible. CHeck if other process is
> utilizing the memory.
> 2. Backup the database (BACKUP DATABASE), Copy the BAK file to destination,
> restore the database (RESTORE DATABASE. This is actually a online operation.
> Thanks
> Hari
> SQL Server MVP
>
> "Q" wrote:
> > Hello:
> >
> > We have a 80GB SQL database that has 6 files, including the transaction log.
> > The data files size range from 6GB to 53GB. We are trying to move it from
> > one drive to another. Tried two methods, but either does not work.
> >
> > 1. Detach the database, and copy the data files to the destination drive.
> > There was a Windows message saying that there was not enough resource (our
> > computer server has 2GB of Ram).
> > 2. Create a new database in the destination drive. Then run SQL import
> > utility to import the objects and data to the new drive. It failed, but the
> > error message was not clear enough to point out what the problem is. It
> > seems most of the data was copied however, as the destination database size
> > is about 80GB.
> >
> > Any idea?
> >
> > Thanks,
> >
> > Q|||Hello Jasper:
Thanks for your idea. Not sure if we have the exchance CD. I was thinking
about using a third party software from Western Digital. When I bought a
disk from them, it came with a software for copying files.
Thanks again for your ideas!
Q
"Jasper Smith" wrote:
> I tend to use ESEFILE to copy large files as the speed is much better than
> xcopy/robocopy etc and you're less likely to hit system resource issues. You
> can find it on an Exchange CD if you have one
> http://support.microsoft.com/kb/248406/EN-US/
> --
> HTH
> Jasper Smith (SQL Server MVP)
> http://www.sqldbatips.com
> I support PASS - the definitive, global
> community for SQL Server professionals -
> http://www.sqlpass.org
> "Q" <Q@.discussions.microsoft.com> wrote in message
> news:E9D6F236-F79A-45D9-A36A-1C8F8F2557AB@.microsoft.com...
> > Hello:
> >
> > We have a 80GB SQL database that has 6 files, including the transaction
> > log.
> > The data files size range from 6GB to 53GB. We are trying to move it from
> > one drive to another. Tried two methods, but either does not work.
> >
> > 1. Detach the database, and copy the data files to the destination drive.
> > There was a Windows message saying that there was not enough resource (our
> > computer server has 2GB of Ram).
> > 2. Create a new database in the destination drive. Then run SQL import
> > utility to import the objects and data to the new drive. It failed, but
> > the
> > error message was not clear enough to point out what the problem is. It
> > seems most of the data was copied however, as the destination database
> > size
> > is about 80GB.
> >
> > Any idea?
> >
> > Thanks,
> >
> > Q
>
>sql

moving a 80GB sql data from one drive to another

Hello:
We have a 80GB SQL database that has 6 files, including the transaction log.
The data files size range from 6GB to 53GB. We are trying to move it from
one drive to another. Tried two methods, but either does not work.
1. Detach the database, and copy the data files to the destination drive.
There was a Windows message saying that there was not enough resource (our
computer server has 2GB of Ram).
2. Create a new database in the destination drive. Then run SQL import
utility to import the objects and data to the new drive. It failed, but the
error message was not clear enough to point out what the problem is. It
seems most of the data was copied however, as the destination database size
is about 80GB.
Any idea?
Thanks,
Q
The detach and attach should have worked fine. I don't know where the
resource error came from. Are you ruining anything else on the server at
that time? Did you try to copy the files one at a time?
Andrew J. Kelly SQL MVP
"Q" <Q@.discussions.microsoft.com> wrote in message
news:E9D6F236-F79A-45D9-A36A-1C8F8F2557AB@.microsoft.com...
> Hello:
> We have a 80GB SQL database that has 6 files, including the transaction
> log.
> The data files size range from 6GB to 53GB. We are trying to move it from
> one drive to another. Tried two methods, but either does not work.
> 1. Detach the database, and copy the data files to the destination drive.
> There was a Windows message saying that there was not enough resource (our
> computer server has 2GB of Ram).
> 2. Create a new database in the destination drive. Then run SQL import
> utility to import the objects and data to the new drive. It failed, but
> the
> error message was not clear enough to point out what the problem is. It
> seems most of the data was copied however, as the destination database
> size
> is about 80GB.
> Any idea?
> Thanks,
> Q
|||Hi,
The 2 best approaches are:-
1. detach the database , copy the MDF, NDF, LDF to destination drive, Attach
the datbase. This method is not at all resouce intencive. So possibility of
memory utilization is not at all possible. CHeck if other process is
utilizing the memory.
2. Backup the database (BACKUP DATABASE), Copy the BAK file to destination,
restore the database (RESTORE DATABASE. This is actually a online operation.
Thanks
Hari
SQL Server MVP
"Q" wrote:

> Hello:
> We have a 80GB SQL database that has 6 files, including the transaction log.
> The data files size range from 6GB to 53GB. We are trying to move it from
> one drive to another. Tried two methods, but either does not work.
> 1. Detach the database, and copy the data files to the destination drive.
> There was a Windows message saying that there was not enough resource (our
> computer server has 2GB of Ram).
> 2. Create a new database in the destination drive. Then run SQL import
> utility to import the objects and data to the new drive. It failed, but the
> error message was not clear enough to point out what the problem is. It
> seems most of the data was copied however, as the destination database size
> is about 80GB.
> Any idea?
> Thanks,
> Q
|||I tend to use ESEFILE to copy large files as the speed is much better than
xcopy/robocopy etc and you're less likely to hit system resource issues. You
can find it on an Exchange CD if you have one
http://support.microsoft.com/kb/248406/EN-US/
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Q" <Q@.discussions.microsoft.com> wrote in message
news:E9D6F236-F79A-45D9-A36A-1C8F8F2557AB@.microsoft.com...
> Hello:
> We have a 80GB SQL database that has 6 files, including the transaction
> log.
> The data files size range from 6GB to 53GB. We are trying to move it from
> one drive to another. Tried two methods, but either does not work.
> 1. Detach the database, and copy the data files to the destination drive.
> There was a Windows message saying that there was not enough resource (our
> computer server has 2GB of Ram).
> 2. Create a new database in the destination drive. Then run SQL import
> utility to import the objects and data to the new drive. It failed, but
> the
> error message was not clear enough to point out what the problem is. It
> seems most of the data was copied however, as the destination database
> size
> is about 80GB.
> Any idea?
> Thanks,
> Q
|||Andrew:
Thanks for your reply. We were not running anything particular at that
time. Our server is mainly for SQL server. Perhaps I shall try to shut it
down before copying/moving the data file. I may have selected to copy all
the data files at the same time. That may have something to do with the
resource warning.
I will give it another try.
Thanks again!
Q
"Andrew J. Kelly" wrote:

> The detach and attach should have worked fine. I don't know where the
> resource error came from. Are you ruining anything else on the server at
> that time? Did you try to copy the files one at a time?
> --
> Andrew J. Kelly SQL MVP
>
> "Q" <Q@.discussions.microsoft.com> wrote in message
> news:E9D6F236-F79A-45D9-A36A-1C8F8F2557AB@.microsoft.com...
>
>
|||Thanks Hari!
Q
"Hari Prasad" wrote:
[vbcol=seagreen]
> Hi,
> The 2 best approaches are:-
> 1. detach the database , copy the MDF, NDF, LDF to destination drive, Attach
> the datbase. This method is not at all resouce intencive. So possibility of
> memory utilization is not at all possible. CHeck if other process is
> utilizing the memory.
> 2. Backup the database (BACKUP DATABASE), Copy the BAK file to destination,
> restore the database (RESTORE DATABASE. This is actually a online operation.
> Thanks
> Hari
> SQL Server MVP
>
> "Q" wrote:
|||Hello Jasper:
Thanks for your idea. Not sure if we have the exchance CD. I was thinking
about using a third party software from Western Digital. When I bought a
disk from them, it came with a software for copying files.
Thanks again for your ideas!
Q
"Jasper Smith" wrote:

> I tend to use ESEFILE to copy large files as the speed is much better than
> xcopy/robocopy etc and you're less likely to hit system resource issues. You
> can find it on an Exchange CD if you have one
> http://support.microsoft.com/kb/248406/EN-US/
> --
> HTH
> Jasper Smith (SQL Server MVP)
> http://www.sqldbatips.com
> I support PASS - the definitive, global
> community for SQL Server professionals -
> http://www.sqlpass.org
> "Q" <Q@.discussions.microsoft.com> wrote in message
> news:E9D6F236-F79A-45D9-A36A-1C8F8F2557AB@.microsoft.com...
>
>