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
Solved

SQL Server 2005 Databse growing rapidly for mno apparant reason

Posted on 2011-03-13
4
294 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 77

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

Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Help with SQL joins 9 54
Get row count of current SQL query 8 59
Delete from table 6 47
Please help for the below sql query. 1 28
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…
INTRODUCTION: While tying your database objects into builds and your enterprise source control system takes a third-party product (like Visual Studio Database Edition or Red-Gate's SQL Source Control), you can achieve some protection using a sing…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

856 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