HiveBrain v1.2.0
Get Started
← Back to all entries
patternMinor

Database growing like crazy - what do I do?

Submitted by: @import:stackexchange-dba··
0
Viewed 0 times
whatlikedatabasegrowingcrazy

Problem

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.

Solution

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

Context

StackExchange Database Administrators Q#14304, answer score: 9

Revisions (0)

No revisions yet.