|
|
Posted by Erland Sommarskog on 06/20/06 10:32
Zvonko (zvonko_NOSPAM_@velkat.net) writes:
> I have a huge database file. When I run sp_spaceused this are the
> results:
>
> db_size 1337,31 Mb
> unallocated 14,62 Mb
> reserved 1088456 Kb
> data 258992 Kb
> index_size 6224 Kb
> unused 823240 Kb
>
> Now, when I run dbcc shrinkfile(database_name, 50) or whatever value
> of percent, it says there is not enough free space in db.
>
> Please advice how to shrink the size of that file and why is it so big?
Big? 1.3 GB is a small database by today's stanadards, at least for a
production system.
The fact that the amount of "unused" is so much larger than data, indicates
that you have one or more tables that suffers from high level of
fragmentation.
This SELECT:
SELECT object_name(id), reserved FROM sysindexes WHERE indid IN (0,1)
ORDER BY reserved DESC
gives you the tables by size. You can then use DBCC SHOWCONTIG to determine
the level of fragmentation, and DBCC DBREINDEX to defragment the tables.
However, you cannot reindex tables that does not have a clustered index
with DBCC DBREINDEX. You can however, create a clustered index on these
tables, and then drop it if you absolutely do not want it.
"reserved" = space actually allocated to tables.
"unused" = space within the reserved space that is not actually used.
DBCC SHRINKFILE operates only with unallocated space. Once you have
defragmented the tables, you may be able to shrink the file.
--
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
Navigation:
[Reply to this message]
|