• I've run into such stubborn files in the past. Normally I "give in" to the following...

    1. Run DBCC CheckDB to make sure there's no corruption.

    2. Do a FULL backup.

    3. Suspend any jobs that do log file backups on the DB. --This is where potential for possible dataloss begins.

    4. Set the database the database to SIMPLE recovery model.

    5. Run a script to set the database to SINGLE user with "ROLLBACK IMMEDIATE", set it right back to "MULTI-USER", and start the shrink on given file with a target size of "0". It, of course, won't do "0" but it seems that a setting of "1" or some other number doesn't always work (I'm thinking "bug" but won't submit it as a CONNECT item because MS probably won't be able to duplicate the problem). If you have a log file that was poorly constructed and has too many VLFs, now would be a good time to shrink that t0 zero and then regrow it properly.

    6. Once the shrink completes, change the DB back to FULL recovery.

    7. Do a DIF backup to reestablish the log chain. (Or a full backup if you prefer and have the room).

    8. Turn the jobs that do log file backups back on.

    9. REORGANIZE all the indexes. It's going to be slow but it won't cause but a bit of growth on the database for sorting. IF you have the Enterprise Edition, you can specify "SORT_IN_TEMPDB = ON" and that'll help even more.

    10. Use REBUILD to rebuild only those indexes where a properly organized B-TREE would really help. This will cause some file growth depending on the size of the largest index you rebuild (usually, a clustered index).

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)