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...
>
>

Move transaction logs in SQL cluster

Hi
I need to move the transaction logs for all the sql db's (including master,
model and user db's etc) to a seperate drive. Does anyone have any experience
doing this in a sql cluster?
Thanks
Parky
Parky wrote:
> Hi
> I need to move the transaction logs for all the sql db's (including
> master, model and user db's etc) to a seperate drive. Does anyone
> have any experience doing this in a sql cluster?
There is no difference if you move t-log in a single box machine or in a
cluster environment.
You have to pay attention to move db files ONLY on shared disk and modify,
using EM, the switch -l in startup properties of SQL Server and assign them
the new location of t-log of master database

> Thanks
Bye
Luca Bianchi
Microsoft MVP - SQL Server
http://mvp.support.microsoft.com
http://italy.mvps.org
|||Hello Parky,
I agree with Luca. There is no difference between moving the db files on a standalone SQL server v/s clustered SQL Server. One difference is that the shared dirve where you want to move the db files should be in
the dependency list of SQL Server resource. (For Ex. Say you want to move the Tlogs to a shared drive F:. Make sure that SQL Server resource has F: as a dependency. If not you will have have make it. Note that
this requires a downtime as you will have to take SQL Server resource offline, add the shared drive as a dependency and then take SQL Server online. Then you can use your usual steps (see
http://support.microsoft.com/kb/224071/EN-US/
INF: Moving SQL Server Databases to a New Location using Detach/Attach )
If you have never done this on a cluster, I will recommend you to practice the steps on a test SQL Cluster or a cluster using virtual technology.
HTH,
Best Regards,
Uttam Parui
Microsoft Corporation
This posting is provided "AS IS" with no warranties, and confers no rights.
Are you secure? For information about the Strategic Technology Protection Program and to order your FREE Security Tool Kit, please visit http://www.microsoft.com/security.
Microsoft highly recommends that users with Internet access update their Microsoft software to better protect against viruses and security vulnerabilities. The easiest way to do this is to visit the following websites:
http://www.microsoft.com/protect
http://www.microsoft.com/security/guidance/default.mspx
|||I would leave the System databases and there log file on one drive and split
the user databases to two diffrent databases, that way is you losse the log
drive you can still get sql server running and manage it to recreate the user
databases from the datafiles with diffrent location of the log files.
"Uttam Parui[MS]" wrote:

> Hello Parky,
> I agree with Luca. There is no difference between moving the db files on a standalone SQL server v/s clustered SQL Server. One difference is that the shared dirve where you want to move the db files should be in
> the dependency list of SQL Server resource. (For Ex. Say you want to move the Tlogs to a shared drive F:. Make sure that SQL Server resource has F: as a dependency. If not you will have have make it. Note that
> this requires a downtime as you will have to take SQL Server resource offline, add the shared drive as a dependency and then take SQL Server online. Then you can use your usual steps (see
> http://support.microsoft.com/kb/224071/EN-US/
> INF: Moving SQL Server Databases to a New Location using Detach/Attach )
> If you have never done this on a cluster, I will recommend you to practice the steps on a test SQL Cluster or a cluster using virtual technology.
> HTH,
> Best Regards,
> Uttam Parui
> Microsoft Corporation
> This posting is provided "AS IS" with no warranties, and confers no rights.
> Are you secure? For information about the Strategic Technology Protection Program and to order your FREE Security Tool Kit, please visit http://www.microsoft.com/security.
> Microsoft highly recommends that users with Internet access update their Microsoft software to better protect against viruses and security vulnerabilities. The easiest way to do this is to visit the following websites:
> http://www.microsoft.com/protect
> http://www.microsoft.com/security/guidance/default.mspx
>
>

Move Transaction Log

I want to move the transaction log from its current
location (same as the databases) to a different drive for
performance reasons. How do I do that?
Check out sp_detach_db and sp_attach_db in the BOL.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Keith" <dkcamc@.yahoo.com> wrote in message
news:1d4301c49b5e$504e5b00$3a01280a@.phx.gbl...
I want to move the transaction log from its current
location (same as the databases) to a different drive for
performance reasons. How do I do that?
|||http://www.support.microsoft.com/?id=224071
Andrew J. Kelly SQL MVP
"Keith" <dkcamc@.yahoo.com> wrote in message
news:1d4301c49b5e$504e5b00$3a01280a@.phx.gbl...
> I want to move the transaction log from its current
> location (same as the databases) to a different drive for
> performance reasons. How do I do that?
|||Sorry if I'm dragging an old thread up, but I need to do this also. I've
tried using the SP_Detatch command, but it refuses to move the log, due to
having replications attached. Is there anyway I can move the TNS log without
deleting the replications and starting again?
Cheers
Steve
"Keith" wrote:

> I want to move the transaction log from its current
> location (same as the databases) to a different drive for
> performance reasons. How do I do that?
>

Move Transaction Log

I want to move the transaction log from its current
location (same as the databases) to a different drive for
performance reasons. How do I do that?Check out sp_detach_db and sp_attach_db in the BOL.
--
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Keith" <dkcamc@.yahoo.com> wrote in message
news:1d4301c49b5e$504e5b00$3a01280a@.phx.gbl...
I want to move the transaction log from its current
location (same as the databases) to a different drive for
performance reasons. How do I do that?|||http://www.support.microsoft.com/?id=224071
Andrew J. Kelly SQL MVP
"Keith" <dkcamc@.yahoo.com> wrote in message
news:1d4301c49b5e$504e5b00$3a01280a@.phx.gbl...
> I want to move the transaction log from its current
> location (same as the databases) to a different drive for
> performance reasons. How do I do that?|||Sorry if I'm dragging an old thread up, but I need to do this also. I've
tried using the SP_Detatch command, but it refuses to move the log, due to
having replications attached. Is there anyway I can move the TNS log without
deleting the replications and starting again?
Cheers
Steve
"Keith" wrote:
> I want to move the transaction log from its current
> location (same as the databases) to a different drive for
> performance reasons. How do I do that?
>

move T-log to another Disk?

Hello DBAs,
I think there are several different ways to move a
transaction log file to another disk. What would you do?
what method will you go with?
Currently DBs and T-logs are located in same disk, and I
learned that if they are located different disks would
increase SQL server performance from SQL trainning class.
Please advice me on this, new DBA here.
Thanks in advance.
SunnyThis is a multi-part message in MIME format.
--=_NextPart_000_0295_01C3512E.6C124CF0
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
Detach the database using sp_detach_db. (Don't bother updating the =statistics.) Move your log to the new drive, then re-attach, using =sp_attach_db.
-- Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"litnadsl" <lintadsl@.yahoo.com> wrote in message =news:031f01c3514e$f63ce230$a301280a@.phx.gbl...
Hello DBAs,
I think there are several different ways to move a transaction log file to another disk. What would you do? what method will you go with?
Currently DBs and T-logs are located in same disk, and I learned that if they are located different disks would increase SQL server performance from SQL trainning class.
Please advice me on this, new DBA here.
Thanks in advance.
Sunny
--=_NextPart_000_0295_01C3512E.6C124CF0
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

---

Detach the database using =sp_detach_db. (Don't bother updating the statistics.) Move your log to the new =drive, then re-attach, using sp_attach_db.
-- Tom
---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql
"litnadsl" wrote in =message news:031f01c3514e$f6=3ce230$a301280a@.phx.gbl...Hello DBAs,I think there are several different ways to move a =transaction log file to another disk. What would you do? what method will you =go with?Currently DBs and T-logs are located in same disk, and I =learned that if they are located different disks would increase SQL server performance from SQL trainning class.Please advice me on this, =new DBA here.Thanks in advance.Sunny

---

--=_NextPart_000_0295_01C3512E.6C124CF0--|||If you have a RAID system then you do not need to store the log file to a
different disk.
"litnadsl" <lintadsl@.yahoo.com> wrote in message
news:031f01c3514e$f63ce230$a301280a@.phx.gbl...
> Hello DBAs,
> I think there are several different ways to move a
> transaction log file to another disk. What would you do?
> what method will you go with?
> Currently DBs and T-logs are located in same disk, and I
> learned that if they are located different disks would
> increase SQL server performance from SQL trainning class.
> Please advice me on this, new DBA here.
> Thanks in advance.
> Sunny|||Detach the database and then when you do sp_attachdb then specify different
locations.
This you can do with user databases.
For system databases, you need to follow different approach.
"litnadsl" <lintadsl@.yahoo.com> wrote in message
news:031f01c3514e$f63ce230$a301280a@.phx.gbl...
> Hello DBAs,
> I think there are several different ways to move a
> transaction log file to another disk. What would you do?
> what method will you go with?
> Currently DBs and T-logs are located in same disk, and I
> learned that if they are located different disks would
> increase SQL server performance from SQL trainning class.
> Please advice me on this, new DBA here.
> Thanks in advance.
> Sunny|||For user databases, follow Tom's description. For master, model, tempdb,
there're more steps involved including setting up trace flags and modifying
startup parameters. Please refer to the following MS KB articles:
http://www.support.microsoft.com/?id=314546 Moving DB's between Servers
http://www.support.microsoft.com/?id=224071 Moving SQL Server Databases
to a New Location with Detach/Attach
Richard
"litnadsl" <lintadsl@.yahoo.com> wrote in message
news:031f01c3514e$f63ce230$a301280a@.phx.gbl...
> Hello DBAs,
> I think there are several different ways to move a
> transaction log file to another disk. What would you do?
> what method will you go with?
> Currently DBs and T-logs are located in same disk, and I
> learned that if they are located different disks would
> increase SQL server performance from SQL trainning class.
> Please advice me on this, new DBA here.
> Thanks in advance.
> Sunny|||Danut
I'm a little puzzled how you think a RAID system affects
the way a Transaction log is sequentially written to? (As
that is the main reason for putting it on a seperate disk)
Regards
John|||Well, I'm not expert but I read some articles on MSDN. Disk stripping offers
the best performance, adding the parity will make it falut tolerant so you
get both.
When you write to a RAID logical drive it will write it to more than one
physical drive in parallel and this is what makes it performant.
Well, let's look into MSDN:
"Both Microsoft Windows NT® and Microsoft Windows® 2000-based disk striping,
and striping with parity, can improve performance. Disk striping with parity
also protects against data loss in the event of media failure."
"Disk striping writes data in stripes across a volume (created from areas of
free space). For more information about volumes, see the Windows NT or
Windows 2000 documentation.
These areas are all the same size and are spread over an array of disks (up
to 32 disks). Striping writes files across all disks, so data is added to
all partitions in the set at the same rate.
Windows NT-based disk striping and Windows 2000 volume sets implement RAID
0. Disk striping provides the best performance of all Windows NT Server
disk-management strategies, but does not provide any fault-tolerance
protection.
Disk striping with parity is similar to disk striping. Disk striping with
parity adds a parity-information stripe to each disk partition in the
volume. This provides fault-tolerance protection equivalent to that of disk
mirroring, but requires much less space for the redundant data. Windows
NT-based disk striping with parity and Windows 2000 RAID-5 volumes implement
RAID 5. "
"John Bandettini" <johnbandettini@.yahoo.co.uk> wrote in message
news:0c6901c351f3$d490d470$a501280a@.phx.gbl...
> Danut
> I don't think you have got the idea here yet. RAID 5 uses
> an extra disk for parity checking. Say you have four disks
> in your RAID 5 array. You only have three disks to store
> data as you use one quarter of the space to hold parity
> data. Sql server uses this so that if one disk fails it
> can use the parity data to tell what data is missing. When
> the disk is replaced it can then rebuild the data.
> Performance wise RAID 5 is the worst of the raid systems,
> it turns one write into two reads and two writes to
> maintain the parity information.
> RAID 5 is not that bad if the database is mostly used for
> reads, with little write activity. For databases with high
> write activity, you are much better off using RAID 10 or
> RAID 01.
> Transaction logs are written too sequentially. If you keep
> your Transaction logs on seperate mirrored drives the
> heads are not moving all over the place they are writting
> where they already are. Putting transaction logs and
> database data files together on RAID 5 disks is just about
> the worst thing you can do performance wise. It might make
> administration easy, but it is a very bad idea.
> The only exception to that is if you are using OLAP with
> no update activity on the database.
> Regards
> John|||I know what is does, I've been a DBA for years, I'm just
telling you in simple terms
RAID 5 IS THE WORST PERFOMING RAID FOR DISK WRITES
TRANSACTION LOGS PERFORM BADLY ON RAID 5 BECAUSE THEY
WRITE A LOT
HAVING A TRANSACTION LOG ON THE SAME DISK AS YOUR DATABASE
SLOWS PERFORMANCE
You will not find anyone on here that disagees with any of
those statements.|||I will attest to what John is saying. Regardless of the Raid used it is
detrimental to performance to have the log file on the same array as the
data. The data is written randomly and the log is sequential and these two
just don't mix well. A Raid 1, 1+0 or 0+1 are much better for log files
than a Raid 5 but in any case it should always be on it's own array.
--
Andrew J. Kelly
SQL Server MVP
"John Bandettini" <johnbandettini@.yahoo.co.uk> wrote in message
news:0eb101c351fb$6df46440$a401280a@.phx.gbl...
> I know what is does, I've been a DBA for years, I'm just
> telling you in simple terms
> RAID 5 IS THE WORST PERFOMING RAID FOR DISK WRITES
> TRANSACTION LOGS PERFORM BADLY ON RAID 5 BECAUSE THEY
> WRITE A LOT
> HAVING A TRANSACTION LOG ON THE SAME DISK AS YOUR DATABASE
> SLOWS PERFORMANCE
> You will not find anyone on here that disagees with any of
> those statements.|||Ok. I got it.
Thanks!
Danut
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:uZdPL2fUDHA.1928@.TK2MSFTNGP12.phx.gbl...
> I will attest to what John is saying. Regardless of the Raid used it is
> detrimental to performance to have the log file on the same array as the
> data. The data is written randomly and the log is sequential and these
two
> just don't mix well. A Raid 1, 1+0 or 0+1 are much better for log files
> than a Raid 5 but in any case it should always be on it's own array.
> --
> Andrew J. Kelly
> SQL Server MVP
>
> "John Bandettini" <johnbandettini@.yahoo.co.uk> wrote in message
> news:0eb101c351fb$6df46440$a401280a@.phx.gbl...
> > I know what is does, I've been a DBA for years, I'm just
> > telling you in simple terms
> >
> > RAID 5 IS THE WORST PERFOMING RAID FOR DISK WRITES
> >
> > TRANSACTION LOGS PERFORM BADLY ON RAID 5 BECAUSE THEY
> > WRITE A LOT
> >
> > HAVING A TRANSACTION LOG ON THE SAME DISK AS YOUR DATABASE
> > SLOWS PERFORMANCE
> >
> > You will not find anyone on here that disagees with any of
> > those statements.
>

Move TempDB data/trans From C: to E:

I have a SQL Server 2000 database server on Windows 2000 Advanced Server.
What is the best way to move the TempDB data and transaction log files from
the C: to E: drive?
Thank You,I'm very interested in this as well. We were planning on doing this first
thing Saturday morning.
"Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
news:D7969E35-E4D2-4D22-A9BB-DD7B52EA6E2D@.microsoft.com...
> I have a SQL Server 2000 database server on Windows 2000 Advanced Server.
> What is the best way to move the TempDB data and transaction log files
> from
> the C: to E: drive?
> Thank You,|||Here ya go....BOL...just tried it and it worked great.
1. stop sql server
2. copy to your desired location
3. start sql server
4. run the alter statements below
5. restart sql server
USE master;
GO
ALTER DATABASE tempdb
MODIFY FILE (NAME = tempdev, FILENAME = 'E:\SQLData\tempdb.mdf');
GO
ALTER DATABASE tempdb
MODIFY FILE (NAME = templog, FILENAME = 'E:\SQLData\templog.ldf');
GO
"Tim Greenwood" <tim_greenwood A-T yahoo D-O-T com> wrote in message
news:uPtIzWxaGHA.2124@.TK2MSFTNGP03.phx.gbl...
> I'm very interested in this as well. We were planning on doing this first
> thing Saturday morning.
>
> "Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
> news:D7969E35-E4D2-4D22-A9BB-DD7B52EA6E2D@.microsoft.com...
>> I have a SQL Server 2000 database server on Windows 2000 Advanced Server.
>> What is the best way to move the TempDB data and transaction log files
>> from
>> the C: to E: drive?
>> Thank You,
>|||Hi Tim
What exactly are you doing in step 2? You shouldn't have to move anything,
since the tempdb files are recreated upon system startup. To move tempdb you
just need to run ALTER DATABASE and then restart your SQL Server.
--
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"Tim Greenwood" <tim_greenwood A-T yahoo D-O-T com> wrote in message
news:uRzZ4dxaGHA.3408@.TK2MSFTNGP04.phx.gbl...
> Here ya go....BOL...just tried it and it worked great.
> 1. stop sql server
> 2. copy to your desired location
> 3. start sql server
> 4. run the alter statements below
> 5. restart sql server
> USE master;
> GO
> ALTER DATABASE tempdb
> MODIFY FILE (NAME = tempdev, FILENAME = 'E:\SQLData\tempdb.mdf');
> GO
> ALTER DATABASE tempdb
> MODIFY FILE (NAME = templog, FILENAME = 'E:\SQLData\templog.ldf');
> GO
> "Tim Greenwood" <tim_greenwood A-T yahoo D-O-T com> wrote in message
> news:uPtIzWxaGHA.2124@.TK2MSFTNGP03.phx.gbl...
>> I'm very interested in this as well. We were planning on doing this
>> first thing Saturday morning.
>>
>> "Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
>> news:D7969E35-E4D2-4D22-A9BB-DD7B52EA6E2D@.microsoft.com...
>> I have a SQL Server 2000 database server on Windows 2000 Advanced
>> Server.
>> What is the best way to move the TempDB data and transaction log files
>> from
>> the C: to E: drive?
>> Thank You,
>>
>|||I was doing the steps as outlined in BOL...and I quote from BOL
G. Moving tempdb to a new location
The following example moves tempdb from its current location on disk to
another disk location.
1.. Determine the logical file names of the tempdb database and their
current location on disk.
SELECT name, physical_name
FROM sys.master_files
WHERE database_id = DB_ID('tempdb');
GO
2.. Physically move these files to the new location E:\SQLData.
In this example the new location specified for the tempdb files is
E:\SQLData. If you move the files to different location, modify the example
to point to that location.
3.. Change the location of each file by using ALTER DATABASE.
4.. Stop and restart the instance of SQL Server.
5.. Verify the file change.
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:uG0CSzxaGHA.1192@.TK2MSFTNGP03.phx.gbl...
> Hi Tim
> What exactly are you doing in step 2? You shouldn't have to move anything,
> since the tempdb files are recreated upon system startup. To move tempdb
> you just need to run ALTER DATABASE and then restart your SQL Server.
> --
> HTH
> Kalen Delaney, SQL Server MVP
> www.solidqualitylearning.com
>
> "Tim Greenwood" <tim_greenwood A-T yahoo D-O-T com> wrote in message
> news:uRzZ4dxaGHA.3408@.TK2MSFTNGP04.phx.gbl...
>> Here ya go....BOL...just tried it and it worked great.
>> 1. stop sql server
>> 2. copy to your desired location
>> 3. start sql server
>> 4. run the alter statements below
>> 5. restart sql server
>> USE master;
>> GO
>> ALTER DATABASE tempdb
>> MODIFY FILE (NAME = tempdev, FILENAME = 'E:\SQLData\tempdb.mdf');
>> GO
>> ALTER DATABASE tempdb
>> MODIFY FILE (NAME = templog, FILENAME = 'E:\SQLData\templog.ldf');
>> GO
>> "Tim Greenwood" <tim_greenwood A-T yahoo D-O-T com> wrote in message
>> news:uPtIzWxaGHA.2124@.TK2MSFTNGP03.phx.gbl...
>> I'm very interested in this as well. We were planning on doing this
>> first thing Saturday morning.
>>
>> "Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
>> news:D7969E35-E4D2-4D22-A9BB-DD7B52EA6E2D@.microsoft.com...
>> I have a SQL Server 2000 database server on Windows 2000 Advanced
>> Server.
>> What is the best way to move the TempDB data and transaction log files
>> from
>> the C: to E: drive?
>> Thank You,
>>
>>
>|||This is a multi-part message in MIME format.
--=_NextPart_000_0368_01C66B08.B9F88460
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
Hi Tim
Just FYI, you do not have to move any files to move tempdb, because =tempdb is rebuilt every time SQL Server starts.
Do you have the latest BOL? There was a refresh just a week ago.
G. Moving tempdb to a new location
The following example moves tempdb from its current location on the disk =to another disk location. Because tempdb is re-created each time the =MSSQLSERVER service is started, you do not have to physically move the =data and log files. The files are created when the service is restarted =in step 3. Until the service is restarted, tempdb continues to function =in its existing location.
1.. Determine the logical file names of the tempdb database and their =current location on disk.
SELECT name, physical_name
FROM sys.master_files
WHERE database_id =3D DB_ID('tempdb');
GO
2.. Change the location of each file by using ALTER DATABASE.
USE master;
GO
ALTER DATABASE tempdb MODIFY FILE (NAME =3D tempdev, FILENAME =3D 'E:\SQLData\tempdb.mdf');
GO
ALTER DATABASE tempdb MODIFY FILE (NAME =3D templog, FILENAME =3D 'E:\SQLData\templog.ldf');
GO
3.. Stop and restart the instance of SQL Server.
4.. Verify the file change.
SELECT name, physical_name
FROM sys.master_files
WHERE database_id =3D DB_ID('tempdb');
5.. Delete the tempdb.mdf and templog.ldf files from their original =location.
-- HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"Tim Greenwood" <tim_greenwood A-T yahoo D-O-T com> wrote in message =news:%23TTfa5zaGHA.1196@.TK2MSFTNGP03.phx.gbl...
>I was doing the steps as outlined in BOL...and I quote from BOL
> > G. Moving tempdb to a new location
> The following example moves tempdb from its current location on disk =to > another disk location.
> > 1.. Determine the logical file names of the tempdb database and their =
> current location on disk.
> > > > SELECT name, physical_name
> FROM sys.master_files
> WHERE database_id =3D DB_ID('tempdb');
> GO
> > 2.. Physically move these files to the new location E:\SQLData.
> > > > In this example the new location specified for the tempdb files =is > E:\SQLData. If you move the files to different location, modify the =example > to point to that location.
> > > > 3.. Change the location of each file by using ALTER DATABASE.
> > 4.. Stop and restart the instance of SQL Server.
> > > 5.. Verify the file change.
> > > > > "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message > news:uG0CSzxaGHA.1192@.TK2MSFTNGP03.phx.gbl...
>> Hi Tim
>> What exactly are you doing in step 2? You shouldn't have to move =anything, >> since the tempdb files are recreated upon system startup. To move =tempdb >> you just need to run ALTER DATABASE and then restart your SQL Server.
>> -- >> HTH
>> Kalen Delaney, SQL Server MVP
>> www.solidqualitylearning.com
>>
>> "Tim Greenwood" <tim_greenwood A-T yahoo D-O-T com> wrote in message >> news:uRzZ4dxaGHA.3408@.TK2MSFTNGP04.phx.gbl...
>> Here ya go....BOL...just tried it and it worked great.
>> 1. stop sql server
>> 2. copy to your desired location
>> 3. start sql server
>> 4. run the alter statements below
>> 5. restart sql server
>> USE master;
>> GO
>> ALTER DATABASE tempdb
>> MODIFY FILE (NAME =3D tempdev, FILENAME =3D ='E:\SQLData\tempdb.mdf');
>> GO
>> ALTER DATABASE tempdb
>> MODIFY FILE (NAME =3D templog, FILENAME =3D ='E:\SQLData\templog.ldf');
>> GO
>> "Tim Greenwood" <tim_greenwood A-T yahoo D-O-T com> wrote in message =
>> news:uPtIzWxaGHA.2124@.TK2MSFTNGP03.phx.gbl...
>> I'm very interested in this as well. We were planning on doing =this >> first thing Saturday morning.
>>
>> "Joe K." <Joe K.@.discussions.microsoft.com> wrote in message >> news:D7969E35-E4D2-4D22-A9BB-DD7B52EA6E2D@.microsoft.com...
>> I have a SQL Server 2000 database server on Windows 2000 Advanced >> Server.
>> What is the best way to move the TempDB data and transaction log =files >> from
>> the C: to E: drive?
>> Thank You,
>>
>>
>> > >
--=_NextPart_000_0368_01C66B08.B9F88460
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

---

Hi Tim
Just FYI, you do not have to move any =files to move tempdb, because tempdb is rebuilt every time SQL Server =starts.
Do you have the latest BOL? There was a =refresh just a week ago.
G. Moving =tempdb to a new location
http://ddue.schemas.microsoft.com/authoring/2003/5">
The following example moves =tempdb from its current location on the disk to another disk location. Because tempdb is re-created each time the MSSQLSERVER =service is started, you do not have to physically move the data and log =files. The files are created when the service is restarted in step 3. Until the =service is restarted, tempdb continues to function in its existing =location.
Determine the logical file names of =the tempdb database and their current location on disk.
SELECT name, =physical_name
FROM sys.master_files
WHERE database_id =3D DB_ID('tempdb');
GO
Change the location of each file by =using ALTER DATABASE.
USE master;
GO
ALTER DATABASE tempdb MODIFY FILE (NAME =3D tempdev, FILENAME =3D 'E:\SQLData\tempdb.mdf');
GO
ALTER DATABASE tempdb MODIFY FILE (NAME =3D templog, FILENAME =3D 'E:\SQLData\templog.ldf');
GO
Stop and restart the instance of SQL =Server.
Verify the file change.
SELECT name, =physical_name
FROM sys.master_files
WHERE database_id =3D =DB_ID('tempdb');
Delete the tempdb.mdf and templog.ldf =files from their original location.
-- HTHKalen Delaney, SQL =Server MVPwww.solidqualitylearning.com
"Tim Greenwood" wrote in message news:%23TTfa5zaGHA.1196@.TK2MSFTNGP03.phx.gbl...>I was doing the =steps as outlined in BOL...and I quote from BOL> > G. Moving =tempdb to a new location> The following example moves tempdb from its current =location on disk to > another disk location.> => 1.. Determine the logical file names of the tempdb database and their => current location on disk.> > > > SELECT =name, physical_name> FROM sys.master_files> WHERE database_id ==3D DB_ID('tempdb');> GO> > 2.. Physically move =these files to the new location E:\SQLData.> > > > In this example the =new location specified for the tempdb files is > E:\SQLData. If you =move the files to different location, modify the example > to point to =that location.> > > > 3.. Change the =location of each file by using ALTER DATABASE.> > 4.. Stop and =restart the instance of SQL Server.> > > 5.. Verify =the file change.> > > > > "Kalen Delaney" wrote in message > news:uG0CSzxaGHA.1192@.TK2MSFTNGP03.phx.gbl...> Hi Tim>> What exactly are =you doing in step 2? You shouldn't have to move anything, > since the =tempdb files are recreated upon system startup. To move tempdb > you =just need to run ALTER DATABASE and then restart your SQL Server.>> -- > HTH> Kalen =Delaney, SQL Server MVP>www.solidqualitylearning.com>>> "Tim Greenwood" = wrote in message > news:uRzZ4dxaGHA.3408@.TK2MSFTNGP04.phx.gbl...> Here ya go....BOL...just tried it and it =worked great.> 1. stop sql server> 2. copy to =your desired location> 3. start sql server> 4. =run the alter statements below> 5. restart sql server>> USE master;> GO> ALTER DATABASE tempdb> MODIFY FILE =(NAME =3D tempdev, FILENAME =3D 'E:\SQLData\tempdb.mdf');> =GO> ALTER DATABASE tempdb> MODIFY FILE (NAME =3D =templog, FILENAME =3D 'E:\SQLData\templog.ldf');> GO>> "Tim Greenwood" wrote in message > news:uPtIzWxaGHA.2124@.TK2MSFTNGP03.phx.gbl...> I'm very interested in this as =well. We were planning on doing this > first thing Saturday morning.>>>&=gt;>> "Joe K." wrote in message > news:D7969E35-E4D2-4D22-A9BB-DD7B52EA6E2D@.microsoft.com...>> I have a SQL Server 2000 database server on Windows 2000 Advanced > =Server.>> What is the best way to move the TempDB data and transaction log files > from> the C: to E: drive?>> Thank You,>>>>&=gt;>> > >

---

--=_NextPart_000_0368_01C66B08.B9F88460--sql

Move TempDB data/trans From C: to E:

I have a SQL Server 2000 database server on Windows 2000 Advanced Server.
What is the best way to move the TempDB data and transaction log files from
the C: to E: drive?
Thank You,I'm very interested in this as well. We were planning on doing this first
thing Saturday morning.
"Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
news:D7969E35-E4D2-4D22-A9BB-DD7B52EA6E2D@.microsoft.com...
> I have a SQL Server 2000 database server on Windows 2000 Advanced Server.
> What is the best way to move the TempDB data and transaction log files
> from
> the C: to E: drive?
> Thank You,|||Here ya go....BOL...just tried it and it worked great.
1. stop sql server
2. copy to your desired location
3. start sql server
4. run the alter statements below
5. restart sql server
USE master;
GO
ALTER DATABASE tempdb
MODIFY FILE (NAME = tempdev, FILENAME = 'E:\SQLData\tempdb.mdf');
GO
ALTER DATABASE tempdb
MODIFY FILE (NAME = templog, FILENAME = 'E:\SQLData\templog.ldf');
GO
"Tim Greenwood" <tim_greenwood A-T yahoo D-O-T com> wrote in message
news:uPtIzWxaGHA.2124@.TK2MSFTNGP03.phx.gbl...
> I'm very interested in this as well. We were planning on doing this first
> thing Saturday morning.
>
> "Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
> news:D7969E35-E4D2-4D22-A9BB-DD7B52EA6E2D@.microsoft.com...
>|||Hi Tim
What exactly are you doing in step 2? You shouldn't have to move anything,
since the tempdb files are recreated upon system startup. To move tempdb you
just need to run ALTER DATABASE and then restart your SQL Server.
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"Tim Greenwood" <tim_greenwood A-T yahoo D-O-T com> wrote in message
news:uRzZ4dxaGHA.3408@.TK2MSFTNGP04.phx.gbl...
> Here ya go....BOL...just tried it and it worked great.
> 1. stop sql server
> 2. copy to your desired location
> 3. start sql server
> 4. run the alter statements below
> 5. restart sql server
> USE master;
> GO
> ALTER DATABASE tempdb
> MODIFY FILE (NAME = tempdev, FILENAME = 'E:\SQLData\tempdb.mdf');
> GO
> ALTER DATABASE tempdb
> MODIFY FILE (NAME = templog, FILENAME = 'E:\SQLData\templog.ldf');
> GO
> "Tim Greenwood" <tim_greenwood A-T yahoo D-O-T com> wrote in message
> news:uPtIzWxaGHA.2124@.TK2MSFTNGP03.phx.gbl...
>|||I was doing the steps as outlined in BOL...and I quote from BOL
G. Moving tempdb to a new location
The following example moves tempdb from its current location on disk to
another disk location.
1.. Determine the logical file names of the tempdb database and their
current location on disk.
SELECT name, physical_name
FROM sys.master_files
WHERE database_id = DB_ID('tempdb');
GO
2.. Physically move these files to the new location E:\SQLData.
In this example the new location specified for the tempdb files is
E:\SQLData. If you move the files to different location, modify the example
to point to that location.
3.. Change the location of each file by using ALTER DATABASE.
4.. Stop and restart the instance of SQL Server.
5.. Verify the file change.
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:uG0CSzxaGHA.1192@.TK2MSFTNGP03.phx.gbl...
> Hi Tim
> What exactly are you doing in step 2? You shouldn't have to move anything,
> since the tempdb files are recreated upon system startup. To move tempdb
> you just need to run ALTER DATABASE and then restart your SQL Server.
> --
> HTH
> Kalen Delaney, SQL Server MVP
> www.solidqualitylearning.com
>
> "Tim Greenwood" <tim_greenwood A-T yahoo D-O-T com> wrote in message
> news:uRzZ4dxaGHA.3408@.TK2MSFTNGP04.phx.gbl...
>|||Hi Tim
Just FYI, you do not have to move any files to move tempdb, because tempdb i
s rebuilt every time SQL Server starts.
Do you have the latest BOL? There was a refresh just a week ago.
G. Moving tempdb to a new location
The following example moves tempdb from its current location on the disk to
another disk location. Because tempdb is re-created each time the MSSQLSERVE
R service is started, you do not have to physically move the data and log fi
les. The files are created when the service is restarted in step 3. Until th
e service is restarted, tempdb continues to function in its existing locatio
n.
1.. Determine the logical file names of the tempdb database and their curren
t location on disk.
SELECT name, physical_name
FROM sys.master_files
WHERE database_id = DB_ID('tempdb');
GO
2.. Change the location of each file by using ALTER DATABASE.
USE master;
GO
ALTER DATABASE tempdb
MODIFY FILE (NAME = tempdev, FILENAME = 'E:\SQLData\tempdb.mdf');
GO
ALTER DATABASE tempdb
MODIFY FILE (NAME = templog, FILENAME = 'E:\SQLData\templog.ldf');
GO
3.. Stop and restart the instance of SQL Server.
4.. Verify the file change.
SELECT name, physical_name
FROM sys.master_files
WHERE database_id = DB_ID('tempdb');
5.. Delete the tempdb.mdf and templog.ldf files from their original location
.
--
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"Tim Greenwood" <tim_greenwood A-T yahoo D-O-T com> wrote in message news:%23TTfa5zaGHA.1196
@.TK2MSFTNGP03.phx.gbl...
>I was doing the steps as outlined in BOL...and I quote from BOL
>
> G. Moving tempdb to a new location
> The following example moves tempdb from its current location on disk to
> another disk location.
>
> 1.. Determine the logical file names of the tempdb database and their
> current location on disk.
>
>
>
> SELECT name, physical_name
> FROM sys.master_files
> WHERE database_id = DB_ID('tempdb');
> GO
>
> 2.. Physically move these files to the new location E:\SQLData.
>
>
>
> In this example the new location specified for the tempdb files is
> E:\SQLData. If you move the files to different location, modify the exampl
e
> to point to that location.
>
>
>
> 3.. Change the location of each file by using ALTER DATABASE.
>
> 4.. Stop and restart the instance of SQL Server.
>
>
> 5.. Verify the file change.
>
>
>
>
> "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
> news:uG0CSzxaGHA.1192@.TK2MSFTNGP03.phx.gbl...
>
>

Move Database and Relocate Transaction Logs

Hello,
We have just built a Windows 2000 Advance Server with SQL Server 2000
Enterprise Edition. We would like to move some databases off of an old SQL
2000 Server onto this new server.
Questions:
What is the proper procedure for moving these databases over?
Once the databases are transferred, how can we relocate all of the
transaction logs (master, model, tempdb and databases we have created) to
another drive?
Thanks,
JBaileyCheckout the Copy Database Wizard, you can specify where
the files get copied to. It doesn't copy system
databases, but should grab most relevant material from
them.
>--Original Message--
>Hello,
>We have just built a Windows 2000 Advance Server with SQL
Server 2000
>Enterprise Edition. We would like to move some databases
off of an old SQL
>2000 Server onto this new server.
>Questions:
>What is the proper procedure for moving these databases
over?
>Once the databases are transferred, how can we relocate
all of the
>transaction logs (master, model, tempdb and databases we
have created) to
>another drive?
>Thanks,
>JBailey
>
>.
>|||Best place to start is reading 224071. Also, CDW as recommended by .. is an option.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as ugroup=microsoft.public.sqlserver
"JBailey" <abc@.123.com> wrote in message news:ucrE451kDHA.2424@.TK2MSFTNGP10.phx.gbl...
> Hello,
> We have just built a Windows 2000 Advance Server with SQL Server 2000
> Enterprise Edition. We would like to move some databases off of an old SQL
> 2000 Server onto this new server.
> Questions:
> What is the proper procedure for moving these databases over?
> Once the databases are transferred, how can we relocate all of the
> transaction logs (master, model, tempdb and databases we have created) to
> another drive?
> Thanks,
> JBailey
>|||Tibor,
224071 ? I cant seem to find that, have a link?
Thanks,
"Tibor Karaszi" <tibor.please_reply_to_public_forum.karaszi@.cornerstone.se>
wrote in message news:ehGA9E7kDHA.2012@.TK2MSFTNGP12.phx.gbl...
> Best place to start is reading 224071. Also, CDW as recommended by .. is
an option.
> --
> Tibor Karaszi, SQL Server MVP
> Archive at: http://groups.google.com/groups?oi=djq&as
ugroup=microsoft.public.sqlserver
>
> "JBailey" <abc@.123.com> wrote in message
news:ucrE451kDHA.2424@.TK2MSFTNGP10.phx.gbl...
> > Hello,
> >
> > We have just built a Windows 2000 Advance Server with SQL Server 2000
> > Enterprise Edition. We would like to move some databases off of an old
SQL
> > 2000 Server onto this new server.
> >
> > Questions:
> >
> > What is the proper procedure for moving these databases over?
> > Once the databases are transferred, how can we relocate all of the
> > transaction logs (master, model, tempdb and databases we have created)
to
> > another drive?
> >
> > Thanks,
> >
> > JBailey
> >
> >
>|||Found 224071.
Questions:
Is it necessary to move the master, model, and tempdb over? Or can I just
move the databases we created? Is there dependencies user created databases
have on the default SQL databases that I should be aware of?
Thanks,
JBailey
"Tibor Karaszi" <tibor.please_reply_to_public_forum.karaszi@.cornerstone.se>
wrote in message news:ehGA9E7kDHA.2012@.TK2MSFTNGP12.phx.gbl...
> Best place to start is reading 224071. Also, CDW as recommended by .. is
an option.
> --
> Tibor Karaszi, SQL Server MVP
> Archive at: http://groups.google.com/groups?oi=djq&as
ugroup=microsoft.public.sqlserver
>
> "JBailey" <abc@.123.com> wrote in message
news:ucrE451kDHA.2424@.TK2MSFTNGP10.phx.gbl...
> > Hello,
> >
> > We have just built a Windows 2000 Advance Server with SQL Server 2000
> > Enterprise Edition. We would like to move some databases off of an old
SQL
> > 2000 Server onto this new server.
> >
> > Questions:
> >
> > What is the proper procedure for moving these databases over?
> > Once the databases are transferred, how can we relocate all of the
> > transaction logs (master, model, tempdb and databases we have created)
to
> > another drive?
> >
> > Thanks,
> >
> > JBailey
> >
> >
>|||You can to consider what is stored in the system databases. In master, you have logins, linked
servers, sysmessages, sp_configure. In msdb, you have jobs, operators, alerts.
Only you can say whether above is a too huge job to do manually compared to try to get backup etc
over to the new server.
As for logins (which generally is the main issue), read in books online about sp_change_users_login
and you might want to use the GUI tool at www.dbmaint.com (free utilities).
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as ugroup=microsoft.public.sqlserver
"JBailey" <abc@.123.com> wrote in message news:OXwiR3%23kDHA.2364@.TK2MSFTNGP11.phx.gbl...
> Found 224071.
> Questions:
> Is it necessary to move the master, model, and tempdb over? Or can I just
> move the databases we created? Is there dependencies user created databases
> have on the default SQL databases that I should be aware of?
> Thanks,
> JBailey
>
> "Tibor Karaszi" <tibor.please_reply_to_public_forum.karaszi@.cornerstone.se>
> wrote in message news:ehGA9E7kDHA.2012@.TK2MSFTNGP12.phx.gbl...
> > Best place to start is reading 224071. Also, CDW as recommended by .. is
> an option.
> >
> > --
> > Tibor Karaszi, SQL Server MVP
> > Archive at: http://groups.google.com/groups?oi=djq&as
> ugroup=microsoft.public.sqlserver
> >
> >
> > "JBailey" <abc@.123.com> wrote in message
> news:ucrE451kDHA.2424@.TK2MSFTNGP10.phx.gbl...
> > > Hello,
> > >
> > > We have just built a Windows 2000 Advance Server with SQL Server 2000
> > > Enterprise Edition. We would like to move some databases off of an old
> SQL
> > > 2000 Server onto this new server.
> > >
> > > Questions:
> > >
> > > What is the proper procedure for moving these databases over?
> > > Once the databases are transferred, how can we relocate all of the
> > > transaction logs (master, model, tempdb and databases we have created)
> to
> > > another drive?
> > >
> > > Thanks,
> > >
> > > JBailey
> > >
> > >
> >
> >
>

Moving indexes on another physical drive

I have SQL Server, which quite often have 200 transaction/second. It is
expected what the load, during this year, would be increased up to 1000
transactions per second. The database relatively small, can be loaded in
memory. All tables which often used by queries pinned in memory, with
'pintable' option. IO system - DELL CX300, with write cache enabled. I have 3
disks, which can be used for SQL Server files. Currently I use one 2 of them,
1 for data file and 1 for log file. I try to find how to use another disk to
speed up queries and to have more even distribution of transaction times. I
think to move indexes on another physical drive, so data would be on one
disk, and indexes on another. I guess it would reduce average and maximum
wait time on latches during checkpoints. Is it true, what can be expected
from this movement of indexes?
Another idea which I have - use two drives for data, one for log. In this
case I would create filegroup whih would include two data files on different
drives, and data would be evenly distributed across drives. Is it better in
terms of performance and to have more even distribution of transaction times?
May be there is better way to use 3dr drive?
Hi
Have you looked at your disk queues?
Are your data and logs, on seperate RAID-10 volumes, and each RAID-10 volume
on different spindles?
On some of our larger systems, we have seen pinning a table actually
degraded performance as the tables were taking space that could have better
been used for dirty buffers.
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"andsm" wrote:

> I have SQL Server, which quite often have 200 transaction/second. It is
> expected what the load, during this year, would be increased up to 1000
> transactions per second. The database relatively small, can be loaded in
> memory. All tables which often used by queries pinned in memory, with
> 'pintable' option. IO system - DELL CX300, with write cache enabled. I have 3
> disks, which can be used for SQL Server files. Currently I use one 2 of them,
> 1 for data file and 1 for log file. I try to find how to use another disk to
> speed up queries and to have more even distribution of transaction times. I
> think to move indexes on another physical drive, so data would be on one
> disk, and indexes on another. I guess it would reduce average and maximum
> wait time on latches during checkpoints. Is it true, what can be expected
> from this movement of indexes?
> Another idea which I have - use two drives for data, one for log. In this
> case I would create filegroup whih would include two data files on different
> drives, and data would be evenly distributed across drives. Is it better in
> terms of performance and to have more even distribution of transaction times?
> May be there is better way to use 3dr drive?
|||Hi
You can move heavy quering table/s to another physical disk , please note
physical not logical.
You are right , separating LOG and DATA files will improve the performance
of the database.
If your database is small and the tables are small as well why do you want
at least at this moment to move the tabes/indexes, did you start to get slow
response?
"andsm" <andsm@.discussions.microsoft.com> wrote in message
news:7988A160-301C-4BE2-A7FC-9B9090E62831@.microsoft.com...
> I have SQL Server, which quite often have 200 transaction/second. It is
> expected what the load, during this year, would be increased up to 1000
> transactions per second. The database relatively small, can be loaded in
> memory. All tables which often used by queries pinned in memory, with
> 'pintable' option. IO system - DELL CX300, with write cache enabled. I
have 3
> disks, which can be used for SQL Server files. Currently I use one 2 of
them,
> 1 for data file and 1 for log file. I try to find how to use another disk
to
> speed up queries and to have more even distribution of transaction times.
I
> think to move indexes on another physical drive, so data would be on one
> disk, and indexes on another. I guess it would reduce average and maximum
> wait time on latches during checkpoints. Is it true, what can be expected
> from this movement of indexes?
> Another idea which I have - use two drives for data, one for log. In this
> case I would create filegroup whih would include two data files on
different
> drives, and data would be evenly distributed across drives. Is it better
in
> terms of performance and to have more even distribution of transaction
times?
> May be there is better way to use 3dr drive?
|||More importantly than what the others have suggested, when you say current
activity is 200 TPS and future activity is 1,000 TPS, that is a pretty big
jump.
What is the nature of this activity? 50% Reads/50% Writes? 75% Reads/25%
Writes? 25% Reads/75% Writes?
Can you tell what the current I/O OPS are? What is the projection?
A single physical disk (1 spindle, dedicated) can achieve perhaps 100-200
I/O OPS, depending on the R/W and physical disk metrics. For this much
activity, you do not want to drive this disk much above 65% on average.
Every transaction will involve a serial log write and a random data write,
eventually. You should have at least a sufficient number of spindles to
cover your anticipated I/O OPS, both for data and log activity.
For example, if you feel you have 300 I/O OPS, you should have at least 2;
however, especially with a dedicated storage solution, you should stripe at
least six spindles per LUN, at a minimum. If you can't achieve the minimum,
then you may consider a technique called Disk Plaiding, which is striping
your LUN at the hardware level, but then do as you suggest, and multi-file
each SQL Server filegroup. This way you have hardware striping and DBMS
striping across those (a Plaid...checkerboard pattern) of distributed data.
The LUN striping should be Raid, of course, or mirrored sets of
Hyper-volumes, called Meta-volumes. This is effectively RAID 10, but know
that hyper-volumes are only a slice of a disk and any single disk (1
spindle) could be shared out, hyper by hyper, to several hosts. You must
take this into account when trying to count I/O OPS per spindle.
You should really work with your Storage Management to communicate the
capacity requirements. Barring that, contact your storage technical rep.
Be careful though, many will tell you that it doesn't matter and try to give
you one big volume. It matters and they are full of hooey. All of the
major storage providers and Microsoft publish white papers on how to set up
external storage for DBMS support. Use those.
Sincerely,
Anthony Thomas

"andsm" <andsm@.discussions.microsoft.com> wrote in message
news:7988A160-301C-4BE2-A7FC-9B9090E62831@.microsoft.com...
I have SQL Server, which quite often have 200 transaction/second. It is
expected what the load, during this year, would be increased up to 1000
transactions per second. The database relatively small, can be loaded in
memory. All tables which often used by queries pinned in memory, with
'pintable' option. IO system - DELL CX300, with write cache enabled. I have
3
disks, which can be used for SQL Server files. Currently I use one 2 of
them,
1 for data file and 1 for log file. I try to find how to use another disk to
speed up queries and to have more even distribution of transaction times. I
think to move indexes on another physical drive, so data would be on one
disk, and indexes on another. I guess it would reduce average and maximum
wait time on latches during checkpoints. Is it true, what can be expected
from this movement of indexes?
Another idea which I have - use two drives for data, one for log. In this
case I would create filegroup whih would include two data files on different
drives, and data would be evenly distributed across drives. Is it better in
terms of performance and to have more even distribution of transaction
times?
May be there is better way to use 3dr drive?

Moving indexes on another physical drive

I have SQL Server, which quite often have 200 transaction/second. It is
expected what the load, during this year, would be increased up to 1000
transactions per second. The database relatively small, can be loaded in
memory. All tables which often used by queries pinned in memory, with
'pintable' option. IO system - DELL CX300, with write cache enabled. I have 3
disks, which can be used for SQL Server files. Currently I use one 2 of them,
1 for data file and 1 for log file. I try to find how to use another disk to
speed up queries and to have more even distribution of transaction times. I
think to move indexes on another physical drive, so data would be on one
disk, and indexes on another. I guess it would reduce average and maximum
wait time on latches during checkpoints. Is it true, what can be expected
from this movement of indexes?
Another idea which I have - use two drives for data, one for log. In this
case I would create filegroup whih would include two data files on different
drives, and data would be evenly distributed across drives. Is it better in
terms of performance and to have more even distribution of transaction times?
May be there is better way to use 3dr drive?Hi
Have you looked at your disk queues?
Are your data and logs, on seperate RAID-10 volumes, and each RAID-10 volume
on different spindles?
On some of our larger systems, we have seen pinning a table actually
degraded performance as the tables were taking space that could have better
been used for dirty buffers.
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"andsm" wrote:
> I have SQL Server, which quite often have 200 transaction/second. It is
> expected what the load, during this year, would be increased up to 1000
> transactions per second. The database relatively small, can be loaded in
> memory. All tables which often used by queries pinned in memory, with
> 'pintable' option. IO system - DELL CX300, with write cache enabled. I have 3
> disks, which can be used for SQL Server files. Currently I use one 2 of them,
> 1 for data file and 1 for log file. I try to find how to use another disk to
> speed up queries and to have more even distribution of transaction times. I
> think to move indexes on another physical drive, so data would be on one
> disk, and indexes on another. I guess it would reduce average and maximum
> wait time on latches during checkpoints. Is it true, what can be expected
> from this movement of indexes?
> Another idea which I have - use two drives for data, one for log. In this
> case I would create filegroup whih would include two data files on different
> drives, and data would be evenly distributed across drives. Is it better in
> terms of performance and to have more even distribution of transaction times?
> May be there is better way to use 3dr drive?|||Hi
You can move heavy quering table/s to another physical disk , please note
physical not logical.
You are right , separating LOG and DATA files will improve the performance
of the database.
If your database is small and the tables are small as well why do you want
at least at this moment to move the tabes/indexes, did you start to get slow
response?
"andsm" <andsm@.discussions.microsoft.com> wrote in message
news:7988A160-301C-4BE2-A7FC-9B9090E62831@.microsoft.com...
> I have SQL Server, which quite often have 200 transaction/second. It is
> expected what the load, during this year, would be increased up to 1000
> transactions per second. The database relatively small, can be loaded in
> memory. All tables which often used by queries pinned in memory, with
> 'pintable' option. IO system - DELL CX300, with write cache enabled. I
have 3
> disks, which can be used for SQL Server files. Currently I use one 2 of
them,
> 1 for data file and 1 for log file. I try to find how to use another disk
to
> speed up queries and to have more even distribution of transaction times.
I
> think to move indexes on another physical drive, so data would be on one
> disk, and indexes on another. I guess it would reduce average and maximum
> wait time on latches during checkpoints. Is it true, what can be expected
> from this movement of indexes?
> Another idea which I have - use two drives for data, one for log. In this
> case I would create filegroup whih would include two data files on
different
> drives, and data would be evenly distributed across drives. Is it better
in
> terms of performance and to have more even distribution of transaction
times?
> May be there is better way to use 3dr drive?|||More importantly than what the others have suggested, when you say current
activity is 200 TPS and future activity is 1,000 TPS, that is a pretty big
jump.
What is the nature of this activity? 50% Reads/50% Writes? 75% Reads/25%
Writes? 25% Reads/75% Writes?
Can you tell what the current I/O OPS are? What is the projection?
A single physical disk (1 spindle, dedicated) can achieve perhaps 100-200
I/O OPS, depending on the R/W and physical disk metrics. For this much
activity, you do not want to drive this disk much above 65% on average.
Every transaction will involve a serial log write and a random data write,
eventually. You should have at least a sufficient number of spindles to
cover your anticipated I/O OPS, both for data and log activity.
For example, if you feel you have 300 I/O OPS, you should have at least 2;
however, especially with a dedicated storage solution, you should stripe at
least six spindles per LUN, at a minimum. If you can't achieve the minimum,
then you may consider a technique called Disk Plaiding, which is striping
your LUN at the hardware level, but then do as you suggest, and multi-file
each SQL Server filegroup. This way you have hardware striping and DBMS
striping across those (a Plaid...checkerboard pattern) of distributed data.
The LUN striping should be Raid, of course, or mirrored sets of
Hyper-volumes, called Meta-volumes. This is effectively RAID 10, but know
that hyper-volumes are only a slice of a disk and any single disk (1
spindle) could be shared out, hyper by hyper, to several hosts. You must
take this into account when trying to count I/O OPS per spindle.
You should really work with your Storage Management to communicate the
capacity requirements. Barring that, contact your storage technical rep.
Be careful though, many will tell you that it doesn't matter and try to give
you one big volume. It matters and they are full of hooey. All of the
major storage providers and Microsoft publish white papers on how to set up
external storage for DBMS support. Use those.
Sincerely,
Anthony Thomas
"andsm" <andsm@.discussions.microsoft.com> wrote in message
news:7988A160-301C-4BE2-A7FC-9B9090E62831@.microsoft.com...
I have SQL Server, which quite often have 200 transaction/second. It is
expected what the load, during this year, would be increased up to 1000
transactions per second. The database relatively small, can be loaded in
memory. All tables which often used by queries pinned in memory, with
'pintable' option. IO system - DELL CX300, with write cache enabled. I have
3
disks, which can be used for SQL Server files. Currently I use one 2 of
them,
1 for data file and 1 for log file. I try to find how to use another disk to
speed up queries and to have more even distribution of transaction times. I
think to move indexes on another physical drive, so data would be on one
disk, and indexes on another. I guess it would reduce average and maximum
wait time on latches during checkpoints. Is it true, what can be expected
from this movement of indexes?
Another idea which I have - use two drives for data, one for log. In this
case I would create filegroup whih would include two data files on different
drives, and data would be evenly distributed across drives. Is it better in
terms of performance and to have more even distribution of transaction
times?
May be there is better way to use 3dr drive?

Moving indexes on another physical drive

I have SQL Server, which quite often have 200 transaction/second. It is
expected what the load, during this year, would be increased up to 1000
transactions per second. The database relatively small, can be loaded in
memory. All tables which often used by queries pinned in memory, with
'pintable' option. IO system - DELL CX300, with write cache enabled. I have
3
disks, which can be used for SQL Server files. Currently I use one 2 of them
,
1 for data file and 1 for log file. I try to find how to use another disk to
speed up queries and to have more even distribution of transaction times. I
think to move indexes on another physical drive, so data would be on one
disk, and indexes on another. I guess it would reduce average and maximum
wait time on latches during checkpoints. Is it true, what can be expected
from this movement of indexes?
Another idea which I have - use two drives for data, one for log. In this
case I would create filegroup whih would include two data files on different
drives, and data would be evenly distributed across drives. Is it better in
terms of performance and to have more even distribution of transaction times
?
May be there is better way to use 3dr drive?Hi
Have you looked at your disk queues?
Are your data and logs, on seperate RAID-10 volumes, and each RAID-10 volume
on different spindles?
On some of our larger systems, we have seen pinning a table actually
degraded performance as the tables were taking space that could have better
been used for dirty buffers.
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"andsm" wrote:

> I have SQL Server, which quite often have 200 transaction/second. It is
> expected what the load, during this year, would be increased up to 1000
> transactions per second. The database relatively small, can be loaded in
> memory. All tables which often used by queries pinned in memory, with
> 'pintable' option. IO system - DELL CX300, with write cache enabled. I hav
e 3
> disks, which can be used for SQL Server files. Currently I use one 2 of th
em,
> 1 for data file and 1 for log file. I try to find how to use another disk
to
> speed up queries and to have more even distribution of transaction times.
I
> think to move indexes on another physical drive, so data would be on one
> disk, and indexes on another. I guess it would reduce average and maximum
> wait time on latches during checkpoints. Is it true, what can be expected
> from this movement of indexes?
> Another idea which I have - use two drives for data, one for log. In this
> case I would create filegroup whih would include two data files on differe
nt
> drives, and data would be evenly distributed across drives. Is it better i
n
> terms of performance and to have more even distribution of transaction tim
es?
> May be there is better way to use 3dr drive?|||Hi
You can move heavy quering table/s to another physical disk , please note
physical not logical.
You are right , separating LOG and DATA files will improve the performance
of the database.
If your database is small and the tables are small as well why do you want
at least at this moment to move the tabes/indexes, did you start to get slow
response?
"andsm" <andsm@.discussions.microsoft.com> wrote in message
news:7988A160-301C-4BE2-A7FC-9B9090E62831@.microsoft.com...
> I have SQL Server, which quite often have 200 transaction/second. It is
> expected what the load, during this year, would be increased up to 1000
> transactions per second. The database relatively small, can be loaded in
> memory. All tables which often used by queries pinned in memory, with
> 'pintable' option. IO system - DELL CX300, with write cache enabled. I
have 3
> disks, which can be used for SQL Server files. Currently I use one 2 of
them,
> 1 for data file and 1 for log file. I try to find how to use another disk
to
> speed up queries and to have more even distribution of transaction times.
I
> think to move indexes on another physical drive, so data would be on one
> disk, and indexes on another. I guess it would reduce average and maximum
> wait time on latches during checkpoints. Is it true, what can be expected
> from this movement of indexes?
> Another idea which I have - use two drives for data, one for log. In this
> case I would create filegroup whih would include two data files on
different
> drives, and data would be evenly distributed across drives. Is it better
in
> terms of performance and to have more even distribution of transaction
times?
> May be there is better way to use 3dr drive?|||More importantly than what the others have suggested, when you say current
activity is 200 TPS and future activity is 1,000 TPS, that is a pretty big
jump.
What is the nature of this activity? 50% Reads/50% Writes? 75% Reads/25%
Writes? 25% Reads/75% Writes?
Can you tell what the current I/O OPS are? What is the projection?
A single physical disk (1 spindle, dedicated) can achieve perhaps 100-200
I/O OPS, depending on the R/W and physical disk metrics. For this much
activity, you do not want to drive this disk much above 65% on average.
Every transaction will involve a serial log write and a random data write,
eventually. You should have at least a sufficient number of spindles to
cover your anticipated I/O OPS, both for data and log activity.
For example, if you feel you have 300 I/O OPS, you should have at least 2;
however, especially with a dedicated storage solution, you should stripe at
least six spindles per LUN, at a minimum. If you can't achieve the minimum,
then you may consider a technique called Disk Plaiding, which is striping
your LUN at the hardware level, but then do as you suggest, and multi-file
each SQL Server filegroup. This way you have hardware striping and DBMS
striping across those (a Plaid...checkerboard pattern) of distributed data.
The LUN striping should be Raid, of course, or mirrored sets of
Hyper-volumes, called Meta-volumes. This is effectively RAID 10, but know
that hyper-volumes are only a slice of a disk and any single disk (1
spindle) could be shared out, hyper by hyper, to several hosts. You must
take this into account when trying to count I/O OPS per spindle.
You should really work with your Storage Management to communicate the
capacity requirements. Barring that, contact your storage technical rep.
Be careful though, many will tell you that it doesn't matter and try to give
you one big volume. It matters and they are full of hooey. All of the
major storage providers and Microsoft publish white papers on how to set up
external storage for DBMS support. Use those.
Sincerely,
Anthony Thomas
"andsm" <andsm@.discussions.microsoft.com> wrote in message
news:7988A160-301C-4BE2-A7FC-9B9090E62831@.microsoft.com...
I have SQL Server, which quite often have 200 transaction/second. It is
expected what the load, during this year, would be increased up to 1000
transactions per second. The database relatively small, can be loaded in
memory. All tables which often used by queries pinned in memory, with
'pintable' option. IO system - DELL CX300, with write cache enabled. I have
3
disks, which can be used for SQL Server files. Currently I use one 2 of
them,
1 for data file and 1 for log file. I try to find how to use another disk to
speed up queries and to have more even distribution of transaction times. I
think to move indexes on another physical drive, so data would be on one
disk, and indexes on another. I guess it would reduce average and maximum
wait time on latches during checkpoints. Is it true, what can be expected
from this movement of indexes?
Another idea which I have - use two drives for data, one for log. In this
case I would create filegroup whih would include two data files on different
drives, and data would be evenly distributed across drives. Is it better in
terms of performance and to have more even distribution of transaction
times?
May be there is better way to use 3dr drive?