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
No comments:
Post a Comment