tpreto7

Hi

I have a sybase server version 12.0.0.3 running on a solaris 5.8. I
created a database with a total size of 3048MB which of 1000MB is for
log and the remain 2048 is data, then I loaded from other database
which is exactly the same size with no problems. After I did a
sp_helpdb TEST and I notice that some of the data went into the log
device that I had created.
eg from the source database total size 3048MB
rom_data 2048.0 MB data only
rom_log 500.0 MB log only
rom_log_a 500.0 MB log only

Target after load size 3048MB
rom_data 2000.0 MB data only
rom_log_a 300.0 MB log only
rom_log_b 248.0 MB log only
row_log 48.0 MB data only
row_log 452.0 MB log only

How can I change the database so that the row log is only log and not
for data, How can I change the segment mapping on this one

Thanks
Teresa

carl kayser

Is there any harm? In itself no. There is no data corruption as a
consequence.

Will it affect performance? Possibly - for the obvious reason that you have
data and log on common devices.

What can you do? I've used SQL-BackTrack logical dumps & loads since it
avoids this problem. There are additional benefits and some possible
problems (e.g., SPs not loaded owing to common temporary (#) temp tables.)
Sysusages now has a crdate column which I believe will be of use in
eliminatng this problem in the future. Like Carl, I believe that someone
has posted a solution for recreating the db space "nicely" to avoid this
problem. I don't remember who or where but (no surprise) you have to be
careful doing it


news:<3F488070.3000100@cox.net>...

carl.doan

Being that this is a TEST platform, it all depends on what you are
testing ( functionality vs. performance, etc ). There is no harm in the
split per se, but you may end up with some potential performance
problems, depending on the load and how you dump the transaction log (
verus just truncation of the logs or having to dump the db ).

If you are wanting one of the devices to be the log device, try using
sp_logdevice, then dump the transaction and database. That should
realign some of your probs.

Note:
When you create this DB, create it in the same manner as the
source DB to avoid this problem. That is:

create database X on XYZ = 2000 log on UVW = 1000
for load

alter database X on abc = 500
for load

etc.

That should keep this from happening.

Loads of the DB loads device usage as well. Carl

ldboswell

I have created a stored proc that should generate the statements you
need to create the database properly. Create the proc and runn it; it
should give the correct create and alter statements for all yo
databases:

create proc dbo.pr_dbo_create_db_sql as

declare @db_name char(15)
declare @hold_name char(15)
declare @hold_log char(1)
declare @hold_alter char(1)
declare @dev_name char(10)
declare @frag_size char(8)
declare @seg_no char(4)
declare @frag_cnt int
declare @log_cnt int
declare @db_cnt int
declare @alter_cnt int
declare @seg_cnt int
declare @db_lstart int
declare @msg varchar(30)

select @hold_name = ' '
select @hold_alter = ' '
select @hold_log = ' '
select @frag_cnt = 0
select @log_cnt = 0
select @alter_cnt = 0
select @db_cnt = 0


create table tempdb..usages (db_db_name char(15), db_dev_name
char(10),
db_frag_size char(8), db_seg_no char(4))


declare user_tables cursor for
select convert(char(15),db_name(dbid)) ,
convert(char(10),d.name)
, convert(char(8), size/ 512), convert(char(4),segmap), lstart
from master..sysusages u, master..sysdevices d
where vstart/16777216 > 1 and vstart between low and high
and d.status <> 16
order by 1, lstart

open user_tables

fetch user_tables
into @db_name, @dev_name, @frag_size, @seg_no, @db_lstart
if (@@sqlstatus = 2)
begin
select @msg = 'no tables today'
print @msg
close user_tables
end

while (@@sqlstatus = 0)

begin
insert into tempdb..usages values(@db_name, @dev_name, @frag_size,
@seg_no)

fetch user_tables
into @db_name, @dev_name, @frag_size, @seg_no, @db_lstart
end
close user_tables

declare db_info cursor for
select * from
tempdb..usages

open db_info


fetch db_info
into @db_name, @dev_name, @frag_size, @seg_no
if (@@sqlstatus = 2)
begin
select @msg = 'no tables today'
print @msg
close user_tables
end


while (@@sqlstatus = 0) begin
if (@db_name != @hold_name and @db_cnt > 0)
begin
select @frag_cnt = 0
end


if @seg_no != '4' and @hold_log = 'Y'
begin
select @hold_log = 'N'
select @log_cnt = 0
end

if ((@seg_no != '3' and @seg_no < '8')
and @hold_alter = 'Y')
select @hold_alter = 'N'


if @db_name != @hold_name
and @db_cnt > 0
begin
select 'for load'
end
if @db_name != @hold_name
begin
select @alter_cnt = 0
select @log_cnt = 0
select @frag_cnt = 0
select @frag_cnt = @frag_cnt + 1
select @db_cnt = @db_cnt + 1
select @hold_name = @db_name
select @hold_alter = 'N'
select @hold_log = 'N'
select 'create database ' + @db_name , ' on '
, @dev_name + ' = ' + @frag_size
end
else
if (@db_name = @hold_name
and @frag_cnt > 0 and (@seg_no = '3' or @seg_no > '7')
and @hold_alter = 'N')
begin
select @frag_cnt = @frag_cnt + 1
select @alter_cnt = @alter_cnt + 1
select @hold_alter = 'Y'
select 'for load'
select 'alter database ' + @db_name , ' on '
, @dev_name + ' = ' + @frag_size
end
else
if (@db_name = @hold_name
and @frag_cnt > 1 and (@seg_no = '3' or @seg_no > '7')
and @hold_alter = 'Y')
begin
select @frag_cnt = @frag_cnt + 1
select @alter_cnt = @alter_cnt + 1
select ', '
, @dev_name + ' = ' + @frag_size
end

if (@db_name = @hold_name
and @frag_cnt > 0 and @seg_no = '4'
and @log_cnt = 0)
begin
select @frag_cnt = @frag_cnt + 1
select @log_cnt = @log_cnt + 1
select @hold_log = 'Y'
select 'log on '
, @dev_name + ' = ' + @frag_size
end
else
if (@db_name = @hold_name
and @frag_cnt > 0 and @seg_no = '4'
and @log_cnt >= 0
and @hold_log = 'Y')
begin
select @frag_cnt = @frag_cnt + 1
select @log_cnt = @log_cnt + 1
select @hold_alter = 'N'
select @hold_log = 'Y'
select ',', @dev_name + ' = ' + @frag_size
end

fetch db_info
into @db_name, @dev_name, @frag_size, @seg_no
end
close db_info
select 'for load'
drop table tempdb..usages