DBCC ShrinkDatabase(ABC, 10) ..... finished with no actual response....

I have a DB ABC, 160 gb.
I dropped 10 big tables. from SQL2000 view taskpad, can see that the <used> is 88 GB, <free> is 72 gb.

History, the dbcc shrinkdatabase in this ABC db had run twice in the past month, to bring the size from 230 gb to current 160 gb with running time around 120 hours each.

After running DBCC ShrinkDatabase(ABC, 10) for 220 hours since last week, I cancelled the process and reboot the server.

Now, rerun DBCC ShrinkDatabase(ABC, 10) with only 1 minute
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

DBCC CheckDB('ABC') --ok
CHECKDB found 0 allocation errors and 0 consistency errors in database 'ABC'.

dbcc updateusage(ABC) --ok

just try to release the free space to OS
dbcc shrinkdatabase(ABC, truncateonly) --ok

but sp_helpdb ABC, still data portion is 160 gb, log just .5 gb.

thanks for your help!
David

Try 'dbcc shrinkfile'.

use ABC
dbcc shrinkfile (ABC_Data, truncateonly)

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

only 1 minute with the same 160 gb data size.

thanks
David

Don't use truncateonly, specify target size instead.

Hi Ramio:

I have tried

DBCC shrinkfile(abc_data, 100000)

DBCC execution completed. If DBCC printed error messages, contact your system administrator. --57 seconds.

but the size is still 160 gb.

The last method now might be the transfer all the objects to another ABC_New, drop the current ABC and rename the ABC_NEW back to ABC.

Or another way is to backup the ABC and restore to see if the new ABC_NEW is 100 gb? but there are so many objects from dts, trigger, jobs, 24 hours running issues involved.

Have I tried all the normal methods to shrink the file?

thanks
David

You may need to move pages to begining of the file, try shrink the file in em since it has that option.

Hi Ramio:

EMPTYFILE

Migrates all data from the specified file to other files in the same filegroup. Microsoft® SQL Server™ no longer allows data to be placed on the file used with the EMPTYFILE option. This option allows the file to be dropped using the ALTER DATABASE statement.
---------------------------------------------------
Not quite sure about the EM option, does it looks like
DBCC shrinkfile(abc_data, EMPTYFILE) ?

Do I need to create another DataFile to load it? One of the problems is there are only 38 gb free space.

thanks
David

No, they are different. Moving page option moves pages in same file to front, kind of compact. That leaves free sapce in rear of the file for shrinking.

Hi Ramio:

DBCC SHRINKFILE
( { file_name | file_id }
{ [ , target_size ]
| [ , { EMPTYFILE | NOTRUNCATE | TRUNCATEONLY } ] })

don't see the option you mentioned here...
Could you give more detail about EM option on moving pages?
thanks
-D

It moves all pages in the file to front of the file. We use to do it with script in sql7 by creating a table, adding then dropping rows to that table. Sql2k em made it easier for you.

Hi Ramio:

1. without doing anything, this Monday morning, the ABC Data size is reduced from 160 gb to 110 gb.

thus, I have retried it since move objects and re-set secuiryt are very time consumptions and now I am dealing with 110 gb.

First it failed with DBCC shrinkdatabase(ABC, 10)

Server: Msg 1205, Level 13, State 57, Line 1
Transaction (Process ID 58) was deadlocked on lock resources with another process and
has been chosen as the deadlock victim. Rerun the transaction.

--Does it mean, the on-line shrinking is not reliable? it caused deadlock?

--I retried the dbcc shrinkfile
use ABC
go
dbcc shrinkfile (ABC_Data, 10000)

just 1 minute, it is done, don't believe.
The data size is now 10 gb, and free space is now 180 gb.
It works but ended with a little strange feelings....

thanks
David

May cause deadlock since it locks table when move pages around.

does it mean both shrinkdatabase and shrinkfile will cause deadlock ?

Possible, what shrinkdatabase does is shrinking all db files in the db.

But dbcc shrinkdatabase or dbcc shrinkfile do not require single user mode?

and above process are doing some disk defragmentation without change data....

Sql can't move pages without locking them.