Need to Predict Backup Sizes?

Someone showed me a neat trick a little while ago that I thought I would pass on.  The problem is I wrote down the trick but not who told it to me, so if you are reading this and you think it was you who told me please let me know and I will cite you properly.  I apologize for this transgression but I thought the tip was appropriate given my recent posts concerning backup and recovery and I was searching through some old notes looking for something to blog about tonight.

Have you ever added a new database to your server and thought to yourself, hey do I have enough space on my backup drive to cover however many days you are backing up?  Here is an easy way to get a general idea for an uncompressed backup size.  If you are running compression, then it will obviously be less but this is an estimate.

USE <Database Name>
EXEC sp_spaceused @updateusage = ‘TRUE’

Which for a particular database on one of my servers it returned:

database_name database_size unallocated_space
<Database Name> 2449.81 MB 40.45 MB
reserved data index_size unused
2396472 KB 2386856 KB 5144 KB 4472 KB

The actual uncompressed backup for this database was 2,317,543 KB and the reserved data from the stored procedure was 2,396,472 which is pretty close to the actual backup.  Not bad, huh?  I hope this helps.


About SQLGator

Microsoft Data Platform MVP, Florida Gator, Star Wars fanatic and is there anything else...oh yeah PS4! I am a geek and SQL Server Business Intelligence Consultant, there are other technologies greater than these? Not so fast my friend! I also love to travel to new and exotic places.

Posted on February 11, 2013, in Maintenance and tagged . Bookmark the permalink. 1 Comment.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

%d bloggers like this: