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

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