• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 323
  • Last Modified:

SQL Server 2005 Databse growing rapidly for mno apparant reason

Hello Experts,

I have a SQL erver 2005 database which was ibeen collecting data over the last 6 years.

The DB and log files were in the 60 Mb range a couple of months ago. I just noticed that the DB is now 1.2 Gb and growing and the Log file is at 95 Mb.

I found a table that was duplicating data every time a user's action was initiated. I did this:

1. Deleted the table
2. re-created the table
3. fixed the issue to keep the appropriate data inserting into the new table
I checked all other tables and found no similar issues...

I then shrank the DB and log file with

USE master;

USE My_Database;
DBCC SHRINKFILE (My_Database_Log, 10)

This got the DB size down to 540 Mb and the log file down to 15 Mb.

I still think there may be something else going on?

Does anyone know how I can get the file size down further and how to maintain it at the optimum size?

1 Solution
Check the application to see whether it is messing up and duplicating entries.
Is the data that is collected and stored in the database has increased in recent years?
Usually, a database grows slowly as the firm or its use start and then expands as its use expands by more users additional data.  If you do not delete data from the database, it always grows as data is added.
The transaction log presuming you are using the full recovery model can be managed through regular transaction log backups.

Deleting information is to only way to shrink a database.
IMHO, unless the data in a database is static, there is no such thing as "optimal" database size since the size of the database file is governed by the amount of data stored within.
Since you have shrunk the database you will almost certainly have a high level of fragmentation in this DB. This will take up unnecessary space, and you may experience poor performance as well. Try reorganizing/rebuilding the indexes in this database, and you may be able to shrink the database further (and don't forget to reorg/rebuild again if you use DBCC SHRINKDATABASE without the TRUNCATEONLY option).

If you're not familiar with index reorgs/rebuilds you may find it easier to create a Maintenance Plan using the wizard.
please check the unused space for the particular databasr  if you dont have the much unused space then we cant shrink further, if you could delete some data and shrink the datafile
DBCC shrinkfile option and u will get the some more space.
SaxitalisAuthor Commented:
DUplicate entry prob for sure - thought i had responded earlier...
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now