+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 195 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

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