Get sql table size and last updated for all tables

Part of the DBA job is Capacity Planning. Understanding future requirements is built into the database management process. Although future requirements are important to understand , maintaining the current database objects is important .One of the common tasks is creating reports presenting details about table sizes , row counts and usage. A regular audit of the database can discover small problems , maintaining good SQL Server performance

The DBA can work with the owner in understanding about table usage and index usage . Typical problems to solve are:

1)       Should data be archived? If time based data is not required , can it be moved to a different partition?

2)       Should a non-clustered index be dropped ? The Non Clustered Index may have been included , but the queries are no longer relevant. Maintaining the index adds an overhead – for a data updates and regular index management tasks.

3)       Are there application tasks creating Temporary tables , which are not cleared up?

 Any database server instance has  specific data requirements, presenting the information effectively to a non-DBA is a good starting point in understanding the problems and looking for solutions.

 Run this script on a database to present data for every index\heap in every table on a database, including row number, data space usage, percent of database, last user seek, last user scan, last user lookup.

I usually place this information in an Excel spreadsheet and work with the owner to analyse

use mydb
go
------------------SCRIPT ----------START-------------------------

-- Create the temp table for further querying

CREATE TABLE #temp(

tbl_id int IDENTITY (1, 1),

tbl_name varchar(128),

rows_num int,

data_space decimal(15,2),

index_space decimal(15,2),

total_size decimal(15,2),

percent_of_db decimal(15,12),

db_size decimal(15,2))

-- Get all tables, names, and sizes

EXEC sp_msforeachtable @command1="insert into #temp(rows_num, data_space, index_space) exec sp_mstablespace '?'",

@command2="update #temp set tbl_name = '?' where tbl_id = (select max(tbl_id) from #temp)"

-- Set the total_size and total database size fields

UPDATE #temp

SET total_size = (data_space + index_space), db_size = (SELECT SUM(data_space + index_space) FROM #temp)

-- Set the percent of the total database size

UPDATE #temp

SET percent_of_db = (total_size/db_size) * 100;

-- Get the data
--CTE : basic index information for every index
--join CTE output with the table size information
WITH temp_table(tbl_name,idx_name,last_user_update,user_updates,last_user_seek,last_user_scan,last_user_lookup,user_seeks,user_scans,user_lookups)
AS(
SELECT 
'[' + schema_name(tbl.schema_id) + '].['+object_name(ius.object_id)+']'
,six.name
,ius.last_user_update
,ius.user_updates
,ius.last_user_seek
,ius.last_user_scan
,ius.last_user_lookup
,ius.user_seeks
,ius.user_scans
,ius.user_lookups
FROM
sys.dm_db_index_usage_stats ius 
INNER JOIN sys.tables tbl ON (tbl.OBJECT_ID = ius.OBJECT_ID)
INNER JOIN sys.indexes six ON six.index_id = ius.index_id and six.object_id = tbl.OBJECT_ID
WHERE ius.database_id = DB_ID()

)
select t1.tbl_name,t2.idx_name,t1.rows_num,t1.data_space,t1.index_space,t1.total_size,t1.percent_of_db,t1.db_size,
t2.last_user_update,t2.user_updates,t2.last_user_seek,t2.last_user_scan,t2.last_user_lookup,t2.user_seeks,t2.user_scans,t2.user_lookups from 
#temp t1 LEFT JOIN temp_table t2 ON t1.tbl_name = t2.tbl_name
ORDER BY t1.total_size DESC

-- Comment out the following line if you want to do further querying

DROP TABLE #temp

--------------------SCRIPT FINISH----------------------------------------------

 Read More

 SQL Server - Capacity Planning

7 habits of highly effective DBAs - SQL Server DBA

Calculate table size with existing data - SQL Server DBA

SQL Server - How to find the largest sql index and table size - SQL ...