SQL Server Performance

Abnormal Database growth in 2000

Discussion in 'General DBA Questions' started by Amulya, May 8, 2006.

  1. Amulya New Member

    Hi ,

    I am facing abnormal database growth problem in our environment.Previously we had sql 7.0 .At that time our DB growth is constant. Now , when we migrated to 2000, from that instace, few Databases are growing abnormally.

    Actually data is not growing that much . whenever database is shrinked, it will get reduced, again it starts increasing.



    Can anybody hlp me in this.

    Thanks

    Regards,


  2. cmdr_skywalker New Member

    probably the transaction log has grown more than expected. have you checked what cooking in your SQL Server lately?

    May the Almighty God bless us all!
    www.empoweredinformation.com
  3. thomas New Member

    Are you running reindex operations on the database? these will cause space to be consumed and then released. Only shrink the database if it's absolutely necessary.
  4. satya Moderator

    Have you executed DBCC INDEXDEFRAG as a part of one of the database maintenane tasks?
    THere is no need to shrink the database regularly, where you can define a size by considering the Trlog growth during bulk load jobs & database maintenance operations.

    Satya SKJ
    Microsoft SQL Server MVP
    Contributing Editor & Forums Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
  5. Luis Martin Moderator

    Did you check if what is growing is log file?

    Luis Martin
    Moderator
    SQL-Server-Performance.com

    Although nature commences with reason and ends in experience it is necessary for us to do the opposite, that is to commence with experience and from this to proceed to investigate the reason.
    Leonardo Da Vinci

    Nunca esperes el reconocimiento de tus hijos, eso ocurrirá luego de tu muerte


    All postings are provided “AS IS” with no warranties for accuracy.



  6. Amulya New Member

    Good Morning..

    Here log is not growing ..Only data files are growing. The DB's which are experiencing this problem doesn't have indexes.

    If the Data size is occupying more than 80%.Then I create a clustered index on tables and then remove that . This will reduce the space if I see using DBCC SPACEUSED.

    As this Db's doesn't have indexes , I couldn't run INDEXDEFRAG operations.


    Regards,

  7. satya Moderator

    Run SP_SPACEUSED @UPDATEUSAGE='TRUE' for a period of time and check the database growth.
    SP_WHO2 will give you more information on the number of connections and their activity, you should be able to monitor with the help of references above.

    Satya SKJ
    Microsoft SQL Server MVP
    Contributing Editor & Forums Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
  8. Amulya New Member

    Even I ran SP_SPACEUSED @UPDATEUSAGE='TRUE' . Most of the space will be there in usused column . If I create the index ,the unused space will go off.

    This we are doing as a maintenance job.Want to know what has to be done to stop this type of behaviour.

    Regards,
  9. satya Moderator

    Enlist what kind of batch bulk load jobs and database maintenance jobs you've scheduled on the databases.

    Satya SKJ
    Microsoft SQL Server MVP
    Contributing Editor & Forums Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
  10. Luis Martin Moderator

    Can you check what kind of grow you do have settings?. I mean: automatic growing by %, by MB?


    Luis Martin
    Moderator
    SQL-Server-Performance.com

    Although nature commences with reason and ends in experience it is necessary for us to do the opposite, that is to commence with experience and from this to proceed to investigate the reason.
    Leonardo Da Vinci

    Nunca esperes el reconocimiento de tus hijos, eso ocurrirá luego de tu muerte


    All postings are provided “AS IS” with no warranties for accuracy.



  11. Amulya New Member

    We didn't have any bulk load jobs.


    We have Updatestats job every day , Reindexing jobs every week and intrgrity check job every week.


    we have grow by 10% Setting .


    Regards,


  12. satya Moderator

    That explains why the transaction log is growing and no use of running SHRINK job every day.
    It is better to ascertain the value for the size of Transaction log in order to avoid this roundabout method.

    Satya SKJ
    Microsoft SQL Server MVP
    Contributing Editor & Forums Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
  13. Amulya New Member

    Transaction Log is not growing Only data files are growing..

    My doubt is , If the data files really has data, then shrinking or creating clustered index shouldn't help me naa


    Regards,
  14. ashwinreddy.c New Member

    Hi

    I think this is the problem with SQL Server 7.0. When ever u delete any data from SQL Server 7.0 then that page will not be reused i suppose. Go through the below link and I hope this answers your question.

    www.sql-server-performance.com/lost_data_sql_server.asp



    Regards
    Ashwin Reddy
  15. Amulya New Member

    We have sql 2000 SP4 not SQL 7.0

    Regards,
  16. ashwinreddy.c New Member

    Hi

    Ya thats right. you can see this problem when u migrate from 7.0 to 2000. did you go through that link.

    Regards
    Ashwin Reddy
  17. Amulya New Member

    Hi,

    Do u mean to say that, if table doesn't have clustered index, then this will happen


    Regards
  18. wwtang New Member

    I ran into the same problem. I noticed it when I upgraded to SP4. It took MS 6 months to acknowledge it is a bug:

    "Under certain circumstances, internal information used by SQL Server to allocate pages enters a state that causes extents (64kb page blocks) to be allocated when not needed, and to use only a small amount of the memory space available in the extent."

    You can mask the problem by reindexing tables having a clustered index. Tables without a clustered index will grow out of control. The best way to look at the problem is to store the results from SP_Spaceused for all your tables daily and graph it over time. You can see a saw tooth pattern if you reindex your database weekly. I noticed it the most on a table that grows by 100K new records daily.

    There is no hot fix. The solution/work-around is to change your server settings for "Open Objects" to a value larger than the total number of tables on the server. From what I understand the problem also exists with SQL2k5.

Share This Page