I have a db that grew 8.2GB in one week and don't understand why.
There are three tables added to the db daily. I calculated the
spaceused by each of the three tables for a period of two weeks. The
tatal amount of data added to the db for the three daily tables over
the past two weeks was about 4MB yet the db grew approximately 8.2GB.
WHY?

Can someone please tell me what I should look at so that I can
understand what is going on?

Hi

There are a lot of things that can cause the database to grow. Without
more details its hard to say what, here are a couple of things it could
be.

Is it your database or is it your transaction log that has grown? If
you have your database set to full recovery and you are not doing
transaction log backups that can cause a large amount of growth.

Another possibility, have you recently set up a maintenace plan or a
job that rebuilds some or all of your clustered indexes? If your
database is set to auto grow, it could have grown due to index
rebuilds. Do you have some large tables on your database?

Hope this helps

John


war_whee... (AT) yahoo (DOT) com wrote:

At a guess, you have the database in full recovery mode and you are not
backing up the transaction log - in this case, the log will keep
growing. If you don't need transaction log backups, then you can set
the recovery mode to simple, and MSSQL will truncate (empty) the log
periodically. See "Selecting a Recovery Model" in Books Online for the
implications of using simple mode.

Note that truncating the log doesn't shrink the physical file - you can
use DBCC SHRINKFILE for that. See "Shrinking the Transaction Log" in
Books Online.

If this doesn't help, I suggest you post some more details - which
version of MSSQL, what size are the data and log files for the
database, have you tried DBCC SHRINKDATABASE and SHRINKFILE etc.

Simon

Simon and John thanks for both of your replies. Also in response to
both of your assumptions, I am running a 'Simple' recovery model. My
data file is 47,678,488,576 bytes or 47.6GB while my tlog file is
524,288,000 bytes or 524.2MB. Two weeks ago I started to monitor the
growth of the database. As I previously mentioned, we add three tables
to the database daily. The tatal size of all of the tables over a two
week period (3 tables x 10 days) is approximately 4MB.

Given these numbers, I don't understand how the db size could increase
8.2GB in a week. The reason that I say in a week is because two weeks
ago I tracked the size of the db file on a daily basis. The db was
39,403,651,072 or 39.4GB. I wasn't feeling well last week so I didn't
track the database growth for a week. I starting tracking the
databases growth again yesterday and noticed the 8.2GB difference from
a week ago.

On another note, I do understand your suggestion(s) of shrinking the db
or the db file. My issue though is how can a db grow 8.2GB when only
4MB of data is added. Remember last week the db size was unchanged
with approx. 2MB of additional data while this week the file has grown
8.2GB with the same addition of 2MB.

The db contains 515 tables, but most of the tables are the daily tables
which do not grow. Only the current day's daily tables grow and then
are used for historical reference. The core of the db contains approx.
38 tables which are mostly static. If there is change in the core
tables it is insignificate compared to the growth of the daily tables.

I know that we might not be able to figure out exactly what is
happening. I am only trying to gain a better understanding of the way
databases can grow. Perhaps each of the tables or a significate number
of them outgrow their allotted space and increased their reserved
space. This might account for the physical growth???

Going on the above assumption I reran the spaceused sp and did notice
an increase in the reserved space for the tables of two weeks ago. My
tracking only goes back two week, but if the increase is applied by
515 tables this may explain the anomaly.

Any thoughts?

<war_wheelan (AT) yahoo (DOT) com> wrote

My best guess is that you have autogrow enabled (I don't believe you
mentioned if you do or not), and adding the small tables has resulted in it
kicking in, and increasing the file size by whatever percentage or absolute
size is set. If the database size seems to fluctuate up and down, that could
be because you also have autoshrink enabled.

If these options are set (note that autogrow is per file, but autoshrink is
per database), then you could set up a server-side trace on the
autogrow/autoshrink events to get an idea of when and how often they occur.
There's more information on how the options work here:

http://support.microsoft.com/default...b;en-us;315512

Simon