Shrinking database files

[Jens]

I have a database file LEAR_Index(yes, it hold index data) from a have
have recently removed a bunch of data.
It is about 120 Gb, 100Gb of which is not used. I wan´t to shrink the
file to lean 30-40Gb.

I´ve been trying this:

dbcc shrinkfile('LEAR_Index',40000)

But to no apparent avail; the file did not shrink.
I´ve tried using enterprise manager for this but it consistenly
crashes when performing this operation.#

Any thoughs, idear as to what i might be doing wrong?

[Simon Hayes]

Is LEAR_Index also the logical name of the database's data file? If
not, then you may have mixed up the syntax for DBCC SHRINKFILE and DBCC
SHRINKDATABASE:

DBCC SHRINKDATABASE(LEAR_Index, 30)
DBCC SHRINKFILE(LEAR_Index_Data, 40000)

If that doesn't help, perhaps you can post the result set from the DBCC
execution, or whatever output you get. This KB article might also be
relevant:

http://support.microsoft.com/default...b;en-us;324432

Simon

[Erland Sommarskog]

Jens (je************@lycos-europe.com) writes:

I have a database file LEAR_Index(yes, it hold index data) from a have
have recently removed a bunch of data.
It is about 120 Gb, 100Gb of which is not used. I wan´t to shrink the
file to lean 30-40Gb.

I´ve been trying this:

dbcc shrinkfile('LEAR_Index',40000)

But to no apparent avail; the file did not shrink.
I´ve tried using enterprise manager for this but it consistenly
crashes when performing this operation.#

Any thoughs, idear as to what i might be doing wrong?

You may first have to run DBCC DBREINDEX to defragment fragmented
tables.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[Brian Cryer]

I recall shrinking a database using enterprise manager and thinking it had
locked up (i.e. crashed) but it hadn't - it came back when it had finished
shrinking.

If Enterprise Manager appears to lock up then try shrinking using it before
you go home and see if it has finished by the following morning.

Brian.

www.cryer.co.uk/brian

[Tzvika Barenholz]

Jens wrote:

I have a database file LEAR_Index(yes, it hold index data) from a have have recently removed a bunch of data.
It is about 120 Gb, 100Gb of which is not used. I wan´t to shrink the file to lean 30-40Gb.

I´ve been trying this:

dbcc shrinkfile('LEAR_Index',40000)

But to no apparent avail; the file did not shrink.
I´ve tried using enterprise manager for this but it consistenly
crashes when performing this operation.#

Any thoughs, idear as to what i might be doing wrong?

It takes many hours on even good hardware to perform this operation.
you did not mention your disk setup.
In any event, if the actual usage is only 20gb, it may be cheaper to
create another file of 30gb, move the objects to it and drop the old
file.
alternatively you should consider partitioning, which will allow you do
make this move gradually.

[Paul S Randal [MS]]

Erland - why do you think this will help? Fragmented tables don't affect
shrink in the least, and also, shrink will most likely remove all the work
done by DBREINDEX as it tends to introduce fragmentation.

Thanks

--
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine

This posting is provided "AS IS" with no warranties, and confers no rights.

"Erland Sommarskog" <es****@sommarskog.se> wrote in message
news:Xn*********************@127.0.0.1...

Jens (je************@lycos-europe.com) writes:

I have a database file LEAR_Index(yes, it hold index data) from a have
have recently removed a bunch of data.
It is about 120 Gb, 100Gb of which is not used. I wan´t to shrink the
file to lean 30-40Gb.

I´ve been trying this:

dbcc shrinkfile('LEAR_Index',40000)

But to no apparent avail; the file did not shrink.
I´ve tried using enterprise manager for this but it consistenly
crashes when performing this operation.#

Any thoughs, idear as to what i might be doing wrong?

You may first have to run DBCC DBREINDEX to defragment fragmented
tables.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[Erland Sommarskog]

Paul S Randal [MS] (pr*****@online.microsoft.com) writes:

Erland - why do you think this will help? Fragmented tables don't affect
shrink in the least, and also, shrink will most likely remove all the work
done by DBREINDEX as it tends to introduce fragmentation.

Ok, thanks for the correction, Paul!
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp