Very Computer

We have SQL 6.5 with SP#3 installed and wanted to move the transaction log.

MYDATABASE       ;the daabase name
OLD_LOG               ;The old log device
NEW_LOG              ;The new log device

 The transaction log was already on a seperate device from the database so
we followed the directions for moving a trasaction log in MS SQL Server
Administrators Companion as follows:

1. Create new device  'NEW_LOG'

2. Ran sp_log MYDATABASE,NEW_LOG      and the response was as follows:

No action needed or taken, because all fragments for database 'MYDATABASE'
on device 'NEW_LOG' are already dedicated for log usage only.

(This didn't sound really good but we continued anyhow...can anybody tell us
what this means?)

3. Wrote a script to insert 10,000 records into the database and then
deleted them.  We did this to complete enough transactions to fill the
extent of 8-pages (plus more than that) that the database was currently
using so that it would move to the new log device.  We ran DCCC Checktable
(syslogs) to determine that we had filled at least 8-pages (more like 1,200
pages).

4.  We are sure that all active transactions on the old database device had
completed.  We were in single user mode and there were no queries or
applications being run.

5. We dumped the transaction log.

6. Finally we ran SP_HELPLOG and the reponse was as follows...
In database 'MYDATABASE', the log starts on device 'OLD_LOG'.

We had fully expected to see the new log device getting used as in....
In database 'MYDATABASE', the log starts on device NEW_LOG'...but  this is
not hapening!

Can anyone help us with this.  Any response would be greatly appreciated

I dont know of any easy way to do this.  Why not create another database
with the appropriate
log device and use transfer manger to move all of the objects?
You can then delete the old database.

I have successfully done this by:

1. Mirroring the 'OLD_LOG' device, use a name that you normally would for a
Log file (normally mirror would be called OLD_LOCATION\OLD_LOG.MIR I would
make it NEW_LOCATION\NEW_LOG.DAT)
2. I leave this sit for a while to insure some logging takes place. (Not
sure if really necessary)
3. I 'Unmirror' the log device. One of the options when doing this is to
"Switch to Mirror Device - Replace Original Device" (This step takes a good
deal of time depending on size of the device. I have had it take over 18
hours on a 500 MEG device) It doesn't seem to be related to the actual
amount of data in the transaction log, but the size of the device.
4. You can then physically delete the "OLD_LOG device" data file.

I made sure that I had backups before I started this and that the users
were doing nothing critical to the database. But the entire process was
transparent to the users.

Steve Young

Quote:> We have SQL 6.5 with SP#3 installed and wanted to move the transaction
log.

> MYDATABASE       ;the daabase name
> OLD_LOG               ;The old log device
> NEW_LOG              ;The new log device

> <steps followed snipped>

> Can anyone help us with this.  Any response would be greatly appreciated

Steve,

I don't see any step where you actually alter the database to use the new
device. I also don't know anything about a procedure called sp_log.

If you can tell us exactly what you tried to do, we can give you a better
answer as to what is really going on.

Provide the output from
    sp_helpdb <databasename>

Kalen Delaney
MCSE, SQL Server MCT, MVP

Steve,

 What are you trying to achieve?  If you just want your log device on a
different disk, then you can do this by hacking sysdevices.

 Neil Pike MVP/MCSE
 Protech Computing Ltd