Hi John, it's no suprise that the filling of the database will not shrink, if you call "check data with update" several times. Could you check the size of your base table with the following command: Select * from tablestatistics where tablename = 'MYTABLE' And does the result fit's to the assumed size of 9500 pages? If not could you give me a complete data backup of your data base? Regards, Torsten
SAP DB, SAP Labs Berlin -----Original Message----- From: Gembus, John [mailto:[EMAIL PROTECTED] Sent: Montag, 8. Dezember 2003 09:21 To: Strahl, Torsten Cc: Open Source List Subject: AW: The number of data pages is increasing extraordinarily Hi, there aren't any long colums in my tables. I executed the "check data" several times. Only the first time decreases the numer of used pages. Actually there are 15495 pages used ( before the check data there were 18000 pages). It should be something about 9500. Regards John -----Urspr�ngliche Nachricht----- Von: Strahl, Torsten [SMTP:[EMAIL PROTECTED] Gesendet am: Montag, 8. Dezember 2003 08:49 An: 'Gembus, John' Cc: Open Source List Betreff: RE: The number of data pages is increasing extraordinarily Hi, no it should remove all unreferenced pages from the data base. What's about your table. Has it long columns and if so how big are these longs? (bigger or smaller than 8 KB?) Regards, Torsten SAP DB, SAP Labs Berlin -----Original Message----- From: Gembus, John [ mailto:[EMAIL PROTECTED] <mailto:[EMAIL PROTECTED]> ] Sent: Montag, 8. Dezember 2003 08:40 To: Strahl, Torsten Cc: Open Source List Subject: AW: The number of data pages is increasing extraordinarily Hi Torsten, the database i executed the command "dbmcli -d .. -u .. util_execute check data with update" on became a little smaler. I tested the "drop table and dataload" several times. After your command has finished it seems to me, that only the unreferenced pages from the last operation has been removed. When i'am right, is there a way to remove all of the unreferenced pages? regards John -----Urspr�ngliche Nachricht----- Von: Strahl, Torsten [SMTP:[EMAIL PROTECTED] Gesendet am: Freitag, 5. Dezember 2003 14:56 An: '[EMAIL PROTECTED]' Cc: Open Source List Betreff: RE: The number of data pages is increasing extraordinarily Hi John, your observation is absolut right. If you drop a table this will be done asynchronous by a server task. First the file directory of the correspondig file will be removed and then the b*tree is released. If you shutdown the dabase before this operation is finished you have unreferenced pages in your data area. The only way to remove them is: bring the database in state cold resp. admin and execute one of the following commands: in 7.3: dbmcli -d .. -u .. util_execute verify in 7.4: dbmcli -d .. -u .. util_execute check data with update Regards, Torsten SAP DB, SAP Labs Berlin -----Original Message----- From: Gembus, John [ < mailto:[EMAIL PROTECTED] <mailto:[EMAIL PROTECTED]> >] Sent: Donnerstag, 4. Dezember 2003 16:44 To: '[EMAIL PROTECTED]' Subject: The number of data pages is increasing extraordinarily Hello, some days ago i posted a mail, because my number of pages are increasing. Now i can reproduce the problem. 1. starting database 2. drop table 3. create table 4. dataload ( 270.000 items ) during this process i watch the statistic in dbmgui. The pages after the drop table don't become free. After the dataload is finished the database works a lot and sets the pages step by step free. When i shutdown the computer before this process has stopped these old-pages becomes permanent data. So my database grows. number of pages start 13723 drop table 13723 dataload start increases dataload stop 17500 the database works 13723 Any Ideas what to do ? - I'd like to know, how to stop this situation - Is it possible to free the pages . I looked for a way to reorg the database. I think the pages a marked as old. How can i identify them John
