January 30, 2017 at 3:12 am
As a "technical DBA" i rarely get to work with the actual data in the databases, mostly installations, permissions and kerberos stuff.
While investigating a warning about low disk space left on a data disk, i got curious and looked at the data, i started with SSMS report "disk usage by top tables"
I found a few tables that are no longer used and where able to remove the indexes on them to save space while the gears are grinding the change request to remove them all together, then i found this XML table with 5000 rows, 40gb of data but 314gb of reserved diskspace! 274 824 424 KB are indexes and it also states that 274 877 416 KB are unused!
My first thought is that basically the entire index is emtpy and just hogging alot of space!
The table has a clustered index and a Primary XML index and the XML index has a 99,5% fillfactor!
I dont get it, is it a bug in the report from SSMS?
Is there a batter way of finding these "holes" (tables with a lot of unused space) in the database?
any and all replys are greatly appreciated!
/J
January 30, 2017 at 3:27 am
jCoke - Monday, January 30, 2017 3:12 AMAs a "technical DBA" i rarely get to work with the actual data in the databases, mostly installations, permissions and kerberos stuff.While investigating a warning about low disk space left on a data disk, i got curious and looked at the data, i started with SSMS report "disk usage by top tables"
I found a few tables that are no longer used and where able to remove the indexes on them to save space while the gears are grinding the change request to remove them all together, then i found this XML table with 5000 rows, 40gb of data but 314gb of reserved diskspace! 274 824 424 KB are indexes and it also states that 274 877 416 KB are unused!My first thought is that basically the entire index is emtpy and just hogging alot of space!
The table has a clustered index and a Primary XML index and the XML index has a 99,5% fillfactor!
I dont get it, is it a bug in the report from SSMS?
Is there a batter way of finding these "holes" (tables with a lot of unused space) in the database?
any and all replys are greatly appreciated!
/J
Quick question, what does sp_spaceused return?
😎
January 30, 2017 at 4:21 am
Eirikur Eiriksson - Monday, January 30, 2017 3:27 AMjCoke - Monday, January 30, 2017 3:12 AMAs a "technical DBA" i rarely get to work with the actual data in the databases, mostly installations, permissions and kerberos stuff.While investigating a warning about low disk space left on a data disk, i got curious and looked at the data, i started with SSMS report "disk usage by top tables"
I found a few tables that are no longer used and where able to remove the indexes on them to save space while the gears are grinding the change request to remove them all together, then i found this XML table with 5000 rows, 40gb of data but 314gb of reserved diskspace! 274 824 424 KB are indexes and it also states that 274 877 416 KB are unused!My first thought is that basically the entire index is emtpy and just hogging alot of space!
The table has a clustered index and a Primary XML index and the XML index has a 99,5% fillfactor!
I dont get it, is it a bug in the report from SSMS?
Is there a batter way of finding these "holes" (tables with a lot of unused space) in the database?
any and all replys are greatly appreciated!
/J
Quick question, what does sp_spaceused return?
😎
Thanks for the quick reply!
Sp_spaceused returns a different value: unused 53 504 KB
I wonder why the reports are off by such a wide margin? could it be a bug?
/J
January 30, 2017 at 4:32 am
jCoke - Monday, January 30, 2017 4:21 AMEirikur Eiriksson - Monday, January 30, 2017 3:27 AMjCoke - Monday, January 30, 2017 3:12 AMAs a "technical DBA" i rarely get to work with the actual data in the databases, mostly installations, permissions and kerberos stuff.While investigating a warning about low disk space left on a data disk, i got curious and looked at the data, i started with SSMS report "disk usage by top tables"
I found a few tables that are no longer used and where able to remove the indexes on them to save space while the gears are grinding the change request to remove them all together, then i found this XML table with 5000 rows, 40gb of data but 314gb of reserved diskspace! 274 824 424 KB are indexes and it also states that 274 877 416 KB are unused!My first thought is that basically the entire index is emtpy and just hogging alot of space!
The table has a clustered index and a Primary XML index and the XML index has a 99,5% fillfactor!
I dont get it, is it a bug in the report from SSMS?
Is there a batter way of finding these "holes" (tables with a lot of unused space) in the database?
any and all replys are greatly appreciated!
/J
Quick question, what does sp_spaceused return?
😎Thanks for the quick reply!
Sp_spaceused returns a different value: unused 53 504 KB
I wonder why the reports are off by such a wide margin? could it be a bug?
/J
Haven't found an error in sp_spaceused yet but then again I don't use the reports so I cannot tell if there is a bug or comparison mismatch.
😎
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply