topic Re: ERROR: Teradata row not delivered (trget): No more spool space in SAS Programming

ERROR: Teradata row not delivered (trget): No more spool space

PhatRam33 — Wed, 07 Dec 2022 14:30:36 GMT

Good morning everyone and hope all is well.

Has anyone encountered this error before when pulling from Teradata? Using the following connection string for my pass-through query and using Unix Server in SAS EG. Is there a fix for this or something I can add to my connection string? Thanks in advance.

Error message:

Teradata row not delivered (trget): No more spool space in user123

%_log_host(PRDASWE3,tera);

proc sql ;

connect to teradata (&_log_host.

server="PRDASWE3" mode=teradata);

create table work.tbl_01 as

select * from connection to teradata (

Re: ERROR: Teradata row not delivered (trget): No more spool space

Tom — Wed, 07 Dec 2022 16:58:18 GMT

Ask your Teradata admin to help you tweak your Teradata query.

Re: ERROR: Teradata row not delivered (trget): No more spool space

webart999ARM — Wed, 07 Dec 2022 17:38:32 GMT

To fix this issue, you may need to increase the amount of space allocated to the user, or try running the query at a different time when there is less demand on the system. Additionally, you may want to try optimizing the query to reduce the amount of space it requires, or breaking it up into multiple smaller queries.

Try to increase the spool space limit for your user account. This can be done by running the following SQL statement on the Teradata server:

GRANT SPOOL ON user123 TO user123 WITH MAXSPOOL SIZE 100000;

Re: ERROR: Teradata row not delivered (trget): No more spool space

PhatRam33 — Wed, 07 Dec 2022 19:40:12 GMT

Thank you. Is there a command that would tell me what my current spool size is? Just want to make sure I'm not allocating less than what I have =).

Re: ERROR: Teradata row not delivered (trget): No more spool space

webart999ARM — Wed, 07 Dec 2022 19:47:21 GMT

Try this

SELECT USERNAME, CURRENTPERM, SPOOLUSED, SPOOLQUOTA
FROM dbc.dbcinfo
WHERE USERNAME = 'user123';

This will return the current spool space allocation for your user account. You can then adjust the allocation as needed using the ALTER USER command mentioned above.

Re: ERROR: Teradata row not delivered (trget): No more spool space

Tom — Wed, 07 Dec 2022 20:37:30 GMT

@PhatRam33 wrote:

Thank you. Is there a command that would tell me what my current spool size is? Just want to make sure I'm not allocating less than what I have =).

Probably. But most of the time you get spool file issue with Teradata the issue is not really the spool size. Teradata is a parallel processing engine. It divides the work between a lot of compute nodes. If the data does not get spread to the the nodes in a balanced way you can have issues even when the total spool space is fine. Normally it is caused by not using a primary key that allocates the records equally to each one.