Database unususally large

I've been running Project Server 2003 for close to a year now. Currently the
database in SQL Server is at 16GB. I find it hard to believe that this is
correct, given that there are only around 6 projects stored in the database
and I know that much data has not been accumulated.
One thing I had to resort to early on was truncating the logs each night
because it would eventually grow so large (usually with a w or two) that
it would consume all disk space.
I'm not sure where to start here. I'm running this same App/DB in other
environments and this is not an issue
I did run dbcc checkdb and no errors or inconsistencies were reported.
Does anyone have a suggestion(s) on how to diagnose this issue and hopefully
fix the problem of this abnormal growth?You should be backing up your transaction logs on a regular basis. This
will also manage the growth of your log. Of course, you should also do full
backups of the DB itself. If you can take an outage from time to time, you
could re-index. All of these can be managed with a Database Maintenance
Plan, which you can create with Enterprise Manager.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Troy Jerkins" <tjerkins@.alltel.net> wrote in message
news:%23T4OVz4ZFHA.4040@.TK2MSFTNGP14.phx.gbl...
I've been running Project Server 2003 for close to a year now. Currently the
database in SQL Server is at 16GB. I find it hard to believe that this is
correct, given that there are only around 6 projects stored in the database
and I know that much data has not been accumulated.
One thing I had to resort to early on was truncating the logs each night
because it would eventually grow so large (usually with a w or two) that
it would consume all disk space.
I'm not sure where to start here. I'm running this same App/DB in other
environments and this is not an issue
I did run dbcc checkdb and no errors or inconsistencies were reported.
Does anyone have a suggestion(s) on how to diagnose this issue and hopefully
fix the problem of this abnormal growth?|||Perhaps there just is that much data. If you have documentation regarding
the database model, then identify any tables related to transaction history.
For example, perhaps it is retaining a copy of every record that is
modified.
"Troy Jerkins" <tjerkins@.alltel.net> wrote in message
news:%23T4OVz4ZFHA.4040@.TK2MSFTNGP14.phx.gbl...
> I've been running Project Server 2003 for close to a year now. Currently
the
> database in SQL Server is at 16GB. I find it hard to believe that this is
> correct, given that there are only around 6 projects stored in the
database
> and I know that much data has not been accumulated.
> One thing I had to resort to early on was truncating the logs each night
> because it would eventually grow so large (usually with a w or two)
that
> it would consume all disk space.
> I'm not sure where to start here. I'm running this same App/DB in other
> environments and this is not an issue
> I did run dbcc checkdb and no errors or inconsistencies were reported.
> Does anyone have a suggestion(s) on how to diagnose this issue and
hopefully
> fix the problem of this abnormal growth?
>|||Troy Jerkins wrote:
> I've been running Project Server 2003 for close to a year now.
> Currently the database in SQL Server is at 16GB. I find it hard to
> believe that this is correct, given that there are only around 6
> projects stored in the database and I know that much data has not
> been accumulated.
> One thing I had to resort to early on was truncating the logs each
> night because it would eventually grow so large (usually with a w
> or two) that it would consume all disk space.
> I'm not sure where to start here. I'm running this same App/DB in
> other environments and this is not an issue
> I did run dbcc checkdb and no errors or inconsistencies were reported.
> Does anyone have a suggestion(s) on how to diagnose this issue and
> hopefully fix the problem of this abnormal growth?
If you are not backing up your transaction logs on a regular basis, then
you should run the database in the Simple Recovery model to avoid log
file growth.
In your case, you were probably running in Full Recovery, which means
the log files continue to grow over time. It's possible most of that
space is taken up by the log files. Truncating the log file does not
recover disk space. You need to use DBCC SHRINKFILE for that
(off-hours).
Run sp_spaceused and sp_helpfile on the database and see how much space
is used and how much is unallocated space.
David Gugick
Quest Software
www.imceda.com
www.quest.com