topic DB doesn't return a response for a very long time without errors in Administration and Deployment

DB doesn't return a response for a very long time without errors

John_Wick — Sat, 18 Feb 2023 21:00:21 GMT

Hello everyone
I often encounter a problem and I think it is not unique. The problem sometimes happens on a prod environment. The usual select * from the database freezes on routine tasks. DBA say that there are no problems on their part. This usually happens with several campaigns on the schedule at the same time.

How to set, for example, read timeout or something else so that such requests fall with an error, and not remain dead?

Or does anyone have any idea why this happens at all?

DB - SQLServer via SAS ACCESS to SQLServer.

Usually this request takes 1-3 minutes and the data did not differ from those previously:

MPRINT(EXEC_MACRO.MAEXP.MAUSREXP.MINJECTION.MCOMMUNICATION.MCHECKATTRTYPES.MGETVARTYPESQL):
MPRINT(EXEC_MACRO.MAEXP.MAUSREXP.MINJECTION.MCOMMUNICATION.MCHECKATTRTYPES):
SQLSRV: Called SQLTables with schema of cdm
NOTE: 2 table(s) not displayed/returned because the name is too long.
SQLSRV: AUTOCOMMIT is NO for connection 5
SQLSRV: AUTOCOMMIT turned ON for connection id 5с

SQLSRV_14: Prepared: on connection 5
SELECT * FROM "cdm"."MA CONTACT HISTORY"

NOTE: No rows were selected.
MPRINT (EXEC_MACRO.MAEXP .MAUSREXP .MINJECTION.MCOMMUNICATION.MCHECKATTRTYPES) :
NOTE: PROCEDURE SQL used (Total process time):
real time 2:08:24.06

user cpu time 0.13 seconds
system cpu time 0.05 seconds
memory 5593.12k

OS Memory 39616.00k
Timestamp 17.02.2023 09:21:40
Step Count 435 Switch Count 0

Page Faults 0

Re: DB doesn't return a response for a very long time without errors

SASKiwi — Sun, 19 Feb 2023 02:15:14 GMT

Since it appears you are using a SAS product which is automatically generating SQL you would be best to open a Tech Support track for this issue.

Re: DB doesn't return a response for a very long time without errors

Sajid01 — Sun, 19 Feb 2023 14:10:13 GMT

Hello
I see this in the log "NOTE: 2 table(s) not displayed/returned because the name is too long."

SAS has limit of 32 character for table names whereas SQL server has a limit of 128 Character for table names.

Please review this.

Re: DB doesn't return a response for a very long time without errors

Tom — Sun, 19 Feb 2023 15:12:23 GMT

What are these macros you are running?

MPRINT(EXEC_MACRO.MAEXP.MAUSREXP.MINJECTION.MCOMMUNICATION.MCHECKATTRTYPES.MGETVARTYPESQL):

Perhaps one of them is very slow?