Loading ...
Sorry, an error occurred while loading the content.

Finding the growth size of the DB ... !

Expand Messages
  • itanatonauta
    Hi Everyone.. I would like to know how do you determine the growth size of a Database. Say for instance I have completed the job of creating my database schema
    Message 1 of 4 , Mar 17, 2004
    • 0 Attachment
      Hi Everyone..

      I would like to know how do you determine the growth size of a
      Database. Say for instance I have completed the job of creating my
      database schema for a brand new database. And now I would like to
      specify to my client how / what would be the growth rate of the
      database.

      I do understand that it depends on a lot of factors but I would like
      to know in general what could be the inputs I should have in mind to
      determine the growth of my DB.

      Say is it like you look at volume of certain key tables in the db to
      specify that given if N records are inserted over a period of X time
      then the growth would be Y times.

      in case I am not clear please ask. I am right now doing analysis to
      know if we can take up DB space or use existing DB space which our
      Host is providing but I can determine that only if I know someway to
      determine the probability of growth of the DB.

      - iTana
    • Anjana Ram
      Hi iTana, Very interesting!!! If I have understood it right, i think u r asking about how to determine the growth rate of the size to help u decide about the
      Message 2 of 4 , Mar 18, 2004
      • 0 Attachment
        Hi iTana,

        Very interesting!!!

        If I have understood it right, i think u r asking about how to
        determine the growth rate of the size to help u decide about the
        storage space, and not asking about the estimation of database size
        itself.

        Ofcourse both are interrelated.and in anycase i would like to answer
        both

        Estimating the size of the database:

        I can see 2 things that we should look at while deciding the size.

        One: The DB design

        Now when we say DB design it just doesn't mean the number of tables
        present and their relations.

        It means many things like the datatypes that have been used, number
        of indexes that have been created on the tables, number of primary
        keys, foreign keys etc etc.

        For instance, their might be a very small database but it might have
        been designed more for reporting purpose and hence might have too
        many indexes for enhancing speed. This database size is defenetely
        going to be more that some other larger database with no indexes at
        all.Same holds good incase of data types also. Data types tell us
        about the kind of data that we are going to store and hence to a
        large extent contributes to the current estimation.

        So with all these factors in mind one can estimate the space for one
        row or one transaction(which ever is applicable).

        Second: The application for which the DB has been designed.

        The application or the users of the application will be actually
        helping us to know what are the transactions and number of inserts
        per transaction. (We should also know that data is not always added
        to the database, it can also be deleted.So consider this also)

        So, here we can estimate the number of records or transaction that
        can take place over a period of time.

        So with careful analysis of these 2 factors and interrelating them we
        can estimate the size of the database and decide about the space
        required for over specific period of time.
        And from here we can know the growth rate also.

        2.How to determine the storage space with growth rate:

        Now if u have already estimated the database size for a specific
        period of time, u can to certain extent determine the growth rate
        also. But to decide about the storage space that u require u should
        also consider the following things and not depend only on the
        estimated growth rate.

        1. How much data is crucial for u after a period of time.
        All the data stored in ur database might not be always important for
        u over a perord of time.
        So u might decide to backup this data or delete it and store only the
        important and required information for ur application to perform
        properly .
        For example,
        If u have an application (say a discussion board)that stores the user
        information and their posts. When the user unsubscribes, u would
        delete the user info but might not delete his posts.Or if the posts
        are very old u might even delete them. It depends!!!.
        So one has to estimate the importance and relavance of data in each
        table after a period of time and what measures he would be taking to
        manage that data.

        2. Insertions vs deletions ratio
        While determining the size itself, people usually tend to consider
        only addition of data but do not consider deletion. This ofcourse
        does not do any harm. Infact we are on the safe side this way.
        But if u have to economically decide, then addition vs deletion
        ratio is very important.

        3.And one more thing before u decide about the disk space is that
        apart ur your data the disk will also store some default system files
        and some application files like the log files etc. And all this
        occupies space. So, the disk space available for use will be ur total
        disk space minus the disk space used by the system files.

        Hope this helps!!! Let me know if have understood u right.

        There might be many more things that i would have missed.
        I would like to know more on this. Any more thougts!!!

        Regards
        Anjana
        MS India Community Star
        MUGH.Net

        --- In sqlcon@yahoogroups.com, "itanatonauta" <ansari@m...> wrote:
        > Hi Everyone..
        >
        > I would like to know how do you determine the growth size of a
        > Database. Say for instance I have completed the job of creating my
        > database schema for a brand new database. And now I would like to
        > specify to my client how / what would be the growth rate of the
        > database.
        >
        > I do understand that it depends on a lot of factors but I would
        like
        > to know in general what could be the inputs I should have in mind
        to
        > determine the growth of my DB.
        >
        > Say is it like you look at volume of certain key tables in the db
        to
        > specify that given if N records are inserted over a period of X
        time
        > then the growth would be Y times.
        >
        > in case I am not clear please ask. I am right now doing analysis to
        > know if we can take up DB space or use existing DB space which our
        > Host is providing but I can determine that only if I know someway
        to
        > determine the probability of growth of the DB.
        >
        > - iTana
      • VISHNAT
        Hi Itana As Anjana has pointed out the Database growth may be due to several reasons and i would not repeat the same .Essential DB growth means growth of data
        Message 3 of 4 , Mar 18, 2004
        • 0 Attachment
          Hi Itana

          As Anjana has pointed out the Database growth may be due to several
          reasons and i would not repeat the same .Essential DB growth means
          growth of data file and Log File.

          Few things i would recommend is while actions like Rebuild Index and
          large transaction processing there is constant writing in the Log
          file and it tends to grow continuously as logged operations
          occurs.Ensure regular back up takes place this ensures the inactive
          log is truncated and the space is reused.

          Also i would suggest i if you are expecting your DB to grow to 3 GB
          in 6 months from now i would suggest you have that as your initial
          size and have a substantially big increment growth.

          hth
          Viswanath



          --- In sqlcon@yahoogroups.com, "Anjana Ram" <anjana_r21@y...> wrote:
          > Hi iTana,
          >
          > Very interesting!!!
          >
          > If I have understood it right, i think u r asking about how to
          > determine the growth rate of the size to help u decide about the
          > storage space, and not asking about the estimation of database size
          > itself.
          >
          > Ofcourse both are interrelated.and in anycase i would like to
          answer
          > both
          >
          > Estimating the size of the database:
          >
          > I can see 2 things that we should look at while deciding the size.
          >
          > One: The DB design
          >
          > Now when we say DB design it just doesn't mean the number of tables
          > present and their relations.
          >
          > It means many things like the datatypes that have been used, number
          > of indexes that have been created on the tables, number of primary
          > keys, foreign keys etc etc.
          >
          > For instance, their might be a very small database but it might
          have
          > been designed more for reporting purpose and hence might have too
          > many indexes for enhancing speed. This database size is defenetely
          > going to be more that some other larger database with no indexes at
          > all.Same holds good incase of data types also. Data types tell us
          > about the kind of data that we are going to store and hence to a
          > large extent contributes to the current estimation.
          >
          > So with all these factors in mind one can estimate the space for
          one
          > row or one transaction(which ever is applicable).
          >
          > Second: The application for which the DB has been designed.
          >
          > The application or the users of the application will be actually
          > helping us to know what are the transactions and number of inserts
          > per transaction. (We should also know that data is not always added
          > to the database, it can also be deleted.So consider this also)
          >
          > So, here we can estimate the number of records or transaction that
          > can take place over a period of time.
          >
          > So with careful analysis of these 2 factors and interrelating them
          we
          > can estimate the size of the database and decide about the space
          > required for over specific period of time.
          > And from here we can know the growth rate also.
          >
          > 2.How to determine the storage space with growth rate:
          >
          > Now if u have already estimated the database size for a specific
          > period of time, u can to certain extent determine the growth rate
          > also. But to decide about the storage space that u require u should
          > also consider the following things and not depend only on the
          > estimated growth rate.
          >
          > 1. How much data is crucial for u after a period of time.
          > All the data stored in ur database might not be always important
          for
          > u over a perord of time.
          > So u might decide to backup this data or delete it and store only
          the
          > important and required information for ur application to perform
          > properly .
          > For example,
          > If u have an application (say a discussion board)that stores the
          user
          > information and their posts. When the user unsubscribes, u would
          > delete the user info but might not delete his posts.Or if the posts
          > are very old u might even delete them. It depends!!!.
          > So one has to estimate the importance and relavance of data in each
          > table after a period of time and what measures he would be taking
          to
          > manage that data.
          >
          > 2. Insertions vs deletions ratio
          > While determining the size itself, people usually tend to consider
          > only addition of data but do not consider deletion. This ofcourse
          > does not do any harm. Infact we are on the safe side this way.
          > But if u have to economically decide, then addition vs deletion
          > ratio is very important.
          >
          > 3.And one more thing before u decide about the disk space is that
          > apart ur your data the disk will also store some default system
          files
          > and some application files like the log files etc. And all this
          > occupies space. So, the disk space available for use will be ur
          total
          > disk space minus the disk space used by the system files.
          >
          > Hope this helps!!! Let me know if have understood u right.
          >
          > There might be many more things that i would have missed.
          > I would like to know more on this. Any more thougts!!!
          >
          > Regards
          > Anjana
          > MS India Community Star
          > MUGH.Net
          >
          > --- In sqlcon@yahoogroups.com, "itanatonauta" <ansari@m...> wrote:
          > > Hi Everyone..
          > >
          > > I would like to know how do you determine the growth size of a
          > > Database. Say for instance I have completed the job of creating
          my
          > > database schema for a brand new database. And now I would like
          to
          > > specify to my client how / what would be the growth rate of the
          > > database.
          > >
          > > I do understand that it depends on a lot of factors but I would
          > like
          > > to know in general what could be the inputs I should have in
          mind
          > to
          > > determine the growth of my DB.
          > >
          > > Say is it like you look at volume of certain key tables in the
          db
          > to
          > > specify that given if N records are inserted over a period of X
          > time
          > > then the growth would be Y times.
          > >
          > > in case I am not clear please ask. I am right now doing analysis
          to
          > > know if we can take up DB space or use existing DB space which
          our
          > > Host is providing but I can determine that only if I know
          someway
          > to
          > > determine the probability of growth of the DB.
          > >
          > > - iTana
        • veer wangoo
          You must have already got reply from Vishwa and Anajan on this issue. How ever I can add to it.If Database Growth w.r.t LOG is too much the best solution is
          Message 4 of 4 , Mar 18, 2004
          • 0 Attachment
            You must have already got reply from Vishwa and Anajan
            on this issue.

            How ever I can add to it.If Database Growth w.r.t LOG
            is too much the best solution is put the recovery
            MODEL to Simple and backup..LOg will delete on its
            own.(TRuncate log on Check point)

            If data that is MDB file is growing ...make sure that
            file groups properly placed in disks and you divide
            the Objects on proper files.

            Now main question how to predict..Only one
            answer..Monitor and record the growth..use simple
            tools like Excel and other to predict with Bar charts
            etc ...Thats the clean and best solution...

            There are third party tools available in market as
            well.that monitor an d predict it for you...You can
            write your SPs as well and predict the growth

            =====

            VEER JI WANGOO
            DBA - Support
            Microsoft BUIT (HYD)
            INDIA
            MOBILE:-+91-41-32380755

            LANDLINE:-011-26645334
            ALTERNATE MAIL ID:-v-veerjw@...










            __________________________________
            Do you Yahoo!?
            Yahoo! Mail - More reliable, more storage, less spam
            http://mail.yahoo.com
          Your message has been successfully submitted and would be delivered to recipients shortly.