Sybase Database - Sybase 'transaction log in database tempdb is almost full' error msg.

Author

Sybase 'transaction log in database tempdb is almost full' error msg.

2006-01-04, 8:23 pm

Hi,
I'm getting the following error messages from the database when I run
the following SQL via DBArtisan against Sybase 12.5 .

Error message follows:
The transaction log in database tempdb is almost full. Your
transaction is being suspended until space is made available in the
log.
Space available in the log segment has fallen critically low in
database 'tempdb'. All future modifications to this database will be
suspended until the log is successfully dumped and space becomes
available.

Query follows:

SELECT DISTINCT
T_DEPOSITORY.id, T_DEPOSITORY.depository_type, T_VENDOR.edi_name,
T_ORDER.depository_id, T_ORDER.id,
T_ORDER_DETAIL.latest_record_flag, T_ORDER_DETAIL.active_flag,
T_ORDER_DETAIL.license_price,
T_ORDER_DETAIL.multilist_code, T_ORDER_DETAIL.unit_price,
T_ORDER_DETAIL.quantity,
T_SHIPPING_DETAIL.shipping_date,
T_SHIPPING_DETAIL.shipped_quantity,
T_SHIPPING_DETAIL.vendor_part_number,
T_SHIPPING_DETAIL.shipped_license,
T_SHIPPING_DETAIL.invalid_flag,
T_REQUISITION. requested_shipment_d
ate,
T_REQUISITION. county_district_numb
er, T_REQUISITION.date_completed,
T_REQUISITION.date_released,
T_REQUISITION.latest_record_flag,
T_PRODUCT.title, T_PRODUCT.class_type, T_PRODUCT.medium_type,
T_PRODUCT_VENDOR.type
FROM
T_DEPOSITORY,
T_SCHOOL_DISTRICT,
T_ORDER,
T_ORDER_DETAIL,
T_SHIPPING_DETAIL T_SHIPPING_DETAIL,
T_REQUISITION T_REQUISITION,
T_PRODUCT T_PRODUCT,
T_PRODUCT_VENDOR T_PRODUCT_VENDOR,
T_VENDOR T_VENDOR
WHERE
T_DEPOSITORY.source_id *= T_SCHOOL_DISTRICT. county_district_numb
er
and T_DEPOSITORY.source_id *= convert(varchar(6), T_VENDOR.id)
AND T_ORDER.id =T_ORDER_DETAIL.order_id
AND T_ORDER.depository_id = T_DEPOSITORY.id
AND T_ORDER_DETAIL.active_flag = 1
AND T_ORDER_DETAIL.unit_price > 0
AND T_ORDER_DETAIL.latest_record_flag = 1
AND NOT (T_VENDOR.edi_name='SCHOOL' OR T_VENDOR.edi_name='STATE')
AND T_SHIPPING_DETAIL.invalid_flag=0
AND T_SHIPPING_DETAIL.shipping_date >
T_REQUISITION. requested_shipment_d
ate
AND T_SHIPPING_DETAIL.shipping_date >= '2005-12-27 00:00:00'
AND T_SHIPPING_DETAIL.shipped_quantity > 0
AND T_REQUISITION. latest_record_flag=1

AND T_PRODUCT. medium_type='TEXTBOO
K'
AND T_PRODUCT_VENDOR.type='CONTRACT'

ORDER BY
T_VENDOR.name, T_ORDER_DETAIL.multilist_code,
T_REQUISITION. county_district_numb
er, T_ORDER_DETAIL.order_id,
T_SHIPPING_DETAIL.vendor_part_number

Does anyone have any recommendations?

Thanks,
Bill

Larry Coon

2006-01-04, 8:23 pm

bb wrote:

> Does anyone have any recommendations?

A few comments:

1. Read and understand how the tran log works in ASE,
along with how & why it fills and when & how it
should be dumped/truncated. This is a fundamental
aspect of the dbms that is very important to
understand.

2. Assuming it's not your query filling up the temdb
tran log all by itself (with a worktable), you can
clear the tran log with:
dump tran tempdb with truncate_only

3. If your query is doing it all by iteslf, you can
take a look at the query plan, see where it's using
a worktable, and possibly re-write it to avoid it.
If you can't, then another alternative would be to
make tempb's log segment larger.

4. You probably want to turn the trunc log on checkpoint
option on for tempdb, if it isn't on already. Again,
see item #1 above.

Larry Coon
University of California

Sponsored Links