6

Recently we have an audit database that went over one terabyte and, since we have storage problems, management is looking for options.

My proposal is at the end of each year we take a backup and truncate all the tables which will keep the database manageable.

It will not be beneficial to have an archive database as it will again consume the same space.

I would like to have an expert opinion about the options that I can propose to the management that is either allocate more space or truncate the whole database every year.

Improve this question
9
  • 2
    What "storage problems" are you trying to resolve? Just the mere fact that you're consuming 1 TB of space? Problems related to performance because of the database size? Something else? Does the audit data have value after a year-- do you have to be able to restore the data later to run queries, for example?
    – Justin Cave
    Oct 8, 2019 at 2:39
  • We simply dont have enough storage for the expansion. this server is an audit server and may be one or 2 people use it through the client not from the database directly
    – SQL_NoExpert
    Oct 8, 2019 at 2:41
  • Do those people want the data that more than a year old? How do you load the database (is it an OLTP process, for example, or a nightly/ weekly/ monthly batch load)? Are you already compressing tables and indexes?
    – Justin Cave
    Oct 8, 2019 at 3:00
  • application log files are loaded daily to the database through batch scripts. No tables or indexes being compressed
    – SQL_NoExpert
    Oct 8, 2019 at 3:03
  • If you don't have storage and this database is concerning audit then possibly you need to revisit the audit capture condition. May be, you need to capture much lesser log than what you are actually doing at this point of time.
    – Learning_DBAdmin
    Oct 8, 2019 at 9:39

2 Answers 2

Reset to default
8
  1. Get more space. If you have a business requirement to retain that much data, they have to come up with the money.
  2. Turn on page compression for the biggest tables (and indexes) (test test test!), Log data compresses extremely well, but compressing that amount of data is going to take some time.
  3. See point 1
Improve this answer
2
  • 1
    Also, don't forget about row compression. Sometimes that will give a decent compression ratio, but with a near-zero CPU overhead.
    – Tibor Karaszi
    Oct 8, 2019 at 8:38
  • As @TiborKaraszi says, row compression can be surprisingly effective especially if you have plenty of N[VAR]CHAR({n}) data in those rows because Unicode compression comes with it. Unfortunately that is not the case for LOB data such as NVARCHAR(MAX).
    – David Spillett
    Oct 8, 2019 at 16:29
5

I'm adding to Trubs' answer above, which you should upvote as (s)he's on the right track, but I'm adding more options here as well:

  1. Add more space (as stated above)
  2. If you have a high amount of average free space per page on any indexes you should run an ALTER INDEXREORG¹ statement (if you had ample free space, I'd suggest a REBUILD operation instead, but since we're talking about running out of space, this operation may not complete on larger tables). To find your average free space per page amount, you need to run the sys.dm_db_index_physical_stats dmv using the either the SAMPLED or DETAILED mode which will take a while.
  3. Purge some data. You can batch your deletes to help reduce excessive logging and locking, or the better long-term solution would be to utilize table and index partitioning² and switch/truncate the last partition for quicker data purging.
  4. Enable Page or Row compression² (as stated above). You can see estimated space savings using the the sp_estimate_data_compression_savings stored procedure.
  5. After you upgrade to SQL 2016 or later, you can convert your tables to use Clustered Columnstore Indexes². These will likely give you the best compression ratios available and will likely improve query execution times against these tables as well. Of note, I would NOT suggest you do this using SQL 2014 (or SQL 2012) as I would consider this feature being in Beta until SQL 2016 where it was finally released with proper functionality.
  6. Utilize Azure Stretch Database which is a hybrid solution that allows you to store data that isn't often accessed in the cloud. This will cost money and does require at least SQL 2016 to use, but since we're talking about features in future editions, I figure it should be listed as well.

¹ - Available in Enterprise Edition ONLY

² - Available in all editions of SQL Server starting with SQL Server 2016 SP1. This is an Enterprise-Only feature prior to this Service Pack.

Improve this answer
1
  • RE: "After you upgrade to SQL 2016 or later, you can convert your tables to use Clustered Columnstore Indexes" and/or archive old read only data to CCI as often as monthly when it is past possibly being edited. P.S. I am assuming some of the data is read/write if it is all read only, go with CCI all the way.
    – James Jenkins
    Oct 8, 2019 at 15:13

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