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

   

6 Comments on “SQL Server – Get TOP 5 Tables Consuming Maximum Space”

  1. Hi Ritesh,

    Thanks for your effort in building this script.

    Just an FYI… We are already having a Standard report “Disk Usage by Top Tables” which serves this purpose.

    This can be accessed by

    1. Right-Click on the Database

    2. Choose “Reports” then “Standard Reports”

    3. Choose “Disk Usage by Top Tables” Report

    This is give the data in KB and also these kinds of reports can be exported to pdf, word or excel.

  2. Sandesh: I agree with you.

    The way I have implemented this is my organization is as follows

    1. Scheduled a job that executes the procedure on daily basis. The data is captured in a table. This helps us in understanding the growth trend. You may observe that a particular table is barged with loads of data in a partcular week. This way you can predict the anticipated load.

    2. I get statistics for about 100+ databases from spanned across 50 odd servers in mail only daily basis. Manually going and checking the space consumed by tables will be tedious.

  3. SearchSuggestion(Table) 764(MB)
    LogQuery 628
    dictionary 91
    DictionaryDetails 36
    url_log 20

    Pls tell me what “ll be my next step.

  4. Ajitesh: Excpet for first two tables rest all seem to be small.

    How big is you databases?

    Is it OLTP database or part of ETL?

    Run sp_spaceused on SearchSuggestion & LogQuery tables and check what value is showned under unused space. If % of unusedspace is more >50% then you may want to run rebuild command. Please refer my post https://www.sqlservergeeks.com/blogs/riteshmedhe/sql-server-bi/405/sql-getting-rid-of-unused-space-in-table#493 for further details

    Thanks

  5. Hi Ritesh,

    Thank for creating such a script.

    Can you please explain what all are the reports and thing we need to analyse in a database for maintenance. Because I am an accidental DBA. Please provide me a check list if you have.

    Thanks in advance,
    Sudeesh

Leave a Reply

Your email address will not be published.