{"id":1841,"date":"2019-10-09T01:27:14","date_gmt":"2019-10-09T01:27:14","guid":{"rendered":"http:\/\/www.theSQLReport.com\/?p=1841"},"modified":"2019-10-09T01:30:46","modified_gmt":"2019-10-09T01:30:46","slug":"collect-statistics-spool-space-error-in-teradata","status":"publish","type":"post","link":"https:\/\/www.theSQLReport.com\/?p=1841","title":{"rendered":"Collect Statistics Spool Space Error in Teradata"},"content":{"rendered":"

Recently I received an e-mail alert that my collect statistics job had a spool space error (“Failure 2646 No more spool space in…”) for one of the statements it ran on one of my Teradata systems.<\/p>\n

\"Collect<\/a>I went in to Viewpoint to look at the table in the “Space Usage” portlet, and found the skew was almost 100% for that table.\u00a0 There are many other ways to find how a table is skewed, but I find using Viewpoint easiest.<\/p>\n

\"Table<\/a><\/p>\n

Then in SQL Assistant, I ran a group by count of the primary key, and found that the table had all NULL values in the primary key field for the 21 million plus records.\u00a0 At that point, I notified the developer that something was very wrong.<\/p>\n

\"Group<\/a><\/p>\n

If at this point, the problem is still not clear, Teradata distributes the table data across its AMPs by the table’s primary key.\u00a0 If their is only one value for the primary key (in this case it being NULL), then all the data will be loaded into one amp.\u00a0 Spool space limits are also divided equally among all the AMPs for a user.\u00a0 So if all the 21 millions are loaded in one AMP, that user could possibly use all the available space trying to collect the statistics for that table.<\/p>\n","protected":false},"excerpt":{"rendered":"

Recently I received an e-mail alert that my collect statistics job had a spool space error (“Failure 2646 No more spool space in…”) for one of the statements it ran … Continue Reading →<\/a><\/p>\n","protected":false},"author":1,"featured_media":1842,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[54],"tags":[167,166,61],"class_list":["post-1841","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-teradata","tag-collect-statistics","tag-failure-2646","tag-spool-space"],"_links":{"self":[{"href":"https:\/\/www.theSQLReport.com\/index.php?rest_route=\/wp\/v2\/posts\/1841"}],"collection":[{"href":"https:\/\/www.theSQLReport.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.theSQLReport.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.theSQLReport.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.theSQLReport.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=1841"}],"version-history":[{"count":4,"href":"https:\/\/www.theSQLReport.com\/index.php?rest_route=\/wp\/v2\/posts\/1841\/revisions"}],"predecessor-version":[{"id":1848,"href":"https:\/\/www.theSQLReport.com\/index.php?rest_route=\/wp\/v2\/posts\/1841\/revisions\/1848"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.theSQLReport.com\/index.php?rest_route=\/wp\/v2\/media\/1842"}],"wp:attachment":[{"href":"https:\/\/www.theSQLReport.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=1841"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.theSQLReport.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=1841"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.theSQLReport.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=1841"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}