Per our security breaks and guidelines when a database grow too big that it cant meet the requirements where it cannot be restored under a given RTO we need to plan on achieving same:-

Based on your experiences i would need some guidance and help here:-

Some insights on the database in question -

Total space --> Right click database-properties -> size = 10TB

What seems free in above --> Right click database-properties -> space Available = 5 TB

This database is an OLTP and critical one as it goes through lot of DUI operations throughout with heavy usage of TempDB

I have listed out TOP 10 tables with space and index usage:-

All top 5 as pulled are

  • partitioned table with billion and million rows over 700 partitions in single table

  • Above tables have PAGE compression enabled

  • Above tables have less than 3 indexes in most of them but compression at index level shows NONE

For backup we do use compression with third party tool like idera to reduce size yet it crosses the threshold. Backup size is approx 1.9 TB for full backups. We do have diff and tran log backups as well.

I am little hesitant to go that shrinking route as it may impact performance but please help me with any ideas but else can be done to bring the space down?

Can we split few large table to their own separate database? If yes what is the catch?

Much appreciate all your help

  • 1
    If you are using compression on your backups, then that empty space is getting "collapsed" when you back up the database so shrinking the database files will not appreciably decrease the size of the backup. You certainly could move tables to another database, but is that solution acceptable to the business? Will the software still work correctly? Can you meet your RTO when restoring two databases instead of the one? I feel like you're looking inside the database for a solution to a problem that's largely external to it. – alroc Oct 29 at 20:11
  • @alroc: Thanks - also do you think index level compression helps here? – BeginnerDBA Oct 29 at 20:43
  • 4
    Have you determined when the RTO was created and if it has been recently reviewed? Perhaps the RTO is no longer applicable to this particular database/application ? Perhaps multiple RTOs are required ? Do you need all the data? Can some of it be purged/archived/migrated off line ? Do you have secondary datacente ? If not and the primary is destroyed is the backup stored off site ? If not your may want to revisit your entire DRP. – armitage Oct 29 at 21:13
  • 1
    Either the indexes are being compressed in the live database or they're being compressed in the backup file. Compressing already-compressed data rarely yields better compression. It's possible that they might compress better in the backup if they're not compressed in the live database but will it be enough to bring you under the limit(s)? Impossible to say without testing it with the database itself, or something resembling it closely. – alroc Oct 29 at 21:21
  • @armitage: Very good points. I just raised the same and few have asked them to check last week itself on data purge. yes DR side we are good. only thing is how far we can split or shrink here – BeginnerDBA Oct 29 at 21:30

I'll echo SqlWorldWide on the point about data types. The article they linked has a few good examples, but there are a lot more less-obvious areas to check too.

I've written a script sp_sizeoptimiser to help automatically identify a lot of them. Even if you don't use it, the areas it covers are worth looking into:

Data Types

  • Time based data types
  • Unspecified VARCHAR length
  • Mad VARCHAR Max
  • NVARCHAR data type
  • FLOAT and REAL data types
  • NUMERIC or DECIMAL with 0 scale
  • Enum columns not implemented as foreign key


  • Default fill factor
  • Number of indexes
  • Inefficient indexes
  • Sparse columns
  • Heap tables

Details for each of these is in the documentation linked above.

Preferably, I'd run this on a non-production copy of the database if possible, however it doesn't do any heavy lifting under the hood, so should be OK to run on production as well.

| improve this answer | |
  • 1
    @BeginnerDBA Sparse columns might be worth looking into implementing. Should be a rather quick way to reduce space, especially if you have a lot of nullable fields. The other points are all definitely worth looking into too. – J.D. Oct 31 at 3:06

I agree with all the points above and this is not a complete answer but I cannot put these as a comment.

In order to reduce size which will eventually make your restore quicker:

  1. Check compression at table/heap and also for each index
  2. Archive/Purge old data
  3. Choosing the correct data type will involve a lot of work. Read this Saving disk space by choosing the correct data type by Eduardo Pivaral
  4. Can you move objects to a different database?

Can you make the restore faster?

Few articles for your reference:

  1. Faster Restores: Best Practices to Increase Speed by Grant Fritchey
  2. How to Make SQL Server Backups and Restores Faster by Brent Ozar
  3. Why is restore slower than backupby Paul Randal
  5. Improve Restore Speed
| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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