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
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”
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.
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.
Pls tell me what “ll be my next step.
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
Thanku Sir…for ur support..
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,