I think useless is a heavy word for this.

We have a database with 500GB, and most of it's information is only there if we MAYBE need to use them. it's a big database with credit card information and until now, we've never had the need to select them ( the table has 500millions rows ).

this database is growing daily and I would like to know what is the best approach of this?

I can't think in something smart. I don't think a backup and delete all rows will be the best approach. then if we need those rows, we restore the backup.

Is there something like partitioning to be used here? there are 2 tables with this "problem". how can I get rid of these 2 tables, but maintaining these tables secured?

  • 3
    What's the problem you're having right now? 500GB isn't a lot of space - unless you're storing it in memory or something... You could move them to their own file group and stick that group on slower storage, right? – James Jun 11 '18 at 14:52
  • 1
    oh no, it's because in this company it's a war to arrange some space. I'm thinking about move them to a different file gorup in a different storage, but the "space" is still there you know? I think there's no miracle here. – Racer SQL Jun 11 '18 at 14:54
  • 5
    There's not really any grey area here... either you need the data or you don't. If you do then you need to decide what trade-offs you're willing to make. Is keeping this database small by purging older data worth the effort of restoring an old copy when you need it? Partitioning isn't going to affect size, or even performance really, it's only going to affect manageability (e.g. making it easy to purge all data older than a certain date/time). So you need to better frame exactly what you're hoping to accomplish and why your peers are better equipped to decide what to do. – Aaron Bertrand Jun 11 '18 at 14:54
  • Thanks Aaron. that's what I told them. if we don't need it, we delete it. – Racer SQL Jun 11 '18 at 14:56

You basically have a couple of options, but in the end if you have records, they must occupy space.

You can try to compress the data. This will reduce disk space, but has side effects (it can make queries slower, for example). And it may not be available in your version of SQL. If I remember correctly, in SQL 2008 r2, compression requires Enterprise Edition. Brent Ozar's website talks a bit about some other side effects.

Otherwise, the best you can do is either move the data to cheaper (slower) disks via partitions and/or file groups or work out a retention policy that lets you purge data past a certain age (possibly after exporting it?)

| improve this answer | |

Not the answer you're looking for? Browse other questions tagged or ask your own question.