Why Does SHRINKFILE Page Latch on a page in different File?

Question

Post reply

Why Does SHRINKFILE Page Latch on a page in different File?

jheim

Say Hey Kid

Points: 712
More actions
September 28, 2015 at 11:24 am

#305102

I am running a DBCC SHRINKFILE on "FILE1" of a database (it has fileid = 1)...intent is to remove 70GB of file space:
DBCC SHRINKFILE (N'FILE1' , 400000).
For the SPID that's doing the shrink, In activity monitor you can see:
Waittype: PAGEIOLATCH_EX on resource: 9:3:15411328
(the DB is dbid=9)
But why does it need a page from fileid=3? Are there page dependencies between files that prevent moving a page within a given file? Does it need that fileid3 page to come along?
Its just sitting there in the SUSPENDED state for the last hour....I am going to leave it another 5 hours or so before cancelling.
the dm_exec_requests has an estimated percent complete at 83% and holding....not sure if I can believe that.
NOTE: I know about shrinking in increments to get something done....don't need that advice.
I know shrinking is not a good thing to do as a rule due to fragmentation....don't need that advice either.
I would like feedback on this particular issue....PAGEIOLATCH on a page in another file during a SHRINKFILE of a different file.
Thanks

Viewing 7 posts - 1 through 6 (of 6 total)

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

jheim Say Hey Kid Points: 712 More actions · Answer 1

Also when I do:

DBCC PAGE(9,3,15411328,1)

no results (tried levels 0,1,2,3 in 4th parm...nothing)

Am I misunderstanding something here? Isn't the "3" in the Activity monitor Wait Resource the fileid?

Any feedback appreciated

Thanks,

jheim Say Hey Kid Points: 712 More actions · Answer 2

ok forgot to turn on the traceflag to allow print display.

did this and now I can see stuff from DBCC PAGE....but still don't understand whats goiong on.

DBCC TRACEON(3604)

DBCC PAGE(9,3,15411328,3) with tableresults

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

It's updating the page references.

If we take a page in the leaf level of an index, it'll have pointers to the next and previous pages in the index (next and previous in the index, not next and previous in the file), plus a pointer to the parent page. An intermediate page will have pointers to next, previous, parent and child. A tex page will have pointers to the tex leaf pages, etc, etc.

These page references are in the form file:page number, so if the page moves within the file, all pages referencing that page have to be updated.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

jheim Say Hey Kid Points: 712 More actions · Answer 4

OK, I've turned up something....my DB has a table with varchar(max) LOBs stored off-row. So some pages on file3 are linked to pages on file1. the process of true-up of the page structure requires going after pages linked to the page being moved.

I still don't know why its stuck on that one page.

In any case, here is a blog post (not sqlcentral...hope that's ok) that explains pretty well.

http://www.sqlskills.com/blogs/paul/why-lob-data-makes-shrink-run-slooooowly-t-sql-tuesday-006/#comment-999888

jheim Say Hey Kid Points: 712 More actions · Answer 5

MY shrink ended after 4hrs.....most of that time the dmvs indicated it was waiting on the same page....too weird.

But the take-aways I have are multiple file objects will require cross file page latching....and large LOB area off-row objects can take a long time to do structure housekeeping due to slow resolution of links back to pages that are connected to moved pages.

AND....Shrinks will actually finish. 😀

Thanks for the input Gila Gail gal....you had the key point there.

Jeff Moden SSC Guru Points: 1003857 More actions · Answer 6

jheim (9/28/2015)
MY shrink ended after 4hrs.....most of that time the dmvs indicated it was waiting on the same page....too weird.
But the take-aways I have are multiple file objects will require cross file page latching....and large LOB area off-row objects can take a long time to do structure housekeeping due to slow resolution of links back to pages that are connected to moved pages.
AND....Shrinks will actually finish. 😀
Thanks for the input Gila Gail gal....you had the key point there.

Now all you need to do is rebuild your indexes to remove the 99+% fragmentation of the shrink. 😛

--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)