Managing a SQL Server database with over one terabyte of data
- Get more space. If you have a business requirement to retain that much data, they have to come up with the money.
- 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.
- See point 1
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:
- Add more space (as stated above)
- If you have a high amount of average free space per page on any indexes you should run an
ALTER INDEX
REORG
¹ statement (if you had ample free space, I'd suggest aREBUILD
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 thesys.dm_db_index_physical_stats
dmv using the either theSAMPLED
orDETAILED
mode which will take a while. - 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.
- Enable Page or Row compression² (as stated above). You can see estimated space savings using the the
sp_estimate_data_compression_savings
stored procedure. - 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.
- 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.