Check for non-logged ops

[unknown]

Getting a spurious non-logged error for a dump tran command.
I would like to manually check to see if a dump tran can be
performed so I can monitor this condition. Is this a
condition I can check for in spt_values or logsegment status
bit? Would be part of the gen_id value or? Still searching
on my end. -Thanks.

[unknown]

Okay. No more Google! I was able to quickly answer that
question internal to the Sybase site. Pre 15.x as:

dbcc traceon (3604)
go
dbcc dbinfo (dbname)
go

[Manish Negandhi [TeamSybase]]

Post by unknown
Okay. No more Google! I was able to quickly answer that
dbcc traceon (3604)
go
dbcc dbinfo (dbname)
go

yes..and now we have handy function in ASE 15.x tran_dumpable_status
here is more informtaion http://tinyurl.com/yemwhtb

-HTH
Manish Negandhi
[TeamSybase]

[Bret Halford]

Post by unknown
Getting a spurious non-logged error for a dump tran command.
I would like to manually check to see if a dump tran can be
performed so I can monitor this condition. Is this a
condition I can check for in spt_values or logsegment status
bit? Would be part of the gen_id value or? Still searching
on my end. -Thanks.

This shouldn't be happening if the dboption "select into" is
turned off. Conversely, if you run with "select into" turned
on, you probably shouldn't generally be expecting to take tran dumps.

If you are pretty sure that "select into" is being kept off,
make sure nobody is using the "select ... into existing table"
syntax - it was documented in error and should not be used.
It does perform minimally logged transaction that can
break the transaction log chain and can execute even when
select into is turned off. CR 475469.

[zionassedo]

Post by unknown
Getting a spurious non-logged error for a dump tran command.
I would like to manually check to see if a dump tran can be
performed so I can monitor this condition. Is this a
condition I can check for in spt_values or logsegment status
bit? Would be part of the gen_id value or? Still searching
on my end. -Thanks.

On unix I use :
dbcc traceon (3604)
dbcc log (@dbid,0,0,0,-1,0,0)
Then I make a grep on 'time=' to get the very last transaction datetime, then I compare it with the previous one to see if there is a need to dump the transaction, it's a bit fishy but it works.

Here is the function in my ksh shell :
function GetLastTransDate
{
DBNAME=$1
LAST_TRANS=$($EXECDIR/sqsh -U$USERID -P$USER_PASS -S$EMS_SERVER -HHA -C"declare @dbid int select @dbid = db_id('$DBNAME') dbcc traceon (3604) dbcc log (@dbid,0,0,0,-1,0,0)" -b -h|grep "time=")
if [[ $LAST_TRAN == "" ]]
then #{
TRYVAR=1
while [[ $TRYVAR -le 5 && $LAST_TRANS == "" ]]
do #{
echo "retrying ...$TRYVAR/5"
sleep 5
LAST_TRANS=$($EXECDIR/sqsh -U$USERID -P$USER_PASS -S$EMS_SERVER -HHA -C"declare @dbid int select @dbid = db_id('$DBNAME') dbcc traceon (3604) dbcc log (@dbid,0,0,0,-1,0,0)" -b -h|grep "time=")
(( TRYVAR += 1 ))
done #}
fi #}

IFS="="
LAST_TRANS_DATE=$(echo $LAST_TRANS | awk '{print "",$4,$5,$6,$7 }')
IFS=" "
echo "LAST_TRANS=$LAST_TRANS"
echo "LAST_TRANS_DATE=$LAST_TRANS_DATE"
}