September 11, 2018 at 4:29 pm
Hi,
I am trying to provide our IT team with a rough estimate of disk space requirements and growth rates for our data warehouse. My initial strategy was to figure out the current total reserved space per table, then use current record count and record growth rates to plan out. I used the "Disk Usage by Table" canned report in SSMS (right click on the DB, select reports -> standard reports -> Disk Usage by Table). This seemed great, but then I ran into an issue... I totaled the "Reserved (KB)" column and compared it to the values on the "Disk Usage" canned report - I can't seem to reconcile the 2 reports. Here are the numbers I'm seeing:
- Disk Usage Report, Total Space Reserved: 496.1 GB
- Disk Usage Report, Data Files Space Reserved: 301,396.44 MB (294.33 GB)
- Disk Usage Report, TX Log Space Reserved: 204,522.19 MB
- Disk Usage Report, Disk Space Used by Data Files, Space Used: 154.97 GB
- Disk Usage by Table Report, Reserved (KB) - this is the total for all tables: 160,310,416 (or 153.53 GB)
I was expecting the total reserved from the "disk usage by table" report (153.53 GB) to match the "disk usage report, data files space reserved" figure (294.33 GB). What am I missing here? What's taking up the remaining 140GB in the data file that isn't represented in the tables report?
September 11, 2018 at 5:17 pm
chris.o.smith - Tuesday, September 11, 2018 4:29 PMHi,
I am trying to provide our IT team with a rough estimate of disk space requirements and growth rates for our data warehouse. My initial strategy was to figure out the current total reserved space per table, then use current record count and record growth rates to plan out. I used the "Disk Usage by Table" canned report in SSMS (right click on the DB, select reports -> standard reports -> Disk Usage by Table). This seemed great, but then I ran into an issue... I totaled the "Reserved (KB)" column and compared it to the values on the "Disk Usage" canned report - I can't seem to reconcile the 2 reports. Here are the numbers I'm seeing:
- Disk Usage Report, Total Space Reserved: 496.1 GB
- Disk Usage Report, Data Files Space Reserved: 301,396.44 MB (294.33 GB)
- Disk Usage Report, TX Log Space Reserved: 204,522.19 MB
- Disk Usage Report, Disk Space Used by Data Files, Space Used: 154.97 GB- Disk Usage by Table Report, Reserved (KB) - this is the total for all tables: 160,310,416 (or 153.53 GB)
I was expecting the total reserved from the "disk usage by table" report (153.53 GB) to match the "disk usage report, data files space reserved" figure (294.33 GB). What am I missing here? What's taking up the remaining 140GB in the data file that isn't represented in the tables report?
You aren't accounting for any unused space. Disk usage data files space reserved includes unused space.
Sue
September 12, 2018 at 2:01 am
Hi,
try this kind of query:
There is a column with unused space. And by the way, much more helpful than the reports in the SSMS.
Kind regards,
Andreas
September 12, 2018 at 9:00 am
Thanks Andreas and Sue. From what I can tell, the "Disk Usage by Table" reserved space does include unused space. That report has 4 columns: Reserved, Data, Indexes, Unused. The math works out as follows: Reserved = Data + Indexes + Unused. However, I think this cleared things up for me (and seems rather obvious now). The "disk usage by tables" report does not include unallocated space, since it has not been allocated to an object yet. On the other had, the "disk usage data files space reserved" is data + indexes + unused + unallocated. Thank you both for your help!
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply