Mine came back as 0.02.... does that mean it is not very fragmented? Our database is 112GB, 78.8% used, was "new" in October 2001 and has never been reorganized. Robin Sharpe Berlex Labs
|---------+---------------------------> | | "Prather, Wanda"| | | <[EMAIL PROTECTED]| | | HUAPL.EDU> | | | Sent by: "ADSM: | | | Dist Stor | | | Manager" | | | <[EMAIL PROTECTED]| | | T.EDU> | | | | | | | | | 10/20/03 01:03 | | | PM | | | Please respond | | | to "ADSM: Dist | | | Stor Manager" | | | | |---------+---------------------------> >-----------------------------------------------------------------------------------------------------------------------------------------| | | | | |To: [EMAIL PROTECTED] | |cc: | |Subject: | | Re: Online DB Reorg | >-----------------------------------------------------------------------------------------------------------------------------------------| That query (taken here from ADSM.QuickFacts) confuses me entirely - can someone please explain? SELECT CAST((100 - ( CAST(MAX_REDUCTION_MB AS FLOAT) * 256 ) / (CAST(USABLE_PAGES AS FLOAT) - CAST(USED_PAGES AS FLOAT) ) * 100) AS DECIMAL(4,2)) AS PERCENT_FRAG FROM DB It finds the number of unused pages (usable_pages - used_pages). Then it takes max-reduction and divides by unusable pages. But, so what? I don't get it. The unusable pages - max_reduction tells you how much of your data base is NOT usable. BUT again, so what? That doesn't say whether you need to do a data base reorg or not, does it? If my max reduction is 8 pages and my unused pages are 10, I've got 2 unusable pages. But if my data base is 1,000,000 pages, that certainly isn't much "fragmentation", the way a DB administrator (or space manager) would traditionally see it. Certainly no reason to do a DB reorg. WHY isn't the division done with the total usable pages as the numerator? The data base size has to enter in to the decision to reorg, somehwere. I'm confoooooooooooosed.... (But then, it's Monday....) -----Original Message----- From: Loon, E.J. van - SPLXM [mailto:[EMAIL PROTECTED] Sent: Monday, October 20, 2003 6:38 AM To: [EMAIL PROTECTED] Subject: Re: Online DB Reorg Hi Guys! The SQL statement can also be found at Richard's quickfacts page: http://people.bu.edu/rbs/ADSM.QuickFacts Kindest regards, Eric van Loon KLM Royal Dutch Airlines -----Original Message----- From: Remco Post [mailto:[EMAIL PROTECTED] Sent: Monday, October 20, 2003 12:30 To: [EMAIL PROTECTED] Subject: Re: Online DB Reorg On Sat, 18 Oct 2003 14:35:09 -0400 "Talafous, John G." <[EMAIL PROTECTED]> wrote: > Remco, > Would you be willing to share your SQL query that reports on DB > fragmentation? > I was allready looking at Eric (he probably saved my thingy somewhere usefull, I just saved it in my sent-mail folder), here it is... select cast((100 - ( cast(MAX_REDUCTION_MB as float) * 256 ) / - (cast(USABLE_PAGES as float) - cast(USED_PAGES as float) ) * 100) as - decimal(4,2)) as percent_frag from db Note that I still think this is one of the more useless queries I've ever build... > Thanks to all, > John G. Talafous IS Technical Principal > The Timken Company Global Software Support > P.O. Box 6927 Data Management > 1835 Dueber Ave. S.W. Phone: (330)-471-3390 > Canton, Ohio USA 44706-0927 Fax : (330)-471-4034 > [EMAIL PROTECTED] http://www.timken.com -- Met vriendelijke groeten, Remco Post SARA - Reken- en Netwerkdiensten http://www.sara.nl High Performance Computing Tel. +31 20 592 8008 Fax. +31 20 668 3167 "I really didn't foresee the Internet. But then, neither did the computer industry. Not that that tells us very much of course - the computer industry didn't even foresee that the century was going to end." -- Douglas Adams ********************************************************************** For information, services and offers, please visit our web site: http://www.klm.com. This e-mail and any attachment may contain confidential and privileged material intended for the addressee only. If you are not the addressee, you are notified that no part of the e-mail or any attachment may be disclosed, copied or distributed, and that any other action related to this e-mail or attachment is strictly prohibited, and may be unlawful. If you have received this e-mail by error, please notify the sender immediately by return e-mail, and delete this message. Koninklijke Luchtvaart Maatschappij NV (KLM), its subsidiaries and/or its employees shall not be liable for the incorrect or incomplete transmission of this e-mail or any attachments, nor responsible for any delay in receipt. **********************************************************************