May 4, 2004 at 3:20 am
Hi there,
I am experiencing a problem which is new to me.
I'm trying to shrink a database with DBCC. It takes ages for this process to execute, after a few hours I kill it, there is no noticable progress. When I look in EM under current activities I see under WAITTYPE the type PAGIOLATCH_SH. When googling the I-net I find several hits on this type of lock, and I can figure what t is. Why this is happening at a DBCC SHRINK I cannot deduct. I've tried to shrink the database and the seperate files, it makes no difference. The waittype goes from PAGIOLATCH_SH to WRITELOG and back, and I cannot find a description for WRITELOG.
I have tried several of the suggestions found via Google, no luck.
I'm shrinking the db because of the gigantic grow of both datafile and logfile after a delete operation on the main table, consisting initially of about 60 million records, brought back to 28 million. This table has no indices or primkey.
I'm wondering if anybody can shed light on this? Any suggestion on how to solve this?
Greetz,
Hans Brouwer
May 4, 2004 at 4:29 pm
I have seen my share of PAGIOLATCH_SH. Without getting into the details, this in essence is an indication of wait for disk resources to complete certain data transfer to buffers in memory. This is inevitable in large databases when running large disk intensive operations (indexing for one), since even the well designed disk systems do not usually keep up with the demands once in a while. As long as this is once in a while (like while indexing) it is probably OK. The writelog is wait for the transaction log entries to be written to the disk. And this usually happens during those "once in a while" transactions.
Now in your particular case, here is my hypothesis. When you have a data file of starting size, say, 10 GB. And let us say over a period it has grown by 10 GB more to 20 GB. Now let's say you delete 5 GB out of it. In usual archiving process most of this 5 GB usually comes from the beginning of the file. Now you have a 20 GB file, with 15 GB data and most of the data towards the end of the file. When you try to shrink the file to 15 GB, it has to take the 5 GB data at the end of the file and copy it to the beginning of the file. Depending on the load on the system, this can take a long time.. The key to remember here is that file can be shrunk only from the end. If there is data there, it has to be copied some where. And all of this has to be logged. So I suggest you do this after hours when the system is not busy.
BTW, for what ever reason, it takes much much longer when you try to do these operations on tables with no clustered index. I have no comprehensive explanation why, but have faced this several times myself. If it is not too much hassle, put a clustered index and then try to shrink it, then drop the index. (though I might recommend keeping the clustered index, since there are several bizarre things happening with tables without clustered indexes)..
If these do not work, create another file group, copy the table over there and shrink the original file group. Some times this is faster than the other options.
May 5, 2004 at 1:02 am
Tnx for the info. This table indeed has no clustered index/primkey. It was deemed not necessary. I'll give this a try and see what happens next time. Very informative posting, tnx again.
Greetz,
Hans Brouwer
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply