April 1, 2009 at 3:32 am
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.
May 1, 2009 at 9:06 am
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.
June 12, 2009 at 1:01 pm
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.
June 12, 2009 at 1:06 pm
You should be able to select the specific shrink process in the Activity Monitor in SQL Management Studio and choose "Kill Process".
June 13, 2009 at 1:30 am
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
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy