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.

MDCCL
  • 8,520
  • 3
  • 30
  • 61
SQL_NoExpert
  • 1,049
  • 1
  • 17
  • 37
  • 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 08 '19 at 02: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 08 '19 at 02: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 08 '19 at 03:00
  • application log files are loaded daily to the database through batch scripts. No tables or indexes being compressed – SQL_NoExpert Oct 08 '19 at 03: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 08 '19 at 09:39
  • Would you consider cloud DBs/storage as an option? It might work out cheaper than buying new hardware until you can properly find a full solution. – KevH Oct 08 '19 at 10:25
  • We have several database that are over several TB in size, as posting the answer is not possible I make a small list of things to try:
    1. Partition your database tables, likely into years, make "archive" file groups read-only helps in backups.
     – Walter Vehoeven Oct 08 '19 at 19:37
  • continue... 2) use mount points on your server, basically you can add them in any folder and your physical server will access DAS, NAS SAS storage via that mount point. 3) duplicate your setup onto a 2nd server and use the same mount points as a warm backup, if "server A" fails you can start "server B". For this to work well you make a DNS alias that you update to point the "active" server your users connect to the DNS alias, you can also use a failover cluster but that will likely fail a lot and you need an Enterprise license. – Walter Vehoeven Oct 08 '19 at 19:38
  • continue... 4) use log-shipping to a shared network drive to copy system tables to the "standby server" 5) Script failover using powershel or any other script to manage DNS update and network boot if "server a" goes offline. 6) You can script users to and use the always-on and other high-availebility features but management rather not pay the license fees on that. – Walter Vehoeven Oct 08 '19 at 19:41

2 Answers2

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
Trubs
  • 742
  • 1
  • 6
  • 14
  • 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 08 '19 at 08: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 08 '19 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.

John Eisbrener
  • 9,427
  • 6
  • 29
  • 63
  • 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 08 '19 at 15:13