Category Archives: Disk Management

Check Tables & Indexes size for your Database

Before accepting that your database is big because it needs all the data inside it and buy extra storage to overcome the potential space problem, you can find out if the existing space can be reduced by removing unused objects, indexes.

First find out what are the biggest tables:


DECLARE @query varchar(100)
    DECLARE @schema as sysname
    DECLARE @objectname as sysname
    IF (object_id('tempdb..#Table_Size') IS NOT Null)
    drop table [dbo].[#Table_Size]
    CREATE TABLE #Table_Size 
      ( TableName varchar(50),
        [RowCount] int,
        Table_Size varchar(50),
        Data_Space_Used varchar(50),
        Index_Space_Used varchar(50),
        Unused_Space varchar(50)
    DECLARE Object_Cursor CURSOR FOR
    SELECT,  FROM sysobjects o inner join sys.schemas s ON o.uid = s.schema_id
    WHERE xtype='U' 
    OPEN Object_Cursor
    FETCH NEXT FROM Object_Cursor 
    INTO  @schema, @objectname
    set @query = 'sp_spaceused ''[' + @schema + '].[' + @objectname + ']'''
    INSERT INTO #Table_Size EXEC(@query)
    FETCH NEXT FROM Object_Cursor 
    INTO   @schema, @objectname
    CLOSE Object_Cursor
    DEALLOCATE Object_Cursor
    select TableName,[RowCount],
    convert(NUMERIC(12,2) ,left(ltrim(rtrim(Table_Size)),len(ltrim(rtrim(Table_Size)))-2))/1024 [Table_Size (MB)],
    convert(NUMERIC(12,2) ,left(ltrim(rtrim(Data_Space_Used)),len(ltrim(rtrim(Data_Space_Used)))-2))/1024 [Data_Space_Used (MB)],
    convert(NUMERIC(12,2) ,left(ltrim(rtrim(Index_Space_Used)),len(ltrim(rtrim(Index_Space_Used)))-2))/1024 [Index_Space_Used (MB)],
    convert(NUMERIC(12,2) ,left(ltrim(rtrim(Unused_Space)),len(ltrim(rtrim(Unused_Space)))-2))/1024 [Unused_Space (MB)]
    from #Table_Size
    order by convert(int ,left(ltrim(rtrim(Table_Size)),len(ltrim(rtrim(Table_Size)))-2))  desc

After you have a list of biggest tables, check their Indexes. During my DBA work I have found many times unused indexes, doubled with same definition which not only occupied the disk space for nothing but also made worse the performance of any DML query as these should have been updated. So for a single Insert imagine updating 4 indexes. Sometimes these can be re-designed.


DECLARE @query varchar(100)
    DECLARE @index_id INT
    DECLARE @index_size BIGINT SET @index_size = 0
    DECLARE @name as NVARCHAR(500)
    DECLARE @indextype as NVARCHAR(256)
    DECLARE @TableName AS NVARCHAR(256)
    SET @TableName = 'TABLENAME'
    IF (object_id('tempdb..#Index_Table') IS NOT Null)
    drop table [dbo].[#Index_Table]
    CREATE TABLE #Index_Table 
     (	Table_Name		 varchar(250),
    	Index_ID		 varchar(250),
     	Index_Name		 varchar(250),
     	IndexType		 varchar(250),	
     	Index_Size_MB	 INT
    SELECT index_id, name FROM sys.indexes WHERE object_id = OBJECT_ID(@TableName) 
    OPEN Index_Table
    FETCH NEXT FROM Index_Table 
    INTO  @index_id, @name
    SELECT @index_size = @index_size + (avg_record_size_in_bytes * record_count)
    FROM sys.dm_db_index_physical_stats (DB_ID(), OBJECT_ID(@TableName), @index_id , NULL, 'DETAILED')
    set @index_size = @index_size / 1048576
    select @indextype = (index_type_desc) 
    from sys.dm_db_index_physical_stats (DB_ID(), OBJECT_ID(@TableName), @index_id , NULL, 'DETAILED')
    insert into #Index_Table values (@TableName,  @index_id,  @name, @indextype, convert(int ,@index_size))
    FETCH NEXT FROM Index_Table
    INTO   @index_id, @name
    CLOSE Index_Table
    DEALLOCATE Index_Table
    select * from  #Index_Table order by Index_Size_MB desc

DiskPart – How to find out whisk disk is distributed to a logical volume

There is one fast way to check out to what physical disk is allocated one logical disk. In this way you can allocate better the available storage between data files, log files and tempdb files. You should know that these are better separated on different disks.

You need to consider applying this method only to the servers which don’t have as physical storage SAN disks. The distribution of  logical disks on SAN is very different and must be provided by network admins.

The following commands don’t mess anything but play carefully with diskpart ’cause it’s a partitioning tool.

Diskpart.exe command-line utility basic commands:

Open a command prompt and type diskpart. Depending the Windows version, diskpart will open in the same window or a new window. When entering the utility you can use the following commands.

list disk – lists the disks known by Windows. At this point you will know how many disks there are to play with.

Diskpart - List Volume Partitions

select disk (Number) – sets the focus on the specified disk. you need to set the focus on one disk in order to check the partitions created on it or the logical volumes. All the below command need to have the focus set on a single disk.

detail disk – after setting the focus, you will see all the volumes created on the specified disk.

select partition – you can guess what that is – lists the partitions from one disk.

Diskpart - List Volume Partitions

It is now obvious that Disk 1 with a capacity of 931 GB hosts two logical Volumes- 683 and 243 each.