In-DB Browse Tool In-DB Data Streamout stalls/errors out

.When I am working with indb tools it is very quick to process, takes merely seconds/minutes; however, when I am data-streaming out of in-db or use the indb browse tool it takes a long time to process, such a long time that the program errors out/stalls due to how long it is running.

.I then tried even datastreaming out 1 record and it still takes a long time. I’ve tried using the browse tool for like 1-100 records and it still won’t finish processing. (I only have this in-db Browse tool active as I understand multiple browse tools may stall the workflow)

Again processing through all the other tools works fine but when it comes to those tools I face a lag. The error I got is attached when using In-db Browse tool...only trying to see/retrieve 1 record.

(The database 'Tempdb' has reached its size quota. Partition or delete data, drop indexes, or consult the documentation for possible resolutions)

Alteryx Error.png

In Database

In-Database

Accepted answers

All comments

apathetichell

what database are you using? What's the driver? What's your memory like? Can you turn off amp.

Cpadilla98

Microsoft SQL, ODBC Driver, My computer is 64GB. I believe I have the option to turn amp on/off. How would turning off the AMP engine assist?

apathetichell

So there are two things here:

1) No amp should not effect this.

2) The key word is should. Amp because of how it works can create misdirection where something you tihnk is working actually isn't - and vice versa. In some cases turning off Amp allows diagnosis of the actual error.

I do not believe this is natively a memory issue so it may be a type issue (I don't use Microsoft SQL) - so are there any file types which may exsist in SQL but don't natively exsist in Alteryx (Structs/Arrays/Etc) - which the driver may have an issue processing?

jdminton

Based on your description and the error message. You may be running into an issue where no space is available in the DB. You mention that it works using in-DB tools, but you also said the in-DB browse tool provides that error. Since the in-DB tools don't provide any results, it may be that the process is finishing quickly without bringing the data out because it is failing. You can try turning on the "Enable performance profiling" in the runtime settings to see the time each tool takes. Based on the message, it could be memory in the DB or possibly that you don't have write access to create a temporary DB.

Cpadilla98

So in a tool prior to browse-indb or data-streamout it is failing/not capturing values?

Wouldn't the browse/data-streamout out just show no blank rather than stalling out?

-Something else to note, even when I try to view/streamout 1 record, it still stalls out. In my other workflow I have viewed/data streamed out much larger amount of records

jdminton

Did you try what I suggested?

apathetichell

-Something else to note, even when I try to view/streamout 1 record, it still stalls out. In my other workflow I have viewed/data streamed out much larger amount of records

that is what I think we have not diagnosed the issue yet. I do not think this is a memory issue. I think this is either a driver/compatability issue (ie field types are not something Alteryx can show/process) or some other issue that AMP is covering.

I lean towards 1) beause you mentioned your entire DB worked IN-DB - correct? It's only when you take to cavas or browse you have an issue.

I also see this - https://stackoverflow.com/questions/29705184/azure-the-database-name-has-reached-its-size-quota - if you are creating a net new tempdb - and working with it - perhaps your db admin has specific limits on temp db sizes/creation? I try to do as little as possible with Azure but this looks like it might not be an Alteryx issue at all.

Cpadilla98

Just tried it without the amp engine and it still stalls out.

I lean towards 1) beause you mentioned your entire DB worked IN-DB - correct? It's only when you take to cavas or browse you have an issue.

--Yes correct.

I think only big change I made in the data v. other WFs I have made in the past is that I used. So i'm not sure if this formula tool expression is something that Alteryx can show/process.

(PERCENT_RANK() over (Order By "% of _______" ASC))*100

apathetichell

Alteryx can handle any (most?) functions your native db can handle - but those kind of rank/partition functions can be memory eaters.

jdminton

Browse in-DB is still an in-DB tool and that did not complete. If you will try my suggestion of enabling performance profiling, you will see the length of time each tool is taking. That may help you identify the problem.

Cpadilla98

My main thing is that I want view data just to make sure it is processed correctly before connecting it to another 3rd party software.

Given that the those functions eat up a lot of memory (I use that function on 3 different occasions) I won't be able to view the data?

apathetichell

This should all be upstream in your DB - can you run this in a notebook/whatever Azure uses (Azure SQL Database query?) to see how long it takes? That will allow you to diagnose if it's a query/DB issue. If the query runs for an hour - it might just take an hour or two to run. This wouldn't be an Alteryx problem. It just is.

Maskell_Rascal

From how you are describing the problem and the error message received there could be a couple different issue causing this. My first thought is that this is an Azure database memory issue. See this link for more info: https://techcommunity.microsoft.com/t5/azure-database-support-blog/lesson-learned-292-the-database-userdb-has-reached-its-size/ba-p/3719609

The other thing that could be going on depends on what kind of database you are using from Microsoft. For example, if you are using "Microsoft SQL Server 2012, 2014, 2016, and 2019" database connection, then the there are size limitations for row size, column name length, etc.

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703