New: Overcoming Database DevOps Challenges. Read now.

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
  • KashyapiKashyapi Posts: 1 New member

     Are the first things that you'll have to check in this case is whether there is lots of space or not. If there are tables with indexes that have the largest slots then this might be one of the reasons behind the issue that you are facing.

    You can simply go for the reorganization of the indexes in order to free up the required space.

    Kashyapi Prajapati has been involved in the world of accounting software, SEO, and cloud computing from a very long time and currently, she is working as a lead content writer with Cloudwalks, a QuickBooks hosting which offers affordable QuickBooks hosting pricing. Cloud computing and SEO is what she eats and drinks.

Sign In or Register to comment.