Re: Checkpoint SPID Blocked

From: Kevin Stark (SENDkevo97NO_at_POTTEDhotMEATmailHERE.com)
Date: 12/14/04 

Date: Tue, 14 Dec 2004 09:40:45 -0500

No clear idea right now from PSS. I'm going to have to restart the service
tonight, so that will probably kill our debugging efforts. We haven't had a
backup now for two days on this particular database and I'm getting quite
nervous about it. I will take your advice and try detaching and reattaching
and then check and rebuild the indexes after the backup. Thanks.

"AnthonyThomas" <Anthony.Thomas@CommerceBank.com> wrote in message
news:eHhc9Ge4EHA.2964@TK2MSFTNGP15.phx.gbl...
> Yes, I have ran into mangled page links before, but an Index Rebuild and
> DBCC CHECKDB found and then I actually had to use the ALLOW_DATA_LOSS
> parameter, but it fixed it.
>
> Can you backup and then verify the backup? If so, get one; then, I would
> try to detach and reattach. The, rerun the checks and index rebuilds.
>
> If you have an MS PSS case open, where are they leaning towards?
>
> Sincerely,
>
>
> Anthony Thomas
>
>
> --
>
> "Kevin Stark" <SENDkevo97NO@POTTEDhotMEATmailHERE.com> wrote in message
> news:ONai5kY4EHA.4092@TK2MSFTNGP14.phx.gbl...
> My original post said that I ran that already. Nothing turned up. I'll
> repost the last paragraph since Outlook Express apparently mangled it:
>
> "We have a case open with Microsoft on this as I'm pretty sure that it's a
> bug somewhere, probably in the free page list maintenance. I'm guessing
> that
> the fileID and the pageID got concatenated somehow and the free list was
> updated to reference page 116:16 when it should have been page 1:16. DBCC
> CheckDB and DBCC Checkalloc didn't turn up any allocation errors. I ran a
> dbcc page on page 1:16 and nothing out of the ordinary was there.
> Obviously
> running a dbcc page on page 116:16 threw an error. Has anyone experienced
> this before?"
>
>
>
> "AnthonyThomas" <Anthony.Thomas@CommerceBank.com> wrote in message
> news:OgXvuKY4EHA.3092@TK2MSFTNGP10.phx.gbl...
>> You really, really need to run DBCC CHECKDB on this database.
>>
>> Sincerely,
>>
>>
>> Anthony Thomas
>>
>>
>> --
>>
>> "Kevin Stark" <SENDkevo97NO@POTTEDhotMEATmailHERE.com> wrote in message
>> news:eNDe0eV4EHA.2876@TK2MSFTNGP12.phx.gbl...
>> I have a nifty problem with SQL 7.0 (build 1063). Perhaps someone here
>> could
>> shed some light on it. We have a situation where the checkpoint process
>> is
>> hung waiting on a pagiolatch_sh to release for a page that doesn't exist.
>>
>> About midnight last night it appears that an I/O was attempted on this
>> non-existent page. The I/O processor of course threw a tantrum about
>> this,
>> as specified in these SQL log records:
>>
>> 2004-12-13 00:12:43.95 spid363 Error: 5180, Severity: 22, State:
>> 1
>> 2004-12-13 00:12:43.95 spid363 Could not open FCB for invalid
>> file
>> ID 116 in database 'DGFE3x00Prod'. Table or database may be corrupted..
>>
>> SQL just kept going about its business up until the point where another
>> spid
>> attempted to execute a read on this same page. These messages started
>> sprouting up in the log:
>>
>> 2004-12-13 00:26:49.45 spid243 Time out occurred while waiting
>> for
>> buffer latch type 2, bp 0x14e54f80, page (116:16), stat 0x40d, object ID
>> 29:1690181632:0, waittime 500. Continuing to wait.
>>
>> The problem is that file #116 doesn't exist for this database. There are
>> two
>> files: the data and the log. I checked sysfiles and the file ID's are 1
>> and
>> 2. Things really went sideways when the checkpoint process came along and
>> attempted to flush the page. Now this process is blocked because of the
>> outstanding share latch on this page:
>>
>> 2004-12-13 02:09:07.19 spid5 Time out occurred while waiting
>> for
>> buffer latch type 3, bp 0x14e54f80, page (116:16), stat 0x40d, object ID
>> 29:1690181632:0, waittime 500. Continuing to wait.
>> 2004-12-13 02:10:47.19 spid5 Error: 845, Severity: 17, State: 2
>>
>> With the checkpoint process blocked, we have no ability to back up this
>> database now. This is the second time that this has happened, the first
>> occuring about three weeks ago. The first time we just restarted SQL
>> Server
>> and the problem went away. I chalked it up to an anolmoly until it
>> recurred
>> today. The database is still running and the website that it stores data
>> for
>> is doing fine, but this outstanding latch is blocking our ability to back
>> up. I'd like the problem to go away for good without requiring a restart
>> of
>> SQL Server when it happens.
>>
>> Here's the output from nonzero sysprocesses:
>>
>> spid kpid blocked waittype waittime lastwaittype waitresource dbid
>> ------ ------ ------- -------- ----------- --------------------------
> -
>> -----
>> --------------------------------------------------------------------------
> -
>> --------------------------------------------------------------------------
> --
>> --------------------------------------------------------------------------
> --
>> ---------------------------- ------ 2 0 0 0x0000 3157 MISCELLANEOUS
>> 0
>> 3 0 0 0x0000 954 MISCELLANEOUS 0 4 0 0 0x0000 110 MISCELLANEOUS 0
>> 5 0 0 0x0423 26141 PAGEIOLATCH_UP 29:116:16 29 243 3452 0 0x0422
>> 62719
>> PAGEIOLATCH_SH 29:116:16 29We have a case open with Microsoft on this as
>> I'm
>> pretty sure that it's abug somewhere, probably in the free page list
>> maintenance. I'm guessing thatthe fileID and the pageID got concatenated
>> somehow and the free list wasupdated to reference page 116:16 when it
>> should
>> have been page 1:16. DBCCCheckDB and DBCC Checkalloc didn't turn up any
>> allocation errors. I ran adbcc page on page 1:16 and nothing out of the
>> ordinary was there. Obviouslyrunning a dbcc page on page 116:16 threw an
>> error.Has anyone experienced this before?Kevin StarkDirector, Technology
>> ServicesSigma Micro
>>
>>
>
>

Relevant Pages

  • Re: Checkpoint SPID Blocked
    ... really need to run DBCC CHECKDB on this database. ... I have a nifty problem with SQL 7.0. ... We have a situation where the checkpoint process is hung waiting on a pagiolatch_sh to release for a page that doesn't exist. ... DBCCCheckDB and DBCC Checkalloc didn't turn up any allocation errors. ...
    (microsoft.public.sqlserver.server)
  • Re: Checkpoint SPID Blocked
    ... CheckDB and DBCC Checkalloc didn't turn up any allocation errors. ... really need to run DBCC CHECKDB on this database. ... We have a situation where the checkpoint process is ... > SQL just kept going about its business up until the point where another ...
    (microsoft.public.sqlserver.server)
  • RE: Move the monitoring database in Windows Small Business Server 2003
    ... I made a test on my test machine (move monitoring database from C:\Program ... Restarted the server. ... Microsoft CSS Online Newsgroup Support ... <I now cannot restart the service MSSQL$SBSMONITORING. ...
    (microsoft.public.windows.server.sbs)
  • Re: Disaster freeing space in 200GB database
    ... Run DBCC UPDATEUSAGE to make sure you are seeing accurate information. ... DBCC SHRINKFILE since it gives me a finer degree of control. ... Senior Database Administrator ... data file increased by the amount of data deleted. ...
    (microsoft.public.sqlserver.server)
  • RE: SQLServer slows down after some time
    ... performance that is not corrected by a restart. ... rectified by a database service restart. ... We restart SQL Server and everything goes back to ... > components running on different tiers. ...
    (microsoft.public.sqlserver.server)