database size suddenly increasing

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.

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

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?

Recovery model is set to bulk logged.

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

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?

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

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?

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.

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

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

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

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

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

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

Cheers
Twan

So does this mean you shouldn't shrink the database at all unless you want fragged database file?

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

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

sounds ok to me

Cheers
Twan

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.