John Porter
asked on
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;
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!
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!
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
DBCC shrinkfile option and u will get the some more space.
ASKER
DUplicate entry prob for sure - thought i had responded earlier...
If you're not familiar with index reorgs/rebuilds you may find it easier to create a Maintenance Plan using the wizard.