Excessive Reserved Space on SQL Server

Question

We have a database running under MSDE (SQL 2000 Service Pack 4) that reserves a huge amount of extra 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., backing up almost 4 times the used space). The worst culprits are tables that are often written but never deleted from (transaction logging). As a rule, deletion is very rare and very small in size and number of entries.

The database files on the disk are at a limit of 2 GB, which causes problems with backups, etc.

I tried DBCC SHRINKDATABASE, DBCC SHRINKFILE and DBCC REINDEX without affecting the file size used on disk

2 questions. How to reduce database file size and how to stop SQL Server from reserving excess space?

thanks

Floor

Answer 1

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 do not know the path to exec sp_helpfile files

Answer 2

, db, db, , . db .

.

Answer 3

. . shrinkfile. .

Answer 4

. , , .

. , - , . , /MSDE.