Answers.Body: <p>I don't think this is a solution you should employ without checking on a few things i.e. foreign keys, indexes, triggers etc. It's a risky way to do it. If you must find space and have cleared as much data as you can then the way to achieve the same results would be to backup and restore the database. This will ensure all the structures are maintained.</p>
  <p>There are alternatives but it depends on the version of MySQL/MariaDB you're running so if you want to explore them then please do post the details.</p>
  
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2021-07-06T09:34:49.493Z
Answers.Text: Thanks for the caution Parvez. I contacted my hosting company with your suggestion to backup / restore the dbs. They indicated they have an automated monthly optimisation routine which should recover the cleared space. They will run it today so I'm hoping it will make a difference. I will report back.
Answers.UserDisplayName: Davy Ivins
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2021-07-06T19:13:56.490Z
Answers.Text: Sadly the optimisation had a negligible effect. Still need to find a safe solution to recover the space.
Answers.UserDisplayName: Davy Ivins
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2021-07-06T19:29:16.047Z
Answers.Text: My MariaDB is version 5.7.34 and my hosting provider (Omega8) provides a Chive Manager interface to manage the DB.
Answers.UserDisplayName: Davy Ivins
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2021-07-06T20:28:06.060Z
Answers.Text: So you could try a manual backup/restore? To be honest anything a hosting provider claims to be doing is very hard to quantify without them being 100% clear on the steps they take. I doubt they will be backup/restoring the DB. If you wanted to 'test' the theory then you could use a backup/export and import into a test/local and see how much space is being used. If you've truncated tables etc this is the only way space can be regained in the version your running, newer versions will do this when an optimize is run on the tables.
Answers.UserDisplayName: Parvez Saleh
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2021-07-07T12:55:57.470Z
Answers.Text: Strongly agree w/ Parvez ... another issue that might be at play here is the mysql configuration "innodb_file_per_table" which used to default to 0. In that case, all the db data is in a big monster file and it tends to use up a lot of space and is harder to recover. I'd grab a copy of the database as a dump and do a few experiments on it to see what the real issue is. Or pay a bit more for more disk space.
Answers.UserDisplayName: Alan Dixon
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2021-07-05T20:57:15.317Z
Answers.LastActivityDate: 2021-07-05T20:57:15.317Z
Answers.UserDisplayName: Parvez Saleh
Body: <p><strong>I need to reduce the size of my Civi database</strong>.</p>
  <p>I've done a bunch of reading and have found what looks like a solution. Please will someone step in to advise if this solution (below) is the way to go, and if I can just run it on each of the db tables.</p>
  <p><strong>Identifying the problem tables</strong></p>
  <p>Mailing records are my primary issue and this extension worked well to remove old records. <a href="https://github.com/adixon/nz.co.fuzion.deleteoldbulkmailings" rel="nofollow noreferrer">https://github.com/adixon/nz.co.fuzion.deleteoldbulkmailings</a></p>
  <p>But as mentioned in <a href="https://civicrm.stackexchange.com/questions/31522/recommendations-or-experiences-on-archiving-mailing-data">Recommendations or Experiences on Archiving Mailing Data</a> this has not freed-up as much the space as I'd hoped.</p>
  <p>Using this helped identify the culprits:</p>
  <pre><code>SELECT 
  table_schema AS `Database`, 
  table_name AS `Table`, 
  round(((data_length + index_length) / 1024 / 1024), 2) `Size in MB` 
  FROM information_schema.TABLES WHERE table_schema = 'dbname' 
  ORDER BY (data_length + index_length) DESC
  LIMIT 20;
  </code></pre>
  <p><strong>SO, IS THIS THE SOLUTION?</strong></p>
  <p>This link offers a solution to recover the disk space: <a href="https://dba.stackexchange.com/questions/48740/recover-the-disk-space-after-deleting-rows-from-table">https://dba.stackexchange.com/questions/48740/recover-the-disk-space-after-deleting-rows-from-table</a></p>
  <p>The accepted solution in the post is to run this on the tables that have had rows removed:</p>
  <pre><code>USE mydb
  DROP TABLE IF EXISTS mytablenew;
  CREATE TABLE mytablenew LIKE mytable;
  ALTER TABLE mytable RENAME mytableold;
  INSERT INTO mytablenew SELECT * FROM mytableold;
  ANALYZE TABLE mytablenew;
  ALTER TABLE mytablenew RENAME mytable;
  DROP TABLE mytableold;
  </code></pre>
  <p>I'm running the latest Drupal and Civi versions.</p>
  
Tags: <database>
Title: Will this solution safely reduce the disk size of my database
ThreadText: # Will this solution safely reduce the disk size of my database
  
  (Asked by: username_0 on 2021-07-05)
  
  I need to reduce the size of my Civi database.
  I've done a bunch of reading and have found what looks like a solution. Please will someone step in to advise if this solution (below) is the way to go, and if I can just run it on each of the db tables.
  Identifying the problem tables
  Mailing records are my primary issue and this extension worked well to remove old records. https://github.com/adixon/nz.co.fuzion.deleteoldbulkmailings
  But as mentioned in Recommendations or Experiences on Archiving Mailing Data this has not freed-up as much the space as I'd hoped.
  Using this helped identify the culprits:
  SELECT 
  table_schema AS `Database`, 
  table_name AS `Table`, 
  round(((data_length + index_length) / 1024 / 1024), 2) `Size in MB` 
  FROM information_schema.TABLES WHERE table_schema = 'dbname' 
  ORDER BY (data_length + index_length) DESC
  LIMIT 20;
  
  SO, IS THIS THE SOLUTION?
  This link offers a solution to recover the disk space: https://dba.stackexchange.com/questions/48740/recover-the-disk-space-after-deleting-rows-from-table
  The accepted solution in the post is to run this on the tables that have had rows removed:
  USE mydb
  DROP TABLE IF EXISTS mytablenew;
  CREATE TABLE mytablenew LIKE mytable;
  ALTER TABLE mytable RENAME mytableold;
  INSERT INTO mytablenew SELECT * FROM mytableold;
  ANALYZE TABLE mytablenew;
  ALTER TABLE mytablenew RENAME mytable;
  DROP TABLE mytableold;
  
  I'm running the latest Drupal and Civi versions.
  
  **Tags:** database
  
  
  ---
  
  ## 1 answers
  
  ### Answer by username_1 on 2021-07-05. Score: 3
  
  I don't think this is a solution you should employ without checking on a few things i.e. foreign keys, indexes, triggers etc. It's a risky way to do it. If you must find space and have cleared as much data as you can then the way to achieve the same results would be to backup and restore the database. This will ensure all the structures are maintained.
  
  There are alternatives but it depends on the version of MySQL/MariaDB you're running so if you want to explore them then please do post the details.
  
  > **Comments:**
  > **username_0**: Thanks for the caution Parvez. I contacted my hosting company with your suggestion to backup / restore the dbs. They indicated they have an automated monthly optimisation routine which should recover the cleared space. They will run it today so I'm hoping it will make a difference. I will report back.  
  > **username_0**: Sadly the optimisation had a negligible effect. Still need to find a safe solution to recover the space.  
  > **username_0**: My MariaDB is version 5.7.34 and my hosting provider (Omega8) provides a Chive Manager interface to manage the DB.  
  > **username_1**: So you could try a manual backup/restore? To be honest anything a hosting provider claims to be doing is very hard to quantify without them being 100% clear on the steps they take. I doubt they will be backup/restoring the DB. If you wanted to 'test' the theory then you could use a backup/export and import into a test/local and see how much space is being used. If you've truncated tables etc this is the only way space can be regained in the version your running, newer versions will do this when an optimize is run on the tables.  
  > **username_2**: Strongly agree w/ Parvez ... another issue that might be at play here is the mysql configuration "innodb_file_per_table" which used to default to 0. In that case, all the db data is in a big monster file and it tends to use up a lot of space and is harder to recover. I'd grab a copy of the database as a dump and do a few experiments on it to see what the real issue is. Or pay a bit more for more disk space.  
  
  ---
  
  