Excessive reserved space on SQL Server

sql sql-server msde
Paul G picture Paul G · Sep 2, 2009 · Viewed 12.4k times · Source

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

Russell Steen picture Russell Steen · Sep 2, 2009

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

Related questions

How do I UPDATE from a SELECT in SQL Server?

In SQL Server, it is possible to insert rows into a table with an INSERT.. SELECT statement: INSERT INTO Table (col1, col2, col3) SELECT col1, col2, col3 FROM other_table WHERE sql = 'cool' Is it also possible to update a …

Read More
How do I perform an IF...THEN in an SQL SELECT?

How do I perform an IF...THEN in an SQL SELECT statement? For example: SELECT IF(Obsolete = 'N' OR InStock = 'Y' ? 1 : 0) AS Saleable, * FROM Product

Read More
OR is not supported with CASE Statement in SQL Server

The OR operator in the WHEN clause of a CASE statement is not supported. How can I do this? CASE ebv.db_no WHEN 22978 OR 23218 OR 23219 THEN 'WECS 9500' ELSE 'WECS 9520' END as wecs_system

Read More