Solved

SQL Server 2005 Databse growing rapidly for mno apparant reason

Posted on 2011-03-13
4
265 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 76

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

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

Suggested Solutions

Introduction This article will provide a solution for an error that might occur installing a new SQL 2005 64-bit cluster. This article will assume that you are fully prepared to complete the installation and describes the error as it occurred durin…
When writing XML code a very difficult part is when we like to remove all the elements or attributes from the XML that have no data. I would like to share a set of recursive MSSQL stored procedures that I have made to remove those elements from …
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.

747 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now