Comments on: Spool space error

By: Sorin Suciu

Sorin Suciu — Thu, 09 Jul 2009 13:05:36 +0000

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.

By: mstr

mstr — Thu, 09 Jul 2009 11:16:17 +0000

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

By: Sorin Suciu

Sorin Suciu — Wed, 08 Jul 2009 09:32:18 +0000

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.

By: mstr

mstr — Tue, 07 Jul 2009 10:49:54 +0000

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