Which Tool for a Large Database?

Hi all!

I've inherited a database which has grown to a huge bloat, and needs to be shrunk. How big? Well, it's over 4TB on disk - and more than 45% is bloat / empty pages. IE, more than 2TB can be reclaimed ... which would really help on our AWS costs.

Note: This problem is compounded several times, since there were several copies of this "starter" database used, so there's actually about 12TB of disk space being used and almost 7TB of disk space that can be free'd up across all of the servers.

Running shrink is a 4 day procedure on a cloned / copied version of the system, and no reads/writes, which we obviously cannot do in production. Some of the tables are full of binary data (that's a whole different problem).

I do have an extended 12 hour maintenance window that I can shut down the server, export / copy / whatever to a new database, and drop the old database.

Any suggestions? Is there a Red Gate tool to help here?

-
A
Tagged:

Answers

  • Hi @AMoney,

    Unfortunately, we don't have a tool that would be able to help in this scenario. 




    Sarah Beckett| Customer Support| Redgate Software
    Have you visited our Help Center? 
  • fhanlonfhanlon Posts: 17 Bronze 2
    If this is still an issue, check where is the bloat.  Often indexes contribute to this.  You can try reorging or rebuilding indexes to see if this helps.  Reorging an index is process that can be stopped and restarted later.  Reindexing rolls back so this is different but at least this process can be done bit by bit.  Stat with finding some of your large tables and checking out the index sizes.
    Francis
  • AMoney said:
    Hi all!

    I've inherited a database which has grown to a huge bloat, and needs to be shrunk. How big? Well, it's over 4TB on disk - and more than 45% is bloat / empty pages. IE, more than 2TB can be reclaimed ... which would really help on our AWS costs.

    Note: This problem is compounded several times, since there were several copies of this "starter" database used, so there's actually about 12TB of disk space being used and almost 7TB of disk space that can be free'd up across all of the servers.

    Running shrink is a 4 day procedure on a cloned / copied version of the system, and no reads/writes, which we obviously cannot do in production. Some of the tables are full of binary data (that's a whole different problem).

    I do have an extended 12 hour maintenance window that I can shut down the server, export / copy / whatever to a new database, and drop the old database.

    Any suggestions? Is there a Red Gate tool to help here?

    -
    A
    There is less number of such tools and they are paid also so one solution for this is to convert your data in the format in which are comfortable as well as took less memory and there is two way for doing this manually and professionally.
  • fhanlonfhanlon Posts: 17 Bronze 2
    Check why there is lots of space.  Are there particular tables with indexes that are very large having lots of unused space.  If so you can try to reorg indexes of ever drop unused indexes to free up space.  There are lots of scripts available that will help you find space used by table as well as space used by indexes.  Look at some of your larger tables first but look at tables that have indexes with large amounts of unused space as well.
    Francis
Sign In or Register to comment.