3

We have 2 Databases whose backup files are close to 900 GB each.Backup Drive is 2 TB.Now the drive is fully occupied.The Database Backup files are compressed ones.

Database size in sp_helpdb shows as 171250 MB and 182350 MB.

Each week we have full backups and each day we have diff backups and .trn logs every 15 minutes. Last full backup size is getting close to 960 GB.

My question is apart from increasing backup drive size.what else should think of to free up some space.The drive contains all wanted files.

Thanks

closed as unclear what you're asking by mustaccio, Erik, Max Vernon, Philᵀᴹ, Tom V Feb 2 '17 at 18:29

Please clarify your specific problem or add additional details to highlight exactly what you need. As it's currently written, it’s hard to tell exactly what you're asking. See the How to Ask page for help clarifying this question. If this question can be reworded to fit the rules in the help center, please edit the question.

  • 1
    Looks like you are out of option. One not so good advise will be before backup starts delete copy the files to other location and delete it and then create new backup – Shanky Feb 2 '17 at 13:41
  • 1
    How big are the databases? When you say backup files are you talking about *.bak, *.dif and *.trn files? Or just two simple *.bak files? – hot2use Feb 2 '17 at 13:47
  • 1
    What's your retention policy? Are you only able to store 1 day's full backup of 2 databases or does "900 GB each" represent full and tlog backups? Given the size of the backups as they currently are, I hope you've at least considered differential backups. Please add some extra details so we can further help you. – SQL_Underworld Feb 2 '17 at 14:36
  • 1
    @SQL_Underworld,I updated my question.Please see,Thank you. – SQLBoy Feb 2 '17 at 14:42
  • @hot2use,yes sir,it contains both Full and Diff backups.Even Diff backups extension is .bak.Thank you. – SQLBoy Feb 2 '17 at 14:42
1

You have options

  • Move away the backup files to a network share or a central backup server. Based on your company's retention policy, the backup can be archived to tape or just deleted (Caution !). This way if you lost the backup drive, you still have backups.
  • For VLDBs, you have to look into a good workable (meeting your company's RTOsand RPOs) backup (and restore) strategy.

The take away is to do proper storage planning. Enable Instant file initialization and yes do test your restores ... since a backup is no good unless you can restore it !

1

A creative option from a seven-year-old Brent Ozar post includes:

  1. Scripting off your non-clustered indexes and dropping them before a backup and...
  2. Rebuilding your tables with a higher fill factor

NOTE: Don't immediately try this in production, as the article points out.

If you don't want to pursue the above, here are a couple things to check and report back on:

  • Run a script to see how much space NC indexes are currently consuming
  • Ensure your databases' data file sizes (size and maxsize, if applicable) aren't over-allocated. (I noticed those nice, round database sizes and wondered...)

Lastly, check out 3rd-party backup tools, such as Dell's Avamar or Quest's LiteSpeed. I'm going to assume, however, that you don't want to go down this route if you're looking for options apart from adding more space to your backup repository (i.e. you may be constrained by the cost of these options).

1

Several things immediately come to mind that deal with the databases themselves:

  • Verify that you are using a Fill Factor that maximizes the fullness of each page. This can have a downside on performance due to page splits, but having your pages as full as possible could reduce your backup size.
  • Make sure you are aggressively running Reorganize and Rebuild maintenance.

    Rebuilding an index drops and re-creates the index. This removes fragmentation, reclaims disk space by compacting the pages based on the specified or existing fill factor setting, and reorders the index rows in contiguous pages.

    Reorganizing an index uses minimal system resources. It defragments the leaf level of clustered and nonclustered indexes on tables and views by physically reordering the leaf-level pages to match the logical, left to right, order of the leaf nodes. Reorganizing also compacts the index pages. Compaction is based on the existing fill factor value.

  • Do some investigation to ensure that the data in the database is still needed. Look for one-off 'save' tables that people create and never go back and delete.

Use a query like this to determine space used for each table

With TableSpaceUsage as
(
SELECT t.NAME AS TableName
    ,s.NAME AS SchemaName
    ,p.rows AS RowCounts
    ,SUM(a.total_pages) * 8 AS TotalSpaceKB
    ,SUM(a.used_pages) * 8 AS UsedSpaceKB
    ,(SUM(a.total_pages) - SUM(a.used_pages)) * 8 AS UnusedSpaceKB
FROM sys.tables t
INNER JOIN sys.indexes i ON t.OBJECT_ID = i.object_id
INNER JOIN sys.partitions p ON i.object_id = p.OBJECT_ID
    AND i.index_id = p.index_id
INNER JOIN sys.allocation_units a ON p.partition_id = a.container_id
LEFT OUTER JOIN sys.schemas s ON t.schema_id = s.schema_id
WHERE t.NAME NOT LIKE 'dt%'
    AND t.is_ms_shipped = 0
    AND i.OBJECT_ID > 255
GROUP BY t.NAME
    ,s.NAME
    ,p.Rows
)
select * from TableSpaceUsage order by UsedSpaceKB desc

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