Would check database and LOG file sizes periodically and use the following proc to learn what individual tables are doing i.e, what size each table is using within the data base. A point of caution it is possible that depending upon additions / updates / deletions made to data to have the log file grow faster than the database so if it is disc space you are worried about - check both
Also Books on Line has some great discussion of database and log file size prediction. What I get from BOL is predicting size of a non existent database and associated log file is not much more than a guessing game
To track table size and space each table uses within the database try this proceedure
(Disclaimer - I owe this procedure to another DBA whose name I have forgotten, and who I probably forgot to thank, for sharing his knowledge with us here on the forums, but I use it frequently)
CREATE procedure space_used
@TablePattern varchar(128) = '%',
@updateusage varchar(5) = false
as
declare @id int
declare @type character(2)
declare @pages int
declare @dbname sysname
declare @dbsize dec(15,0)
declare @bytesperpage dec(15,0)
declare @pagesperMB dec(15,0)
declare @objname nvarchar(776)
create table #spt_space ( id int null,
rows int null, reserved dec(15) null,
data dec(15) null, indexp dec(15) null,
unused dec(15) null )
if @updateusage is not null
begin
select @updateusage=lower(@updateusage)
if @updateusage not in ('true','false')
begin raiserror(15143,-1,-1,@updateusage)
return(1)
End
End
select @dbname = db_name()
declare cur cursor for select TABLE_NAME = convert(sysname,o.name) from sysobjects o where o.type in ('U')
and convert(sysname,o.name) like @TablePattern
order by 1
OPEN cur
FETCH NEXT FROM cur INTO @objname
WHILE @@FETCH_STATUS = 0
begin
select @id = null
select @id = id, @type = xtype
From sysobjects where id = object_id(@objname)
if @id is null
begin
raiserror(15009,-1,-1,@objname,@dbname)
return (1)
End
if @updateusage = 'true'
dbcc updateusage(0,@objname) with no_infomsgs
set nocount on
insert into #spt_space (id, reserved)
select @id, sum(reserved) From sysindexes where indid in (0, 1, 255) and id = @id
select @pages = sum(dpages) From sysindexes Where indid < 2 and id = @id
select @pages = @pages + isnull(sum(used), 0) From sysindexes Where indid = 255 and id = @id
update #spt_space
set data = @pages where id = @id
update #spt_space
set indexp = (select sum(used) From sysindexes where indid in (0, 1, 255) and id = @id) - data
where id = @id
update #spt_space
Set unused = reserved
- (select sum(used) From sysindexes where indid in (0, 1, 255) and id = @id)
where id = @id
update #spt_space
Set Rows = I.Rows from #spt_space s join sysindexes i on i.id = s.id Where I.indid < 2 and i.id = @id
FETCH NEXT FROM cur INTO @objname
End
Close cur
DEALLOCATE cur
select name = substring(object_name(id), 1, 30), rows = convert(char(11), rows), reserved = ltrim(str(reserved * d.low / 1024.,15,0) + ' ' + 'KB'),
data = ltrim(str(data * d.low / 1024.,15,0) + ' ' + 'KB'),
index_size = ltrim(str(indexp * d.low / 1024.,15,0) + ' ' + 'KB'),
unused = ltrim(str(unused * d.low / 1024.,15,0) + ' ' + 'KB')
from #spt_space s, master.dbo.spt_values d Where d.Number = 1 and d.type = 'E'
order by s.reserved * d.low desc
select name = cast('TOTAL' as char(30)),
rows = convert(char(11), SUM(rows)),
reserved = ltrim(str(SUM(reserved * d.low) / 1024.,15,0) + ' ' + 'KB'),
data = ltrim(str(SUM(data * d.low) / 1024.,15,0) + ' ' + 'KB'),
index_size = ltrim(str(SUM(indexp * d.low) / 1024.,15,0) + ' ' + 'KB'),
unused = ltrim(str(SUM(unused * d.low) / 1024.,15,0) + ' ' + 'KB')
from #spt_space s, master.dbo.spt_values d Where d.Number = 1
and d.type = 'E'
GO