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

Steve,

Once the log is separated from the data (as in your case), there is no way
(as far a I know) to get rid of that log allocation.

What you did was quite simply to add another device to be used for
transaction log allocation. The old one will still be used.

Moving the exising physically can be done with mirror/unmirror (keeping the
new mirror).

Theoretically, I suppose, you could update the sysusages table and "mark"
the old "logdevice" to be used for dataallocation (by setting segmap to 3),
but it will till be in use - for data allocation.
I haven't attempted to try this, and I wouldn't recommend it.

Creating a new database and Transfer data is an option, depending on your
situation. Sp_renamedb can be useful in this case.

HTH
--

MCSE, MCSD, MCT, SQL Server MVP
Cornerstone Sweden AB

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

>  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