By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
429,214 Members | 2,066 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 429,214 IT Pros & Developers. It's quick & easy.

Shrinking database files

P: n/a
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 want to shrink the
file to lean 30-40Gb.

Ive been trying this:

dbcc shrinkfile('LEAR_Index',40000)

But to no apparent avail; the file did not shrink.
Ive 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?

Jul 23 '05 #1
Share this Question
Share on Google+
6 Replies


P: n/a
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

Jul 23 '05 #2

P: n/a
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 want to shrink the
file to lean 30-40Gb.

Ive been trying this:

dbcc shrinkfile('LEAR_Index',40000)

But to no apparent avail; the file did not shrink.
Ive 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
Jul 23 '05 #3

P: n/a
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
Jul 23 '05 #4

P: n/a

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 want to shrink the file to lean 30-40Gb.

Ive been trying this:

dbcc shrinkfile('LEAR_Index',40000)

But to no apparent avail; the file did not shrink.
Ive 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.

Jul 23 '05 #5

P: n/a
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 want to shrink the
file to lean 30-40Gb.

Ive been trying this:

dbcc shrinkfile('LEAR_Index',40000)

But to no apparent avail; the file did not shrink.
Ive 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

Jul 23 '05 #6

P: n/a
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
Jul 23 '05 #7

This discussion thread is closed

Replies have been disabled for this discussion.