Symptom
Database has "mixed
log and data", data and log portion of the database reside on the same
device
Need to move the log portion off onto its own device
Resolution
Add a new device to hold
the transaction log and extend the database onto that device
use master
go
disk init name =
"<log device>" ...
go
alter
database<database> log on <log device> = <size>
go
Put the database in single
user mode
sp_dboption
<database>,"single user",true
go
Use the stored procedure
"sp_logdevice" to move the transaction log (This must be performed in
the
database you want to move
syslogs from)
use <database>
go
sp_logdevice<database>,<log
device>
go
Use sp_helplog
to check that the logsegment starts on '<log device>'
sp_helplog
go
No valid log device can be
found to contain the starting logpage of '<####>', ondatabase
'<db>'.
Perhaps the segment mapping
of database has changedrecently. Please inspect the sysusages catalog
and contact your
systemadministrator.
(return status = 0)
Run dummy transactions and
dump tran <dbname> with truncate_only:
Create a table with a
char(n) column where n > 50% of @@maxpagesize.
create table t1 (c1
char(1200))
go
Insert into the table 8
rows of data. Each INSERT log record will use a full log page, so 8 will be
enough to ensure we filled the current extent and cause allocation of a new extent.
insert t1 values
("a")
go 8
Drop the table.
Dump tran <dbname>
with truncate_only.
...until sp_helplog shows
output like this:
sp_helplog
go
In database
'<database>', the log starts on device '<log device>'.
(return status = 0)
Change the database status
from "mixed log and data"
dbcc traceon(3604)
go
dbcc
findstranded(<database>)
go
dbcc traceoff(3604)
go
Put the database back to
multi-user mode:
use master
go
sp_dboption
<database>, "single user",false
go
Dump the database
dump database to
"<dump device>"
go
Perform operations where data and log on separate devices is
required such as dumping transaction log.
dump transaction
<database> to "<transaction dump device>"
go