When you’re creating your database backup maintenance plans, you need to choose a drive that has enough space for your backup. So how much space will you actually need to backup your database? Figuring this out is actually a lot easier than you would expect.
Although nothing will be 100% accurate, SQL Server has a built in stored procedure for calculating the used space in a database. A full database backup only stores the actual data/objects in the database. The unused space is not stored.
EXEC sp_spaceused @updateusage = N'TRUE'
In the example above we call the sp_spaceused stored procedure from the database that we would like the size from. This is important… you need to execute this from the database that you want to get the size for. This will return a dataset with multiple columns. The column that you will look at is the reserved column. This will show the amount of space without the unused space.
Note
The @updateusage parameter tells the stored procedure to update the space usage info before returning the used size. Passing TRUE in to this parameter will give us the most accurate size estimate.