Insert using table variable causes heavy load, fills up tempdb log

[kazuootani]

Hello,

Running "insert into @table_variable select * from @table_variable" causes CPU 100% and heavy writes to tempdb.
It does not return response for a few minutes and finally ends up with tempdb log full.

ASE 16.0 SP03 PL02

Reproducing the issue:

1> declare @t table (a int)<br>2> insert @t values (1)<br>3> insert @t select * from @t<br>4> select count(*) from @t<br>5> go<br>(1 row affected)<br>Space available in segment 'logsegment' has fallen critically low in database'tempdb'. All future modifications to this database will be aborted until thetransaction log is successfully dumped and space becomes available.

Could any of you check this behavior?

Regards,
Kazuo Otani

[kazuootani]

Sorry, a title was too long.

Original one was "insert <table variable> select * from <table variable>" causes heavy load, fills up tempdb log

[former_member188958]

Hi Kazuo,

There is an open CR for this behavior, CR 809237. I've started writing KBA 2632248 for it but
(as I'm about to start a couple days of vacation) I don't expect to have the KBA published until next week.

Cheers,
Bret Halford