June 26, 2007 at 9:00 am
Hi
There is an sys.sp_statistics to get the page size. Writing that into a new table would save history data about the table growth or? Or is there a sys_dm_x which does that?
Whats the best way to observe table growth?
Thanks, Jan
June 26, 2007 at 11:32 am
I usually use sys.sysindexes system view and/or sp_spaceused procedure.
June 27, 2007 at 12:01 am
Create a table that can be used to store the values obtained using the sp_spaceused procedure. Then schedule the procedure to run every day ot week as per your requirement. Then use that table to compare the growth between the last run and the current run details. I know this is not a straight forward way but this too can be a solution. Any suggestion are really welcome.
Cheers,
Sugeshkumar Rajendran
SQL Server MVP
http://sugeshkr.blogspot.com
June 27, 2007 at 2:34 pm
Can I get the values from the sp_spaceused without changing the sp?
Because there are no values declared as output.
Thanks, Jan
June 27, 2007 at 2:39 pm
Crate temp table that matches the output of sp_spaceused
insert into #temp (col, list) exec sp_spaceused ...
June 28, 2007 at 5:43 am
create table spacedetails (name varchar(100),totrows bigint,reserved varchar(30), data varchar(30),index_size varchar(30), unused varchar(30))
insert into spacedetails
exec sp_MSForeachtable @command1 = "sp_spaceused '?'"
select name,totrows, replace(data,'KB','') as 'Data in KB',
replace(Index_Size,'KB','') as 'Index Size in KB' from spacedetails order by name
use this as a base and then manipulate as mentioned by me in the previous post.
Cheers,
Sugeshkumar Rajendran
SQL Server MVP
http://sugeshkr.blogspot.com
June 28, 2007 at 7:02 am
This should do what you want.
Script to analyze table space usage
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply