All Forums Database
jehan1 4 posts Joined 11/08
19 Nov 2008
Problem while creating NUSI

I am using teradat V2R5. I Wanted to create NUSI using " CREATE INDEX NUSI_2(Student_ID) on jehan.Registration "i got an error that "no more room in DBC", while my database name is jehan, it have too much space, any one can tell me what to do for creating index tables etc in my database, following is query through which i want to invistigate, kindly read following query, its result is also following.SELECT databasename ,SUM ((((MaxPerm) / 1024) /1024) ) AS MaxSpace ,SUM ((((CurrentPerm) / 1024) /1024)) AS CurrentUsed_MB,((100-Percent_Used)*MaxSpace)/100 AS FreeSpace,(Current_Space_Used_MB / NULLIFZERO (Maximum_Space_MB) * 100 ) (FORMAT 'zz9.99%' , TITLE 'Percent // Used') AS %Used,(FreeSpace / NULLIFZERO (MaxSpace))FROM DBC.DISKSPACEGroup By 1ORDER BY 5 DESC;Databse | MaxSpace | CurrentUsed | FreeSpace |%used | Free/Max---------------------------------------------- --------------------DBC | 19 | 69 | -51 | 373 | -3jehan | 350 | 111 | 239 | 32 | 1

Jim Chapman 449 posts Joined 09/04
19 Nov 2008

If you do an "explain" for your "create index" statement, you may notice that it wants to insert rows into DBC.Indexes and DBC.ContraintNames. Since there is no more available space in the DBC database, these inserts will fail.

jehan1 4 posts Joined 11/08
19 Nov 2008

How to make room(i mean some space in DBC), i am using TeraData V2R5 Demo Version, I have only one database "jehan", having only two Large tables, you can see database size from above query result.thanks

jehan1 4 posts Joined 11/08
19 Nov 2008

When i created "jehan" database, then i created about 500 tables, but now i have droped all of them and i have only 2 tables, my database is more than 50% free, please show me way to solve problem of DBC space.

Adeel Chaudhry 773 posts Joined 04/08
20 Nov 2008

Hello,Try running following query and check the results then:For output in GBs:SELECT databasename , SUM ((((MaxPerm) / 1024) /1024) / 1024) AS Maximum_Space_GB , SUM ((((CurrentPerm) / 1024) /1024) / 1024) AS Current_Space_Used_GB, ((100-Percent_Used)*Maximum_Space_GB)/100 AS Free_Space_GB, (Current_Space_Used_GB / NULLIFZERO (Maximum_Space_GB) * 100 ) (FORMAT 'zz9.99%' , TITLE 'Percent // Used') AS Percent_Used, (Free_Space_GB / NULLIFZERO (Maximum_Space_GB))FROM DBC.DISKSPACEGroup By 1ORDER BY 5 DESC;For output in MBs:SELECT databasename , SUM (((MaxPerm) / 1024) ) AS Maximum_Space_MB , SUM (((CurrentPerm) / 1024) ) AS Current_Space_Used_MB, ((100-Percent_Used)*Maximum_Space_MB)/100 AS Free_Space_MB, (Current_Space_Used_MB / NULLIFZERO (Maximum_Space_MB) * 100 ) (FORMAT 'zz9.99%' , TITLE 'Percent // Used') AS Percent_Used, (Free_Space_MB / NULLIFZERO (Maximum_Space_MB))FROM DBC.DISKSPACEGROUP BY 1ORDER BY 5 DESC;And also check if you have enabled query logging which is eating up your DBC space.Lastly, it is good to post error-free SQL, and the reason of creating such forum is not to delete previous posts, but to let them stay there so anyone may find useful information from it or you may refer to them even after issue is solved or in similar problems.HTH.Regards,Adeel

-- If you are stuck at something .... consider it an opportunity to think anew.

Adeel Chaudhry 773 posts Joined 04/08
20 Nov 2008

And one more suggestion, if you can use Teradata 12 Express, it supports more space than Teradata V2R5 Demo.You can download Teradata 12 Express from http://www.teradata.com/express.Regards,Adeel

-- If you are stuck at something .... consider it an opportunity to think anew.

jehan1 4 posts Joined 11/08
20 Nov 2008

Dear Adil, i run the scrip you provided, its result is following, another thing i cant change version, i need to do my work within this version.I checked, Query loging not enabledDatabase = DBCMaximum_Space_MB = 19087Current_Space_Used_MB = 131996Free_Space_Used = -112909Percent_Used_MB = 692(Free_Space_MB / NULLIFZERO (Maximum_Space_MB)) = -6-------------------------------------------------------------------my own databseDatabase = jehanMaximum_Space_MB = 358400Current_Space_Used_MB=112961Free_Space_Used = 245440Percent_Used_MB = 32(Free_Space_MB / NULLIFZERO (Maximum_Space_MB)) = 1

You must sign in to leave a comment.