Solved

SQL Server 2005 Databse growing rapidly for mno apparant reason

Posted on 2011-03-13
4
307 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

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

I am showing a way to read/import the excel data in table using SQL server 2005... Suppose there is an Excel file "Book1" at location "C:\temp" with column "First Name" and "Last Name". Now to import this Excel data into the table, we will use…
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 …
This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have. In this…
Michael from AdRem Software explains how to view the most utilized and worst performing nodes in your network, by accessing the Top Charts view in NetCrunch network monitor (https://www.adremsoft.com/). Top Charts is a view in which you can set seve…

690 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