Posted by Erland Sommarskog on 12/24/07 23:21

Neil (nospam@nospam.net) writes:
> It shows about half a gig of unused space. Here's the printout:
>
> database_size unallocated space
> --------------------------------------
> 3355.75 MB -2571.25 MB
>
> reserved data index_size unused
> ------------------ ------------------ ------------------ -----------------
> 6069248 KB 2477728 KB 3066760 KB 524760 KB

The negative number for unallocated space is spooky. Run it again,
but now like this:

sp_spaceused NULL, true

That will make sure the values in sysindexes are updated.

By the way, are you still on SQL 7? I seem to recall that you talked
up moving on to SQL 2005, but did that materialise?

> The maintenance job that is run nightly performs the following:
>
> Optimizations tab:
>
> Reorganize data and index pages
> (change free space per page percentage to 10%)

That's OK.

> Remove unused space from database files
> (shrink database when it grows beyond 50 MB)
> (amount of free space to remain after shrink: 10% of the data space)

But remove this one. Shrinking the database is not a good thing to do
on regular terms.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Back to original message]