Cannot dump transaction

[jerke szabo]

hi,

When i bcp-in a quiet large size of data into a table, the bcp process was
halted and message shown out the log was almost full. I try to exec dump
tran with no_log command, but the below error return:

"DUMP TRANSACTION for database 'aip_db' could not truncate the log. Either
extend the log using ALTER DATABASE ... LOG ON command or eliminate the
oldest active transaction in database 'aip_db' shown in syslogshold table."

use master
go
select * from syslogshold
go

dbid reserved spid page xactid masterxactid name
xloid
------ ----------- ------ ----------- -------------- -------------- --------
----------------------- -----------
5 0 31 502763 0x000848f20002 0x000000000000
aip_db..temp_aip_orders 62
5 0 0 502763 0x000000000000 0x000000000000
$replication_truncation_point 0

How can i do then?? Please help!

Thanks a lot!

[Navneet Chola]

Hi Jerke,

Look at the spid of the process which shows in the syslogshold table.

select "SPID"=spid from syslogshold where dbid = db_id("aip_db")

you will get the spid of the longest running process process .

To get the sqltext of the query :

dbcc traceon(3604)
go
dbcc sqltext(SPID) /*** that you have gotr from earlier query ***/
go
dbcc traceoff(3604)
go

you will get the text of the process , just check if you can affort to
kill that process as it might go for rollback.

if that is the culprit process , then you might be able to resolve the problem.

Regards
Navneet

"jerke szabo" <jerke...@hotmail.com> wrote in message news:<co1l09$2f6t$1...@news.hgc.com.hk>...

[Michael Russo]

"jerke szabo" <jerke...@hotmail.com> wrote in

news:co1l09$2f6t$1...@news.hgc.com.hk:

> hi,
>
> When i bcp-in a quiet large size of data into a table, the bcp process
> was halted and message shown out the log was almost full. I try to
> exec dump tran with no_log command, but the below error return:
>

When bcp'ing in a large table, I find it helpful to specify a batchsize of
10,000 or less, which will make Sybase commit the rows to the database and
remove the rows from the transaction log. Just add "-b 10000" to the bcp
command line.

-Michael Russo

[Jenny Davies]

The other factor that maybe causing you some problems is that the database
appears to have a rep agent/ltm running against it. That the process will
need to read the log before it can be truncated. So if you set your batch
size with -b to some thing like 5000, set truncate log of checkpoint with
sp_dboption and ensure you don't have abort tran set the data should go in.
It may still fill the log but should sleep until space is made available
when the repagent has read the log and continue.