help for shrinking database

[Zvonko]

Hi guys.

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(data base_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?

Thanks in advance

Zvonko

[Jack Vamvas]

Re: help for shrinking database

what about initially shrinking the log , and then the data file?

--
----
Jack Vamvas
_______________ _______________ _____
Receive free SQL tips - www.ciquery.com/sqlserver.htm
_______________ _______________ _____


"Zvonko" <zvonko_NOSPAM_ @velkat.net> wrote in message
news:449799a2@n s1.novi-net.net...[color=blue]
> Hi guys.
>
> 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(data base_name, 50) or whatever value[/color]
of[color=blue]
> 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[/color]
big?[color=blue]
>
> Thanks in advance
>
> Zvonko
>
>[/color]

[Erland Sommarskog]

Re: help for shrinking database

Zvonko (zvonko_NOSPAM_ @velkat.net) writes:[color=blue]
> 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(data base_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?[/color]

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@sommarsk og.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

[Cimode]

Re: help for shrinking database

Do not forget to do some backup B4 the operation...
Jack Vamvas wrote:[color=blue]
> what about initially shrinking the log , and then the data file?
>
> --
> ----
> Jack Vamvas
> _______________ _______________ _____
> Receive free SQL tips - www.ciquery.com/sqlserver.htm
> _______________ _______________ _____
>
>
> "Zvonko" <zvonko_NOSPAM_ @velkat.net> wrote in message
> news:449799a2@n s1.novi-net.net...[color=green]
> > Hi guys.
> >
> > 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(data base_name, 50) or whatever value[/color]
> of[color=green]
> > 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[/color]
> big?[color=green]
> >
> > Thanks in advance
> >
> > Zvonko
> >
> >[/color][/color]