Many clients are using custom stored procedures or third party tools to backup databases in production environments instead of using database maintenance plans. One of the things that you need to do is to maintain the number of backup files that exist on disk, so you don’t run out of disk space. There are several techniques for deleting old files, but in this tip I show how this can be done using PowerShell.
Problem: Often we would like to check how much size backup file will take on the disk? You can follow below simple steps to estimate the backup file size?
Execute the SP_SPACEUSED procedure to get the database size statistics.
EXEC sp_spaceused @updateusage = ‘true’
Now you have to subtract the ununsed pages from reserved pages and divided by 1024 to get the output in MB.
In our scenario we have 2592 (KB) – 96 (KB) = 2496/1024 = 2.4374(MB) will be the backup size.