Spool space error

Database, ,

Okay, so this is an old one, but I still find it useful.

If you can’t run a report because of a spool space error, and you can’t convince the DBA to give you a couple of gigabytes more, then you might want to go to the Report Editor, Data ->VLDB Properties -> Tables -> Intermediate Table Type and select True temporary table.

What this does is modifying the SQL so that instead of having a huge chunk of code (that would use up a lot of memory) it breaks the code into CREATE – INSERT bits that are far easier to handle. It also makes the SQL more readable by humans (this includes programmers).

By the way, this setting also comes to rescue when you get the “Database is terrified by the script and it can’t even begin to analyze it” error. I don’t have the specific error message at hand, but I know for sure that Teradata does this when the SQL code is all in one pass and is so large that it can be seen from the Moon.

About Sorin Suciu

Sorin is a MicroStrategy specialist, experienced in Retail, Telecom and Customer Operations. Blogging is where his passion for writing met his geek nature and decided they should do some crazy things together. Though not a funny guy himself, he is obsessed with arguably funny stuff, such as Terry Pratchett, Monty Python, Douglas Adams and live parrots.


Notice: compact(): Undefined variable: limits in /home1/microoat/public_html/blog/wp-includes/class-wp-comment-query.php on line 821

Notice: compact(): Undefined variable: groupby in /home1/microoat/public_html/blog/wp-includes/class-wp-comment-query.php on line 821

Notice: compact(): Undefined variable: groupby in /home1/microoat/public_html/blog/wp-includes/class-wp-comment-query.php on line 821

Warning: count(): Parameter must be an array or an object that implements Countable in /home1/microoat/public_html/blog/wp-includes/class-wp-comment-query.php on line 399

4 thoughts on “Spool space error”

  1. I am facing this spool space issue from a couple days.I wonder why is not working eventhough
    1)our terdata Dba’s increased the spool space.
    2) changed the VLDb settings as suggested above.

    please suggest me if there is any workaround.

    Thanks in advacne

  2. There are many reasons for spool space errors.
    Have you checked your SQL for cross joins? One of those nasty things can fill up your spool space in the blink of an eye.

  3. There are no cross joins in the sql.The SQL has condtion on division if
    division = 2 or 3 or 4 is running fine if division =1 throwing spool space error.
    data size is almost same for all the divisions.

    Thanks

  4. Well, it’s a bit difficult to assess this without seeing the SQL… The data size is important, but how much of that data is being used by the ensuing joins is also important. What I mean is that some IDs delivered by the condition “division = 1” may correspond to a huge quantity of sales or whatever in the rest of the tables involved in the script, thus resulting in a larger overall size.

    Tell you what… Change the VLDB setting to “Permanent table”, run the report and have a look at the SQL. The intermediary tables should now exist in the database (as they are not dropped at the end) and you can check them out. This way you can get a count on each one of them and perhaps make a better estimation on the amount of spool space requested.

Comments are closed.