"carl.doan" <carl.doan@cox.net> wrote in message news:<3F493C14.3090507@cox.net>...
> Teresa wrote:
> > Thank you,
> > I guess that I did not made my question clear... I created the new
> > database the same size, owner data and log as the source. After
> > loading from source to target I did a sp_helpdb TEST and I noticed
> > this (row_log 48MB data only) I created a device of 2048MB for data
> > and now shows 2000MB while for log I created 1 x 500MB and 2 x 300MB.
> > Is this very important? is there any harm if I leave it as is? I
> > don't know what to do
> >
> > Thank You
> > Teresa
>
> 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
>
> >
> >
> >
> > "carl.doan" <carl.doan@cox.net> wrote in message news:<3F488070.3000100@cox.net>...
> >
> >>Teresa wrote:
> >>
> >>
> >>>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
> >>
> >>When you perform the create/alter DB, try to do it in the same manner as
> >>the source DB. The way the TEST database was created differs from how
> >>the production was created.
> >>
> >>I don't recall who has a web page on this, but I think there is a way to
> >>get around this. Perhaps someone could point us in the correct dir.
> >>
> >>Carl


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