Re: Checkpoint SPID Blocked
From: AnthonyThomas (Anthony.Thomas_at_CommerceBank.com)
Date: 12/14/04
- Next message: Ken: "Re: open_tran = 1"
- Previous message: AnthonyThomas: "Re: Move System BD's?"
- In reply to: Kevin Stark: "Checkpoint SPID Blocked"
- Next in thread: Kevin Stark: "Re: Checkpoint SPID Blocked"
- Reply: Kevin Stark: "Re: Checkpoint SPID Blocked"
- Messages sorted by: [ date ] [ thread ]
Date: Mon, 13 Dec 2004 20:13:45 -0600
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
- Next message: Ken: "Re: open_tran = 1"
- Previous message: AnthonyThomas: "Re: Move System BD's?"
- In reply to: Kevin Stark: "Checkpoint SPID Blocked"
- Next in thread: Kevin Stark: "Re: Checkpoint SPID Blocked"
- Reply: Kevin Stark: "Re: Checkpoint SPID Blocked"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
