Track Table(s) size over time

[jhall01]

Anyone know of a good stored procedure or code to track table size and growth over time in MS SQL 2000?

I can pull the file size but don't know a good way to track table size. If i have to build a table that stores table sizes on a daily bases that would be fine.

Thanks in advance!

Jon

 

[nigelrivett]

What do you mean by size?

Number of rows see

http://www.nigelrivett.net/sp_GetRowsForAllTables.html

For the actual space you can do a similar thing with sp_spaceused - put the output into a temp table and insert the relevent fields into your table from there.

======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

 

[jhall01]

Table size in KB or MB

IE

Name rowcount size(KB) size(MB)
tableX 200000 22528 22

 

[jhall01]

I am thinking if i can get that much I can execute that on a daily basis to insert into a table so that I can record the information daily then run reports on growth vs. time

I know I need to use sysindex and sysobjects tables to get this info...i just don't know how.

 

[nigelrivett]

Have a look at the sp_spaceused SP to see how that does it.
Might be easier to call the SP to do it.

======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

 

[dbomrrsm]

Try this - create the SP in master database then run it against the databse you are interested in.

Hope it helps.

CREATE PROCEDURE dbo.sp_tablesize
@opcion AS Varchar(5)= false
AS
DECLARE @obj_id AS int
--declare @opcion as Varchar(5)--= false
--
DECLARE @strTabName SYSNAME
DECLARE @lngTabCount INTEGER
DECLARE @lngLoopCount INTEGER
IF @opcion IS NOT NULL


begin
SELECT @opcion = lower(@opcion)
IF @opcion NOT IN ('true','false')


begin
raiserror(15143,-1,-1,@opcion)
return(1)
END
end
IF (@opcion = 'true')
GOTO BaseDeDatos
IF (@opcion = 'false')
GOTO Tablas
BaseDeDatos:
EXEC sp_spaceused @updateusage = 'TRUE'
RETURN
Tablas:
SET nocount ON
CREATE TABLE #t
(
numID INTEGER IDENTITY(1,1)
,table_name SYSNAME
,[rows] varchar(25)
,reserved varchar(25)
,data varchar(25)
,index_size varchar(25)
,unused varchar(25)
)
CREATE TABLE #tTables
(
numID INTEGER IDENTITY(1,1)
,strTableName SYSNAME
)
INSERT INTO #tTables (strTableName)
SELECT name FROM dbo.sysobjects WHERE xtype = 'u' ORDER BY name DESC
SET @lngTabCount = @@ROWCOUNT
SET @lngLoopCount = @lngTabCount
WHILE @lngLoopCount <> 0


BEGIN
SET @strTabName = '[dbo].' + (SELECT strTableName FROM #tTables WHERE numID = @lngLoopCount)
--set @rows = (select count(*) FROM #tTa
-- bles WHERE numID = @lngLoopCount)
SELECT @obj_id = object_id(@strTabName)
INSERT INTO #t (table_name,[rows],reserved,data,index_size,unused)
EXEC sp_spaceused @strTabName
SET @lngLoopCount = @lngLoopCount - 1
END
DROP TABLE #tTables
SELECT * FROM #t
DROP TABLE #t
RETURN
GO

DBomrrsm

 

[jhall01]

This is what I ended up doing. I created a table and a job to execute daily. The job runs the following T-SQL Script:

[blue]
INSERT INTO ref_table_tracking
SELECT table_name, row_count, table_size, convert(char(10),getdate(),101) load_date
FROM
(
SELECT
a.table_name,
SUM(a.row_count) row_count,
SUM(a.table_size) table_size
FROM
(
SELECT
OBJECT_NAME(i.id) table_name,
i.Rows row_count,
CONVERT(numeric(15,2),((i.reserved*(SELECT low FROM master.dbo.spt_values WHERE number = 1 AND type = 'E'))/1024.)/1024.) table_size
FROM
sysindexes i
JOIN
sysobjects o
ON
i.id = o.id
WHERE
o.type in ('U') AND
indid in (0, 1, 255)
) a
GROUP BY table_name
) A
WHERE table_size > 2
[/blue]

This seems to work pretty well...but I am curious what the "low" column is in the spt_values table. Can anyone tell me what the "low" column is?

Thanks,

Jon

 

[vongrunt]

Looks like SQL Server page size (8kB).

 

[vikaskalra]

But there is a problem in all the above mentioned cases, as they depend upon using Sysindexes table, which is at times out of sync. In my case where one of my table has 600,000 records, the Sysindexes table shows one number while the Count shows another number... I even tried DBCC DBREINDEX to rebuild the index for this table.. but it still points to the wrong number of records... any clues Guys ?