Database growing like crazy - what do I do?

Question

I have an Epicor 9 ERP database that I recently was put in charge of (new job). Server is running Server 2008 R2 with SQL Server 2008 R2 64bit. The main production database started at 16gb 7 months ago (go-live), and has grown steadily to 80gb+. They did schedule a maintenance job to Shrink all DB's each Sunday night, limit 50mb, free space 10%.

At it's current rate of growth the server's drive space will be maxed in less than 90 days, and I'll be forced to either upgrade the server or move the DB to a SAN. Overall system performance is terrible, system is just plain slow.

When I run the "disk usage by top tables" report I see the following:

• Number of records: 115,117,737
• Reserved (KB): 83,609,584
• Data (KB): 81,867,248
• Indexes (KB): 52,263,296
• Unused (KB) 1,742,336

I am waiting to hear back from Epicor regarding proper care and feeding of their system, but the Indexing seems to be using an insane amount of space. As stated, I only recently took over this system and I'm trying to figure it out as quickly as I can.

Any help would be appreciated.

EDIT: Warning to anyone who reads this. Dropped the duplicate indexes as described in the answers here completed hosed by test Epicor 9 database. E9 apparently uses indexes in a non-standard way. It has a Progress layer which optimizes the indexes for Progress and 4GL (aka ABL) code. I can't state definitively that this caused the problem, but it seems the most likely culprit. Attempting to log into E9 Test gives a "SQL Scripts need to run" error and the database appears to be stuck in an ALTER state.

Answer 1

Take a look at this blog Removing Duplicate Indexes by Kimberly Tripp and use the scripts to check if you have a completely redundant/duplicate indexes

Answer 2

If the indexes are growing like crazy, my gut feeling tells me that it is some bad or unnecessary indexes that are killing the disk space here.

I work with DB2 (as opposed to SQLServer) but I believe the concepts should be the same.

In our case we look to see what the index cardinality is. In other words, how many unique values are there per the columns in the index? The more unique values you have the better. If you have an index over a status field that only has two or three values and you have millions of records, the index has to be continually rebuilt. Kill those indexes as those will take lots of disk space, plus kill performance of your apps when doing INSERTs or UPDATEs.

Also check for redundant indexes (ie the same columns are used over and over again in different indexes). I can't speak for SQLServer, but at least in DB2, redundant indexes are worthless and just chew up disk space. DB2's Query Optimizer will only use one of the indexes and disregard the rest.

Again...I'm coming from a different product, but I believe the thought patterns are the same. Analyze the indexes and the data used by them to see which are really necessary. Dropping a few indexes may save you quite a bit.

Answer 3

Something else that can cause ridiculous growth is failing to backup the transaction log frequently. It will grow until it uses up all your space if you don't back it up. This is separate from the database backup. Check out the size of your transaction logs too.