Solved

SQL Server 2005 Databse growing rapidly for mno apparant reason

Posted on 2011-03-13
4
298 Views
Last Modified: 2012-06-27
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;
DBCC SHRINKDATABASE (My_Database, 10);
GO

USE My_Database;
DBCC SHRINKFILE (My_Database_Log, 10)
GO

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?

Thanks!
 
0
Comment
Question by:Saxitalis
4 Comments
 
LVL 78

Accepted Solution

by:
arnold earned 500 total points
ID: 35123218
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.
0
 
LVL 2

Expert Comment

by:spcb
ID: 35125609
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.
0
 
LVL 2

Expert Comment

by:Umesh_Madap
ID: 35130962
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.
0
 

Author Closing Comment

by:Saxitalis
ID: 35917031
DUplicate entry prob for sure - thought i had responded earlier...
0

Featured Post

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
How to update "part of a text field" without changing the entire contents? 2 66
Isolation level in SQL server 3 54
SQL- GROUP BY 4 23
What Is an Error? 2 24
In SQL Server, when rows are selected from a table, does it retrieve data in the order in which it is inserted?  Many believe this is the case. Let us try to examine for ourselves with an example. To get started, use the following script, wh…
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Email security requires an ever evolving service that stays up to date with counter-evolving threats. The Email Laundry perform Research and Development to ensure their email security service evolves faster than cyber criminals. We apply our Threat…
In an interesting question (https://www.experts-exchange.com/questions/29008360/) here at Experts Exchange, a member asked how to split a single image into multiple images. The primary usage for this is to place many photographs on a flatbed scanner…

749 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question