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
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"
--=_NextPart_000_0368_01C66B08.B9F88460--sql
No comments:
Post a Comment