+91-9464001760
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 351 views

Hello,

 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

GO

 

SELECT

  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

FROM

  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

WHERE

  t.Name NOT LIKE 'dt%'

  AND t.is_ms_shipped = 0

  AND i.object_id > 255

GROUP BY

  t.Name, s.Name, p.Rows

ORDER BY

  t.Name;

GO


Post by: Sarabpreet Singh
Contact author: click here

Key Words

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