June 4, 2008 at 9:56 pm
Hello Experts
My manager has assigned a task to me to map table growth in a database. It should cover all the tables in the database. After my research on internet i found a script which tells me table sizes. For your reference i have attached the script.
By looking at the results of the script, i thought it would be better to store results in a table say tableGrowth and then find the growth of each table from the available data. In order to acheive this, i have to add few more columns to tableGrowth table like id as an identity, date of calculation.
But there is a problem in my thought. How will i calculate the growth trend for each table in tableGrowth table.
is there any other better way to acheive this task? At this stage, i am in my planning phase of how to complete this task. Any comments or advices are most welcome. 🙂
June 4, 2008 at 10:16 pm
Here is the script I use to view table sizes. You can replace the temp table with a permenant table and add the date and snapshot on a regular basis.
CREATE TABLE #tmp
(
name VARCHAR(255),
rows INT ,
reserved varchar(255),
data varchar(255),
index_size varchar(255),
unused varchar(255)
)
INSERT into #tmp
EXEC sp_MSForEachtable 'sp_spaceused ''?'''
SELECT *,
CASE WHEN (replace(reserved,'KB','') * 8 / 1024.0) > 1000
THEN CAST(CAST(((replace(reserved,'KB','') * 8 / 1024)/1024.0) as decimal(18,2)) as varchar(20)) + ' GB'
ELSE CAST(CAST((replace(reserved,'KB','') * 8 / 1024.0) as decimal(18,2)) as varchar(20)) + ' MB' END ReservedConverted
FROM #tmp order by cast(replace(reserved,'KB','') as int) desc
DROP TABLE #tmp
June 4, 2008 at 11:19 pm
Ken
your script uses sp_MSForEachtable which is an undocumented procedure. As for knowledge, is it advisable to use undocumented procedures on production server as i cant hope for any support (if required) from MS.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply