Sql-server – Will DB backup time and size be decreased after truncate large tables

backupsql serversql-server-2005

Two tables in our SQL Server DB equate to 500M rows and 350GB of data. Disk space is an issue where we keep backups and the time to execute backups is as well. We plan to truncate these 2 tables.

I understand that to reclaim the disk space, we'll need to shrink the DB after truncation, then do an index rebuild to fix fragmented indexes afterwards. But as we are not sure how long the rebuilds will take (we are spinning up a test server VM in Azure now to test), in the meantime we are looking for incremental gains. After just truncating (before shrink and rebuild), will the backup times and the resulting backup file size (after 7z compression) be reduced dramatically as a result?

Best Answer

While it's been some time since I dealt with SQL Server backups that weren't compressed by SQL itself, SQL backs ups your data, not your free space.

As stated here

The data contained in a full backup is not necessarily all of the contents of all data files. The backup will only contain the allocated pages from the data files.

TRUNCATE deallocates data pages. If the space consumed by your data drops from 700GB to 350GB, then the size of the backups should be about half what it was as well, and the time to take the backup should indeed be similarly reduced.

Related Solutions

Sql-server – Shrinking Reluctant Log Files

This is one of those exceptions where it's probably okay to execute a DBCC SHRINKFILE. The log file is most likely this large not because of what is going on right now but because there was a time when hourly transaction logs were not being run (or you had an exceptionally large transaction that ran between or across log backups). I'm not sure where the misconception comes from, but backing up the log does not shrink the file for you, it only frees up space within the file.

You can perform a one-time operation like this without interfering with the log chain (or having to "hope" that "nothing happens in the meantime"), preferably right after a log backup has completed successfully:

DBCC SHRINKFILE(yourdb_log, 4000);

This should shrink the file to ~4 GB, which hopefully is plenty of room to accommodate your hourly transactions. You may want to pick a different size, I'm not sure...

If this command doesn't shrink the file as much as you expect, you can check why the log modification may be blocked using:

SELECT log_reuse_wait_desc
  FROM sys.databases
  WHERE name = N'yourdb';

Failure Of Backup Due To Limit Exceeded For Recovery Files Since Cannot Reclaim Disk Space [RMAN-03009,ORA-19809,ORA-19804]

The db_recovery_file_dest includes a variety of files, including online AND archived redo log files; take a look at the Oracle Backup and Recovery User's Guide chapter 5 to find out the best solution; disabling db_recovery_file_dest is not a good idea. You can try

RMAN> report obsolete;

RMAN command to see what can be deleted. Then do a delete obsolete to delete them. Do you have any guaranteed restore points defined?

RMAN> list restore point all;

If you see any results with type GUARANTEED, if they are no longer needed, remove them:

SQL> DROP RESTORE POINT before_app_upgrade;

Is flashback database enabled?

Best Answer

Related Solutions

Sql-server – Shrinking Reluctant Log Files

Failure Of Backup Due To Limit Exceeded For Recovery Files Since Cannot Reclaim Disk Space [RMAN-03009,ORA-19809,ORA-19804]

Related Question