Post reply

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

  • 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

  • September 28, 2015 at 11:30 am

    #1829958

    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,

  • September 28, 2015 at 11:38 am

    #1829961

    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

  • September 28, 2015 at 12:01 pm

    #1829967

    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

  • September 28, 2015 at 12:02 pm

    #1829968

    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

  • September 28, 2015 at 1:07 pm

    #1829984

    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.

  • September 28, 2015 at 5:00 pm

    #1830029

    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)

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

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