SQL Server – Get TOP 5 Tables Consuming Maximum Space

I always say that database sizing is one of the prime tasks and concern for any DBA. As a DBA you should ensure that there is no single record claiming space in the databasefor no reason.

DBA’s should keep an eye on table size regularly. Unreasonable data or tables should be chopped off as soon as possible. People have a habit of backing up a table on production database before performing any critical DML – update/delete.

Unfortunately 8 out of 10 times they forget to delete the backup tables. Such tables may be huge. This may increase the time taken to backup the database and szie of the backup file.

Here is the strored procedure to find the top 5 tables consumiung maximum space in the given database. This will help DBA’s in cleaning up unnecessary data or creating indexes for better performance.

Create this procedure inside the target database.

Execute the procedure: exec uspDBASpaceUsed

 P.S: In case you are unable to free up space from table, refer https://www.sqlservergeeks.com/author/riteshmedhe/#493

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[uspDBASpaceUsed]') AND type in (N'P', N'PC'))
 
DROP PROCEDURE [dbo].[uspDBASpaceUsed]
GO
  
 
CREATE PROCEDURE uspDBASpaceUsed 
 
AS
BEGIN
  
SET NOCOUNT ON
  
DECLARE @cnt INT
DECLARE @Str VARCHAR(MAX)
DECLARE @TABName VARCHAR(100)
SET @cnt = 0
  
IF EXISTS(SELECT 1 FROM tempdb.sys.tables where name = '#TABS')
BEGIN
     DROP TABLE #TABS
END
  
IF EXISTS(SELECT 1 FROM tempdb.sys.tables where name = '##SPaceUsed')
BEGIN
      DROP TABLE ##SPaceUsed
END
  
SELECT 
      IDENTITY(INT,1,1) IDX, 
      name 
INTO 
      #TABS 
FROM 
      SYS.TABLES 
WHERE 
      TYPE = 'U'
  
SELECT @cnt = COUNT(*) FROM #TABS
  
CREATE TABLE ##SPaceUsed 
(
name VARCHAR(100), 
records INT, 
reserved VARCHAR(1000), 
data VARCHAR(1000), 
index_size VARCHAR(1000), 
unsued VARCHAR(1000)
)
	  
WHILE @cnt > 0
BEGIN 
     SET @Str = ''
      
      SELECT @TABName = name FROM #TABS WHERE IDX = @cnt
      SELECT @Str = 'insert ##SPaceUsed exec sp_spaceused [' + @TABName + ']'
       
      EXEC (@STR)
  
SET @cnt = @cnt - 1
END
  
UPDATE ##SPaceUsed SET reserved = REPLACE(reserved,'KB','')
UPDATE ##SPaceUsed SET data = REPLACE(data,'KB','')
UPDATE ##SPaceUsed SET index_size = REPLACE(index_size,'KB','')
UPDATE ##SPaceUsed SET unsued = REPLACE(unsued,'KB','')
	  
SELECT TOP 5
      name [Table], 
      CAST(data AS INT)/1024 AS [DATA (MB)] 
FROM 
      ##SPaceUsed 
ORDER BY 2 DESC
  
SET NOCOUNT OFF
  
END

Regards

Ritesh Medhe

Like us on FaceBook  |  Follow us on Twitter

Join the fastest growing SQL Server group on FaceBook