Table Size from SQL

Question

Hi team ,

  How to identify the table size from Database for past 10 days. i.e Table name <ABCD> , i need to know the size of the table for past 10 days.How much size has been grown day wise ? like this information. 

please share me if any query is there to obtain the same through query analyzer 

Answer 1

One way to get the table size in MB is

SELECT convert(bigint, reserved) * 8192 / 1000000
FROM   sysindexes
WHERE  id = object_id('tablename')
  AND  indid IN (0, 1)

You need to run this every day (or every tenth day) to be able to make any calculations; SQL Server does not save any historic data for you.

---

Erland Sommarskog, SQL Server MVP, [e-mail]

Answer 2

Check this post it may be useful

http://alihamdar.com/2012/04/27/how-to-find-big-tables-in-your-database/

---

Ali Hamdar (alihamdar.com - www.ids.com.lb)

Answer 3

You can use sp_spaceused to get current size of the table, but need to run everyday to get growth info.

Answer 4

You have to setup a size logging table, write a stored procedure to record the size and schedule the sp as a job for periodic execution.  Table size blog post:

http://www.sqlusa.com/bestpractices2008/table-sizes/

---

Kalman Toth SQL SERVER 2012 & BI TRAINING
New Book: Beginner Database Design & SQL Programming Using Microsoft SQL Server 2012

Answer 5

Please see the below link

http://social.msdn.microsoft.com/Forums/en-US/sqlgetstarted/thread/d4ff577d-d8a5-4886-a9da-de9850f0fa3c/

---

ESHANI. Please click "Mark As Answer" if a post solves your problem or "Vote As Helpful" if a post has been useful to you

Answer 6

SQL Server does not keep a history of the table sizes over time automatically.

SP_SPACEUSED 'tablename' command can be used to find the size of the table at that specific time.

As mentioned by others too in this thread, if you want to see size change over time then you'll have to create a job/process that captures the output of SP_SPACEUSED (or other script are possible too) at a defined interval i.e. daily, weekly fortnightly etc. As this process builds the size history, you can create reports based on that data.

One of the methods to implement such a process is at the below URLs. They are quick reads so I'd suggest that you read both of them.

http://www.techrepublic.com/blog/datacenter/capturing-sql-server-2005-database-file-size-information/292?tag=rbxccnbtr1
http://www.techrepublic.com/blog/datacenter/monitor-database-file-sizes-with-sql-server-jobs/296?tag=rbxccnbtr1

---

Please mark this reply as the answer or vote as helpful, as appropriate, to make it useful for other readers.
Thanks!
Aalam | Blog (http://aalamrangi.wordpress.com)

Answer 7

Hi

Every x amount of days check using sql table size and insert into a table (snapshot it) and then compare.

Hope that helps

Emil

---

Katie & Emil Microsoft SQL BI related blog & tutorials SQL Tutorial | SSRS Tutorial | SSIS Tutorial

Answer 8

IF object_id('tempdb..#TableSize') IS NOT NULL

BEGIN

DROP TABLE #TableSize

END

 

create table #TableSize (name varchar(150), rows int, reserved varchar(150)

,data varchar(150), index_size varchar(150), unused varchar(150))

 

insert into #TableSize

EXEC sp_MSforeachtable @command1='EXEC sp_spaceused ''?'''

select name, cast(replace(data, ' KB','') as int)/1024 as TableDataSizeMB

from #TableSize

order by cast(replace(data, ' KB','') as int) desc

drop table #TableSize

---

Shamas Saeed (if Post helpful please mark as Answer) http://shamas-saeed.blogspot.com