29-404, LPU, Jal-Del G.T. Road - Phagwara, PB, India
09:30 am – 05:30 pm

Insufficient Disk-space in MS SQL Database!

Published On: Sunday, March 3, 2019 422 views


 One needs to be always cautious about the physical size of the back-end database to prevent certain scenarios which can cause the front-end application to malfunction due to age old problem of running out of storage space!

 Recently I had used an IoT Device to stream data collected from few deployed sensors and save all that into a back-end MS SQL Database which over a period of time ran out of the storage space on the server. Yeah! Imagine how many rows were dumped into the database system to cause this scenario. Anyways, to remedy the  situation following SQL Script  was extremely useful:


use "YourDatabaseName"

exec sp_spaceused;


USE YourDatabaseName




  t.Name                                       AS TableName,

  s.Name                                       AS SchemaName,

  p.Rows                                       AS RowCounts,

  SUM(a.total_pages) * 8                       AS TotalSpaceKB,

  SUM(a.used_pages) * 8                        AS UsedSpaceKB,

  (SUM(a.total_pages) - SUM(a.used_pages)) * 8 AS UnusedSpaceKB


  sys.tables t

  INNER JOIN sys.indexes i ON t.object_id = i.object_id

  INNER JOIN sys.partitions p ON i.object_id = p.object_id AND i.index_id = p.index_id

  INNER JOIN sys.allocation_units a ON p.partition_id = a.container_id

  LEFT OUTER JOIN sys.schemas s ON t.schema_id = s.schema_id


  t.Name NOT LIKE 'dt%'

  AND t.is_ms_shipped = 0

  AND i.object_id > 255


  t.Name, s.Name, p.Rows




Post by: Sarabpreet Singh
Contact author: click here

Key Words

storage green JOIN Recently index_id object_id problem stream height Name schema_id cause physical layout Hello database about cautious always used_page autospace into space from need total_page which blue front YourDatabaseName INNER running prevent Device application malfunction proof normal scenario back