Changing Transaction Logs

Hello
I have recently completed the SQL Programming course, on
this course we were advised to have Transaction Log files
(ldf) and database files (ndf) on seperate drives.
On inspection I have found that we need to do this. Can
someone please review the following action plan and advise
if this will work? And if not can you suggest an
alternative: -
Carry out the following changes "outside of Working
hours": -
1. Backup all databases and truncate all Transaction Logs.
2. For Each user Database using Enterprise Manager,
Display Properties, Transaction Log. Select the current
Transaction Log, note the File Propeties and then delete
it.
3. Whilst still in Properties, Transaction Log. Create a
new Transaction Log, replicating the File Properties.
Many Thanks
Tony C.
Hi,
No need to do such complex tasks:-
1. Detach the database (Execute - sp_detach_db <dbname> in Query
Analyzer) -- Now the MDF , NDF and LDF will be removed from SQL server
2. Copy the LDF file to new drive
3. Attach the database back (Execute - SP_ATTACH_DB
<dbname>,'mdf_filename_with_path','Ldf_filename_wi th_path' in Query
Analyzer)
Now the database will be attached back to Sql server.
Note:
Please do this activity when there is no user connected.
Thanks
Hari
MCDBA
"Tony C" <anonymous@.discussions.microsoft.com> wrote in message
news:12a9a01c44304$40bb6690$a501280a@.phx.gbl...
> Hello
> I have recently completed the SQL Programming course, on
> this course we were advised to have Transaction Log files
> (ldf) and database files (ndf) on seperate drives.
> On inspection I have found that we need to do this. Can
> someone please review the following action plan and advise
> if this will work? And if not can you suggest an
> alternative: -
> Carry out the following changes "outside of Working
> hours": -
> 1. Backup all databases and truncate all Transaction Logs.
> 2. For Each user Database using Enterprise Manager,
> Display Properties, Transaction Log. Select the current
> Transaction Log, note the File Propeties and then delete
> it.
> 3. Whilst still in Properties, Transaction Log. Create a
> new Transaction Log, replicating the File Properties.
> Many Thanks
>
> Tony C.
|||Hi Hari
Although your script looks just fine, I don't have the
neccessary NT Network Access to physically move files from
one destination to another... Therefore I will have to
go with my original plan if it is ok... I can run all the
steps needed in SQL Server as I am the SQL Server
Administrator.
Many Thanks Anyway.
Tony C.
>--Original Message--
>Hi,
>No need to do such complex tasks:-
>1. Detach the database (Execute - sp_detach_db <dbname>
in Query
>Analyzer) -- Now the MDF , NDF and LDF will be removed
from SQL server
>2. Copy the LDF file to new drive
>3. Attach the database back (Execute - SP_ATTACH_DB
><dbname>,'mdf_filename_with_path','Ldf_filename_w ith_path'
in Query
>Analyzer)
>Now the database will be attached back to Sql server.
>Note:
>Please do this activity when there is no user connected.
>Thanks
>Hari
>MCDBA
>
>"Tony C" <anonymous@.discussions.microsoft.com> wrote in
message[vbcol=seagreen]
>news:12a9a01c44304$40bb6690$a501280a@.phx.gbl...
files[vbcol=seagreen]
advise[vbcol=seagreen]
Logs.[vbcol=seagreen]
Create a
>
>.
>