Blob Storage

• Edward Shaw

  Ten Centuries

  Points: 1020

  More actions

  May 30, 2019 at 9:27 pm

  #3648739

  I was just asked if we could determine how much storage is being used by BlOB across all databases on a server (have about 100 DB's).  Anyone have any idea how to do this, or if you have a script to share, would love that!!!

   

  Thanks.

• oogibah

  SSC Eights!

  Points: 827

  More actions

  May 31, 2019 at 4:20 pm

  #3649085

  so.. this script will identify objects that use the blob data type...

   

  SELECT o.[name], o.[object_id ], c.[object_id ], c.[name], t.[name]
  FROM sys.all_columns c
  INNER JOIN sys.all_objects o
  ON c.object_id = o.object_id
  INNER JOIN sys.types t
  ON c.system_type_id = t.system_type_id 
  WHERE c.system_type_id IN (35, 165, 99, 34, 173)
  AND o.[name] NOT LIKE 'sys%'
  AND o.[name] <> 'dtproperties'
  AND o.[type] = 'U'
  GO

   

  From there you can probably query to find the overall size of the objects but I don't know if you can find out the total size of the space used specifically for a certain datatype

• Chris Harshman

  SSC-Forever

  Points: 42192

  More actions

  May 31, 2019 at 8:14 pm

  #3649156

  You should check out the DMV sys.dm_db_partition_stats:

  https://docs.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-db-partition-stats-transact-sql?view=sql-server-2017

  This has columns lob_used_page_count, lob_reserved_page_count, and can be queried like this:

  SELECT object_id, index_id, SUM(used_page_count) / 128 AS size_MB, SUM(used_page_count) AS used_page_count FROM sys.dm_db_partition_stats
  GROUP BY object_id, index_id;