Hi Thorsten, the number of used pages shown for indexstatistics and tablestatistics ower all tables are 9141. I tried the check data on another Computer where the database has already 56250. Now it has 28671 pages. There should be something like 9500 pages too.
It would be very kind of you, if you have a look on my backup. How can i send it to you. The size 21,8MB kind regards john > -----Urspr�ngliche Nachricht----- > Von: Strahl, Torsten [SMTP:[EMAIL PROTECTED] > Gesendet am: Montag, 8. Dezember 2003 10:32 > An: 'Gembus, John' > Cc: Open Source List > Betreff: RE: The number of data pages is increasing extraordinarily > > 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]>] > 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]>>] > 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 >
