Page 1 of 2 12 LastLast
Results 1 to 15 of 16

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

  1. #1
    Join Date
    Mar 2003
    Posts
    383

    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

  2. #2
    Join Date
    Sep 2002
    Posts
    5,938
    Try 'dbcc shrinkfile'.

  3. #3
    Join Date
    Mar 2003
    Posts
    383
    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

  4. #4
    Join Date
    Sep 2002
    Posts
    5,938
    Don't use truncateonly, specify target size instead.

  5. #5
    Join Date
    Mar 2003
    Posts
    383
    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

  6. #6
    Join Date
    Sep 2002
    Posts
    5,938
    You may need to move pages to begining of the file, try shrink the file in em since it has that option.

  7. #7
    Join Date
    Mar 2003
    Posts
    383
    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

  8. #8
    Join Date
    Sep 2002
    Posts
    5,938
    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.

  9. #9
    Join Date
    Mar 2003
    Posts
    383
    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

  10. #10
    Join Date
    Sep 2002
    Posts
    5,938
    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.

  11. #11
    Join Date
    Mar 2003
    Posts
    383
    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

  12. #12
    Join Date
    Sep 2002
    Posts
    5,938
    May cause deadlock since it locks table when move pages around.

  13. #13
    Join Date
    Mar 2003
    Posts
    383
    does it mean both shrinkdatabase and shrinkfile will cause deadlock ?

  14. #14
    Join Date
    Sep 2002
    Posts
    5,938
    Possible, what shrinkdatabase does is shrinking all db files in the db.

  15. #15
    Join Date
    Mar 2003
    Posts
    383
    But dbcc shrinkdatabase or dbcc shrinkfile do not require single user mode?

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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •