Results 1 to 8 of 8
  1. #1
    Join Date
    Jul 2004
    Posts
    23

    Unanswered: DBCC SHRINKDATABASE duration

    Is there anyway to tell how long this will run for -- or how far it has got? I have a large database that has just had most of the data removed. The command has been running for 8 hours and I have just stopped it to let something else run quickly. Any way of telling how much longer it will take?

  2. #2
    Join Date
    Nov 2006
    Location
    UK
    Posts
    46
    Hi

    Try this:

    Code:
    dbcc shrinkdatabase('DatabaseName', truncateonly)
    This command just truncates unused space and do not move any data.
    It should take few minutes on 100 GB database.
    If you need to run full shrinkdatabase, maybe it is a good idea to run it over the weekend.

    Regards
    Kris Zywczyk
    Last edited by Kris Zywczyk; 11-08-06 at 05:41.

  3. #3
    Join Date
    Jul 2004
    Posts
    23
    Thanks, that gave me a few hundred gigs of free space to play with. Does it gradually move space from the end of the db to the beginning to compact it? What I mean is -- to be able to chop (Say) 100GB from the end of file it needs to be free space and randomly doing a defrag would not do that.

  4. #4
    Join Date
    Nov 2006
    Location
    UK
    Posts
    46
    DBCC SHRINKDATABASE
    ( 'database_name' | database_id | 0
    [ ,target_percent ]
    [ , { NOTRUNCATE | TRUNCATEONLY } ]
    )
    [ WITH NO_INFOMSGS ]


    Arguments
    'database_name' | database_id | 0
    Is the name or ID of the database to be shrunk. If 0 is specified, the current database is used. Database names must comply with the rules for identifiers.

    target_percent
    Is the percentage of free space that you want left in the database file after the database has been shrunk.

    NOTRUNCATE
    Causes the freed file space to be retained in the database files. If not specified, the freed file space is released to the operating system.

    TRUNCATEONLY
    Causes any unused space in the data files to be released to the operating system and shrinks the file to the last allocated extent, reducing the file size without moving any data. No attempt is made to relocate rows to unallocated pages. target_percent is ignored when TRUNCATEONLY is used.

    WITH NO_INFOMSGS
    Suppresses all informational messages that have severity levels from 0 through 10.


    Regards
    Kris Zywczyk

  5. #5
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    Quote Originally Posted by Kris Zywczyk
    This command just truncates unused space and do not move any data.

    Regards
    Kris Zywczyk
    DBCC Shrinkdatabase will indeed reorganize datapages on a disk and will indeed cause fragmentation.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  6. #6
    Join Date
    Nov 2006
    Location
    UK
    Posts
    46
    Quote Originally Posted by Thrasymachus
    DBCC Shrinkdatabase will indeed reorganize datapages on a disk and will indeed cause fragmentation.

    TRUNCATEONLY
    Causes any unused space in the data files to be released to the operating system and shrinks the file to the last allocated extent, reducing the file size without moving any data. No attempt is made to relocate rows to unallocated pages. target_percent is ignored when TRUNCATEONLY is used.


    Regards
    Kris Zywczyk
    Regards
    Kris Zywczyk

  7. #7
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    if you use that arguement sure, but then you are not regaining all of your unused space caused by delete operations. you are only getting back the pages at the end of the files that have never been written too. our friend said he removed a bunch of data, so I am guess the space at the end of the file that has not been written to is not were stands to gain ... oh nevermind
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  8. #8
    Join Date
    Jul 2004
    Posts
    23
    The truncateonly helped. I suspect the shrink preferentially takes pages from the end of the file and moves them towards the front. This means that the truncateonly does recover some space. If it did not preferentially do that then by chance space could only be recovered at the end (as statistically there would always be a block fairly near the end).

Posting Permissions

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