Re: Checkpoint SPID Blocked

From: AnthonyThomas (Anthony.Thomas_at_CommerceBank.com)
Date: 12/14/04 

Date: Tue, 14 Dec 2004 07:34:14 -0600

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: SQL DB defekt wer repariert sowas?
    ... Hilfreich knnten da SQL Compare und SQL Data Compare sein. ... DBCC CHECKDB mit der Option ... In letzter Konsequenz bleibt dir noch DBCC CHECKDB mit der Option ...
    (microsoft.public.de.sqlserver)
  • EXCEPTION_ACCESS_VIOLATION Error
    ... One of our clients is using SQL7/SP4 and their database is working correctly ... DBCC CheckDB at our office produces the following: ... DBCC results for 'JagTrain'. ...
    (microsoft.public.sqlserver.server)
  • Re: dbcc checkdb fix
    ... Run DBCC UPDATEUSAGE." ... Randal and read what he has to say about DBCC CHECKDB (for instance ... Tibor Karaszi, SQL Server MVP ... errors in database 'databasename'. ...
    (microsoft.public.sqlserver.server)
Loading