Please Help!!! Running DBCC SHRINKFILE. It took long long time.

Question

Please Help!!! Running DBCC SHRINKFILE. It took long long time.

RUIJIN

SSC Rookie

Points: 37
More actions
August 4, 2004 at 2:18 pm

#83462

Hi,
I have a 52GB database. I dropped one of the tables that contain 200 million records. The unused space is 38GB. I would like to release that space to OP system. The statement shows below:
USE MyDatabase
GO
DBCC SHRINKFILE (MyDatabase_data)
GO
It was running more than 24 hours and still running.
My system configuration: CPU 3GB
                                  RAM 512MB
        Thanks!!!
                    Helen

Viewing 13 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply

colin.Leversuch-Roberts SSC Guru Points: 52643 More actions · Answer 1

I've found that shrinkfile can sometimes "hang", if I can I switch the database model to simple before shrinking the file(s) I realise that sometimes this isn't possible but it works !

You could also try issuing a checkpoint before the shrink.

[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/

bbdpres SSCommitted Points: 1876 More actions · Answer 2

You can also try shrinking the file(s) in chunks - maybe 1 or 2 GB at a time...

RUIJIN SSC Rookie Points: 37 More actions · Answer 3

Hi,

Thank you for help. Could you tell me more specific and detail.

Thanks!!!

bbdpres SSCommitted Points: 1876 More actions · Answer 4

USE MyDatabase

GO

DBCC SHRINKFILE (MyDatabase_data, 50000)

GO

DBCC SHRINKFILE (MyDatabase_data, 48000)

GO

DBCC SHRINKFILE (MyDatabase_data, 46000)

GO

.

This method always seems to be faster for me than one big chunk...

RUIJIN SSC Rookie Points: 37 More actions · Answer 5

RUIJIN

SSC Rookie

Points: 37

August 5, 2004 at 9:20 am

#517861

Thank you very much. I will try that.

JStiney Say Hey Kid Points: 671 More actions · Answer 6

If you can create a test database, you can try your shrinks and cancel them if they take more than an hour or two.

Try a truncate only first, and see what it gets you.

It is very fast, because it only releases space that is empty.

Don't try to get all the space. Use a size variable that gets you most of the space. It may take a lot more time to get the last couple of Gigs.

USE

MyDatabase

GO

DBCC

SHRINKFILE (MyDatabase_Data, 20000, TRUNCATEONLY)

GO

RUIJIN SSC Rookie Points: 37 More actions · Answer 7

Thanks!!! I will try this first.

colin.Leversuch-Roberts SSC Guru Points: 52643 More actions · Answer 8

I would expect a log shrink to be almost instantaneous, even if it's very large.

try using dbcc sqlperf(logspace) to check how much free space you have to shrink, if your log is actually full then shrinking may prove tricky. Assuming full recovery model you should back up the transaction log before attempting a shrink. Open transactions can also cause problems use dbcc opentran(databasename) to check you have no open transactions.

[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/

RUIJIN SSC Rookie Points: 37 More actions · Answer 9

Thank you very much. I will tell you the results.

RUIJIN SSC Rookie Points: 37 More actions · Answer 10

Hi,

This is a swing server, I did shrink this database from Enterprise manager. That did not release much space for me. But it shrink the log file. Therefore, I had a samall log file for this database.

Here is the resulet:

Log Size : 1.2421875 MB

Log Space Used (%): 40.290882

No active open transactions

Any good idea?

Thanks!!!

RUIJIN SSC Rookie Points: 37 More actions · Answer 11

Hi,

I did try

USE MyDatabase

GO

DBCC

SHRINKFILE (MyDatabase_Data, 20000, TRUNCATEONLY)

GO

It did not release much space for me.

Any other suggestions?

Thanks!

JStiney Say Hey Kid Points: 671 More actions · Answer 12

Increase your log to a couple gigs.

Change your recovery mode to simple.

Shrink the file in chunks, as shown before.