Space user on SQL Server 7

[wassup]

SQL server 7 with 20 MB of fixed space.

Why does it allocate so much reserved space?

Table = FORUM_REPLY
Reserved = 10512 KB
Data = 8192 KB

Does this mean I have 10512KB left to use in the table before more space is required?

I used DBCC SHRINKDATABASE (database name) to try and reduce the reserved space but this has no effect.

 

[moonshadow]

Two things. Firstly, DBCC SHRINKDATABASE doesn't shrink a database smaller than it was created (but see below), secondly your reserved space will include the indices. Try running sp_spaceused (SQL 7 and above), and see what that returns. Additionally, SQL 7 stores rows in 8K pages, and may not be able to fill each page completely (it doesn't wrap rows across pages), also it may be allowing some space in case the indices increase in size (read up about FillFactor in BOL).
If you want to shrink a database as small as you can, I've written this stored procedure which can be created in the Master database, and called from any database you want to shrink. If you don't mind it truncating the transaction log, you can pass it a parameter of 1, otherwise shrinking of the transaction log will occur when it's backed up (unless you have truncate on checkpoint set, when it will happen then). It works quite well - managed to free up 12GB on one of our servers this morning.

CREATE PROCEDURE sp_shrinkdb (@TruncateTransactions bit = 0)

AS
declare @groupid int
declare @name varchar(255)
declare @dbname varchar(255)
declare @sql varchar(500)
set @dbname=db_name()
select 'Shrinking database ' + @dbname

Create table #tempsysfiles (groupid int, name varchar(255))

set @sql = 'insert into #tempsysfiles select groupid, name from ' + @dbname + '..sysfiles'

exec(@sql)

declare curFiles cursor for
select groupid, name from #tempsysfiles

open curFiles

fetch curFiles into @groupid,@name

while @@fetch_status=0
begin
set @name=rtrim(@name)
dbcc shrinkfile(@name,1)
fetch curFiles into @groupid,@name
end
close curFiles
deallocate curFiles
drop table #tempsysfiles
if @TruncateTransactions=1
begin
print 'Truncating Transaction Log'
BACKUP LOG @dbname WITH TRUNCATE_ONLY
END