Shrink Datafile task in SQL2005 - showing as suspended in current activity

  • Last night I started a task to shrink the main datafile of one of our databases, using the GUI with the option of reorganise pages before space is released selected, the task has been running for about 16 hours and if you look in current activity the status of the process (DBCCFilesCompact) is showing as supsended with wait type of either pageiolatch_ex or pageiolatch_sh - Am I right that these are locks on the disk pages that SQL server needs for the task?

    The transaction log has grown from about 10gig to 80+gig in this time despite being regularly backed up (hourly)

    If I try and close enterprise manager, it says that this will cancel the task - would there be any impact in doing that? I know my file won't be shrank but will it damage the database?

    Alternatively, is there a way I can find out what is causing the locks and cancel that process instead?

    Any help would be much appreciated!

    EDITED TO ADD

    The task finished on its own, it was a big database by our organisations's standards (160Gig) and took longer than we were expecting. panic over.

  • Glad to see you waited it out and it finished. For future reference, it is absolutely safe to cancel a shrink operation before it's finished.

  • hi

    how do you cancel the shrinking process ? The cancel button is disabled. The only way I have found to do it is to close SQL SERVER Management Studio altogether.

  • You should be able to select the specific shrink process in the Activity Monitor in SQL Management Studio and choose "Kill Process".

  • ok.Thanks for replying. But is that safe?Is there not a risk of corrupting the database ? The database is currently about 7 Gigabytes

    Also, when I run "database shrinking", either from GUI or SQL, it invariably gets into suspended mode after some time. I check its status by running the following command :

    SELECT * FROM sys.dm_exec_requests.

    How can I determine why it has been suspended ? How can I let it run without being suspended ?

    The application talking to the database is unable to use it during the shrinking process. Is that normal ? I have to cancel the shrinking process.

    Thanks for your precious help.

Viewing 5 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply