Reduce size of database - MSSQL 2000

I have checked the size of each table using the statement:

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

Summing all the space used by both the data and the indexes totals 224MB. The allocated space for our DB is 400MB and it our SQL usage meter is showing a total of 384MB. The remaining 16MB becomes used within a few days whereas not long ago it would last at least 3 or 4 months. The usage of our site has increased quite well - but not that much. We have tried:

1. Truncating the log using a backupcommand - i.e.:

BACKUP LOG <database name> with TRUNCATE_ONLY

2. Defragging all the indexes using:

EXEC sp_MSforeachtable @command1='print '?' DBCC DBREINDEX ('?', ' ', 80)'

AND using

-----------------------------
DECLARE @TableName sysname
DECLARE @indid int
DECLARE cur_tblfetch CURSOR FOR
SELECT table_name FROM information_schema.tables WHERE table_type = 'base table'
OPEN cur_tblfetch
FETCH NEXT FROM cur_tblfetch INTO @TableName
WHILE @@FETCH_STATUS = 0
BEGIN
DECLARE cur_indfetch CURSOR FOR
SELECT indid FROM sysindexes WHERE id = OBJECT_ID (@TableName) and keycnt > 0
OPEN cur_indfetch
FETCH NEXT FROM cur_indfetch INTO @indid
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT 'Derfagmenting index_id = ' + convert(char(3), @indid) + 'of the '
+ rtrim(@TableName) + ' table'
IF @indid <> 255 DBCC INDEXDEFRAG (DB_184164_dbbtdc, @TableName, @indid)
FETCH NEXT FROM cur_indfetch INTO @indid
END
CLOSE cur_indfetch
DEALLOCATE cur_indfetch
FETCH NEXT FROM cur_tblfetch INTO @TableName
END
CLOSE cur_tblfetch
DEALLOCATE cur_tblfetch

-----------------------------

Can anyone:

1. See what is it that is causing the problem OR help me know what I can do to recognize the cause of the problem
2. Suggest some possible fixes

Thanks

 

Hi Bruce,

I was assuming that the DBCC INDEXDEFRAG statement defragged the database or is that not what you are referring to. Can you let me know a little more information - especially in regard to any keywords or TSQL statements - it would be really appreciated.

Thanks