SQL Server Performance

database size suddenly increasing

Discussion in 'Performance Tuning for DBAs' started by rage1973, Jan 23, 2004.

  1. rage1973 New Member

    We have a weird occurence in our company. Our network administor was looking over backups on SQL server and it appears the size of database went from 8 gigs to 10 gigs overnight between 6pm and 7am the next day. We have no employees that hits the database during non business hours so we aren't sure where all that data came from. The log file was also about 5 gigs when normally it should be about 20 to 50 megs. What is best way to try to solve this weird occurence? I tried looking at each size of the table and rows but nothing out of the ordinary sticks out. Thanks in advance for your help.
  2. Luis Martin Moderator

    1) What kind of manteinance plan do you execute overnight?
    2) What recovery model are setting in database?
    3) if 2 = full, what backup method do you use?


    Luis Martin

    ...Thus mathematics may be defined as the subject in which we never know what we are talking about, nor whether what we are saying is true.
    Bertrand Russell
  3. bradmcgehee New Member

    When you say the size of the database went from 8 to 10 gigs, was this the actual data in the database, or the size of the file holding the data?

    -----------------------------
    Brad M. McGehee, MVP
    Webmaster
    SQL-Server-Performance.Com
  4. rage1973 New Member

    It appears our database does dump to a directory and that directory is backed up every night.

    And regards to the other question the database is set to increase by 500 megs so it should be data that has increased also. How would you find out what percent of data file is filled up with data?
  5. Luis Martin Moderator

    Use Enterprise Manager, database, properties, options and see if recovery model is set Full.
    If is full, transaction log can increse from 20 Mb to 5 Gbytes in short time.
    If your backup is full and not incremental or differencial, change full to simple and log will get normal values.


    Luis Martin

    ...Thus mathematics may be defined as the subject in which we never know what we are talking about, nor whether what we are saying is true.
    Bertrand Russell
  6. rage1973 New Member

    Recovery model is set to bulk logged.
  7. Twan New Member

    do you do any dbcc dbreindex overnight? This can drastically grow the database, and shrinking the database will reduce the effectiveness of the dbreindex...

    Cheers
    Twan
  8. rage1973 New Member

    There was a job to db reindex and also shrink database that ran that night. Why would reindexing increase the size of database by 2 gigs though?
  9. bradmcgehee New Member

    Reindexing can increase the size of database. The reason for this is that when you rebuild an index, there must be enough empty room in the database to create a copy of the largest table in the database as the index rebuilding occurs. For example, if you have a 1GB table in the database, and there is only 500MB of free space in the database, then the database will have to grow just over 500MB total, to just over 1GB of empty space, for the index to be rebuilt.

    -----------------------------
    Brad M. McGehee, MVP
    Webmaster
    SQL-Server-Performance.Com
  10. rage1973 New Member

    But we have a same job that does the reindexing also doing a db shrink job. Shouldn't that get rid of all the extra space created by reindexing?
  11. rage1973 New Member

    Also would reindexing be logged because it appears the log file was about 5 gigs in size when it normally is about 50 to 100 megs.
  12. ChrisFretwell New Member

    If you shrunk after the reindex but your DB is still huge, then it may not be the indexes. Someone may have accidently left something running that created extra, unexpected records. They may have even stopped their app but the process remained running on the server (I'm speaking from experience, as this happened a couple of weeks ago and we almost ran out of disk space by the time it was caught). The best way I can tell you to find out if to run a spaceused on all tables in your db now and the same thing in a copy of the db from a day or two before the growth. Compare and look for any that are significanly larger in size/record counts. Also if you can, make sure there were no schema changes that add say a non-nullable char(8000) field to every record.

    Also, if your system guy was looking at backup sizes and not the mdf/ndf files, then its closer to the actual size of the data and someone really did add that much data.

    Chris
  13. Luis Martin Moderator

    BTW: What OS, SQL and SP are you running?


    Luis Martin

    ...Thus mathematics may be defined as the subject in which we never know what we are talking about, nor whether what we are saying is true.
    Bertrand Russell
  14. rage1973 New Member

    Windows 2000 Server
    SQL Server 2000 Enterprise SP2 running in 6.5 compatibility mode due to accounting package MAS 500 requiring it.
  15. Twan New Member

    Hi all,<br /><br />the result of your job will depend on whether you do a dbreindex first and then shrink or the other way around.<br /><br />The way dbreindex works is that it will only use contiguous space <b>at the end</b> of the database file. The reason for this is that dbreindex will end up with giving you totally defragmented indexes, it cannot achieve this by using free space within the database file itself.<br /><br />After this job has finished if you do a shrink database without moving pages, then it won't actually achieve any shrinkage since the pages at the end of the file have the newly defragged indexes.<br /><br />If you do a shrink database asking it to move pages, then it will shrink the database but the movement of pages from the end of the databsae causes your newly defragmented indexes to be fragmented again.<br /><br />SO it is a catch 22, you can either have cleanly defragmented indexes, or minimal free space within a database file. Without recreating the database from scratch and importing all the data cleanly, you can't achieve both.<br /><br />You do NOT want to shrink a database after doing a dbreindex... otherwise just omit the dbreindex step to save yourself some time... <img src='/community/emoticons/emotion-5.gif' alt=';-)' /><br /><br />Cheers<br />Twan
  16. Luis Martin Moderator

    Hi Twan:

    Why Rbindex and shrink before is waisting time?
    I mean, where I can find documentation?

    Thanks.


    Luis Martin

    ...Thus mathematics may be defined as the subject in which we never know what we are talking about, nor whether what we are saying is true.
    Bertrand Russell
  17. Twan New Member

    I've not seen anything written by MS. I went to one of the SQL sessions at Microsoft here in the UK, and Tony Rogerson, the guy presenting gave us a demonstration about this very topic. He created a database, created a table, fragmented it by doing several million inserts/updates and deletes. He then showed the fragmentation stats.

    After doing a dbreindex the table was naturally nice and defragmented.

    However, after doing a shrink db, the fragmentation stats were not that far off their original fragmented state...

    I'll see if I can find a link to this session it was run by www.sqlserverfaq.com

    Cheers
    Twan
  18. Luis Martin Moderator

    Thanks Twan. I suggest you to put this post in Tips Forum because is very important for all.


    Luis Martin

    ...Thus mathematics may be defined as the subject in which we never know what we are talking about, nor whether what we are saying is true.
    Bertrand Russell
  19. Twan New Member

    hmm unfortunately the presentation notes are in a subscribed area... subscription is free, and it is also a good SQL site

    the url is www.sqlserverfaq.com, under events there is UKSSUG Night School 15 Oct 2003. This contains a powerpoint presentation and some examples...

    Luis should I post it in the tips area too? I'm not comfortable with poaching the example and posting it here, but could post the above link/pointer...? what do you think?

    Cheers
    Twan
  20. Luis Martin Moderator

    I think is importat to post your experience with Tony and url too, just what you response me.

    I can´t reach the url in right now, but may be is temporaly problem.



    Luis Martin

    ...Thus mathematics may be defined as the subject in which we never know what we are talking about, nor whether what we are saying is true.
    Bertrand Russell
  21. Twan New Member

    ah the url seems to include the comma... will add this (without the comma) to the Hints and Tips page

    Cheers
    Twan
  22. rage1973 New Member

    So does this mean you shouldn't shrink the database at all unless you want fragged database file?
  23. Twan New Member


    You can shrink the database, but if you want to defrag then don't follow it with a shrinkdb. You could do a shrink first and then dbreindex (but you will have free space in the database

    Cheers
    Twan
  24. SQL_Guess New Member

    Sounds to me like the process should be something like this :

    1> Shrink Database
    2> DBCC DBreindex
    3> Full Backup
    4> DBCC Shrinkfile Logs

    One could play around, depending on your recoverability requirements, with 3 and 4. I add 4 because DBCC DBreindex is a logged operation on SQL 2000 (got caught out with a 60 GB log on ourt first dbreindex post sql 2K :-0 ).

    Obviously 1 is done if there is enough space to warrant shrinking, but assuming there is, it should be noted that this whole process could take a while especially on a large Db (like our 200 GB db).

    Thoughts ?

    Panic, Chaos, Disorder ... my work here is done --unknown
  25. Twan New Member

    sounds ok to me

    Cheers
    Twan
  26. Luis Martin Moderator

    Ok to me too.
    If DBCC DBreindex is for all tables OK.
    If not I suggest to include Update Statistics for those tables not in DBreindex.


    Luis Martin
    Moderator
    SQL-Server-Performance.com
  27. rage1973 New Member

    Does dbreindex increase the log files to huge size also. I notice the log dumps in the morning after the dbreindex job the log files are huge in size compared to other log dumps.
  28. Luis Martin Moderator

    That's right, DBCC increase log files.


    Luis Martin
    Moderator
    SQL-Server-Performance.com

Share This Page