DBCC Shrinkdatabase --run 36 hours....

[DLu]

Hi:

database AB with 195 gb on a SQL2000 sp3 server.
I have dropped 10 tables in the DB A, each about 2 gb.

DBCC Shrinkdatabase(AB, 10) has run 36 hours and still running. It looks it using many CPU and I/O but with 0 in memory.

I have done dbcc checkdb with 0 error.
I have done sp_statistics
both above return results had excluded dropped tables.

Related Jobs and DTS run this DB are as normal.
Before this, I have tried DBCC shinkfile(A_dataFile, 10) and I had cancelled it after run 24 hours.

The server free space is around 36 gb now.
Any idea?
thanks
David

[rmiao]

It is very slow process.

[DLu]

I know it is a slow process.
With a 50 gb size db, shrinkdatabase usually around 2-3 hours.

It has been run 48 hours already.
I was heard other way is to do the HD defragmentation. but it is a production one, could not be off for more than 2 hours the most. Another one is via copy the data file to somewhere and copy back. Not quite sure about this one.

I know it is not proportional between the size and the time required in shrinkdatabase, ...... Is there some way to speed up the process??

thanks
-D

[rmiao]

Depends on disk speed, data movement and amount of space to reclaim. Took me around 20 hours to reclaim 6gb space from 40gb db, and the server is powerful one.

[DLu]

Hi Rimao:

Thanks for your message, it makes me feel 'better'.
I will be more patient for the 50 hours running now....

I hope the next version or service pack from MS SQL could address this issue.

-D

[DLu]

patience pays out.
the 199 gb db finally 'shrinked' to 180 gb, after 70 hours.

Hope: MS SQL need to improve this process....

[rmiao]

It's bit better in sp4 and sql2k5.

[DLu]

Hi Ramio:

Again, thanks for your tip.
This also gives me some impetus to beg mgr to move from 2k to 2005 !