Excessive reserved space on SQL Server

Question

We have a database running under MSDE (SQL 2000, service pack 4) that is reserving massive amounts of excess space. Using sp_spaceused for each table gives a total reserved size of 2102560 KB, a data size of 364456 KB and an unused size of 1690760 KB (i.e. reserving nearly 4 times the used space). The worst culprits are tables that are frequently written to but never deleted from (transaction logging). Generally, deletes are very infrequent and very small in terms of size and number of records.

The database files on disk are at the 2 gb limit and this is causing problems with backups etc.

I have tried DBCC SHRINKDATABASE, DBCC SHRINKFILE and DBCC REINDEX with no effect on the file size used on the disk

2 questions - How can I shrink the database file size and how can I stop SQL Server from reserving the excess space ?

Thanks

Paul

Answer 1

what you could do is take a full db backup, reindex the db, incrementaly shrink it, then reindex it again. that way you'll have the db in it's current size.

also you should move your logging tables to another table.

Answer 2

USE < DBNAME >
GO
BACKUP LOG < DBNAME > WITH TRUNCATE_ONLY
GO
DBCC SHRINKDATABASE ( < DatabaseName > )
GO
DBCC SHRINKFILE (< logfile >, 5)
GO
DBCC SHRINKFILE (< datafile >, 5)
GO

if you don't know the file paths exec sp_helpfile

Answer 3

Create separate files for worst culprits and place them in separate filegroups. Moving tables to another file itself will compress them. Will also make shrinkfile more effective. If needed you can create more then one file per table.

Answer 4

Thanks for all the suggestions. In the end, I had to create a new empty database, copy the data from the massive database and then rename the databases.

I will be keeping an eye out on the reserved sizes. Hopefully, there was something wrong with the database setup that caused this. None of our other customers using identical software / MSDE are having this problem.