February 23, 2010 at 1:43 pm
One of our production database size is 550 MB and backup file size for that database is 320 MB.
Just wondering, why is difference in sizes?
February 23, 2010 at 1:57 pm
A backup is the size of the USED space within a database file. Backup does not backup empty extents.
run sp_spaceused against he database, you will see that 'reserved' amount is very close to the backup size.
---------------------------------------------------------------------
February 23, 2010 at 1:57 pm
Most likely, there's empty space available in the database files and/or log files.
Right-click the database in Management Studio, go to Properties, and look for the Size and Space Available data.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
February 23, 2010 at 2:06 pm
:w00t: You allocated certain space for the database, but not all is used. Some is data, some indexes, etc. Every single object you create uses some space. At the time the backup is run, it returns the real spaced used by your DB.
You can see that when you select shrink database. You will see the size of the database and some free space (don't shrink if you don't need, is only a reference at this time) 😎
Sounds very simple ha? but it is not that simple. Check BOL to understand how the space is set within the database (pages and the size of the page and how, it uses the space in one page and some in the next one. It also has to do with the fill factor for the indexes, etc. Good luck and welcome to the bizarro!!! world of SQL Server!!! Is fun though :hehe:
February 23, 2010 at 2:09 pm
Fernando-235287 (2/23/2010)
:w00t: You allocated certain space for the database, but not all is used. Some is data, some indexes, etc. Every single object you create uses some space. At the time the backup is run, it returns the real spaced used by your DB.You can see that when you select shrink database. You will see the size of the database and some free space (don't shrink if you don't need, is only a reference at this time) 😎
Sounds very simple ha? but it is not that simple. Check BOL to understand how the space is set within the database (pages and the size of the page and how, it uses the space in one page and some in the next one. It also has to do with the fill factor for the indexes, etc. Good luck and welcome to the bizarro!!! world of SQL Server!!! Is fun though :hehe:
Thanks Fernando!!!
great reply
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply