Prev: Index DMV help needed
Next: on line documentation for SQLSERVER 2005
From: Netaji on 27 May 2010 16:47
As everybody is surprised why we have Reinstalled SQL Server, the Reason for
this is ->
We were not able to access any database neither our application was getting
Database response.
We have also restarted the SQL Server services and Server as well
When we tried accessing database from SQL Server Management Studio we got
connected to server but on click on Server to expand the objects (databases)
we were getting following error.
“Could not find database ID 2, name 'tempdb'. The database may be offline.
Wait a few minutes and try again. “
The same error was also getting recorded in Event Viewer.
Now as per most of the reply it seems to hardware or Disk failure but we
have not received any alarm of disk failure on the server.
Now problem is how to confirm that this problem is due bad hardware or
corrupted disk. Is there any tool to find out disk issues?
How to confirm that reason of “SQL Server 2005 Failure - Logical
consistency-based I/O error with Tempdb”


"Erland Sommarskog" wrote:

> Netaji (Netaji(a)discussions.microsoft.com) writes:
> > Few days back we faced an issue with Microsoft SQL Server 2005. The
> > error message which I retrieved from Eventviewer at the time of SQL
> > server failure is as follows –
> >
> > SQL Server detected a logical consistency-based I/O error: incorrect
> > pageid (expected 1:932354; actual 22624:1041497854). It occurred during
> > a read of page (1:932354) in database ID 2 at offset 0x000001c7404000 in
> > file
> > 'C:\Program Files (x86)\Microsoft SQL
> Server\MSSQL.1\MSSQL\DATA\tempdb.mdf'.
> > Additional messages in the SQL Server error log or system event log may
> > provide more detail. This is a severe error condition that threatens
> > database integrity and must be corrected immediately. Complete a full
> > database consistency check (DBCC CHECKDB). This error can be caused by
> > many factors; for more information, see SQL Server Books Online.
> > For more information, see Help and Support Center at
> > http://go.microsoft.com/fwlink/events.asp.
> >
> >
> > This has happened twice with production database server and it makes the
> > SQL Server inaccessible and leaves us with no option other than
> > reinstalling SQL Server. Please let us know the root cause of this
> > failure and what are the preventive measures we can opt to avoid the
> > same failure in future.
>
> Reinstall? That sounds a bit hefty.
>
> Anyway, the most likely reason for this is bad hardware. You should
> replace that disk before disaster strikes for real. And, yeah, since
> you have SQL Server on that disk, you may be in for a reinstall, unless
> you can copy the disk with Ghost or similar program.
>
>
> --
> Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se
>
> Links for SQL Server Books Online:
> SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
> SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
> SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
>
> .
>
From: Erland Sommarskog on 27 May 2010 18:04
Netaji (Netaji(a)discussions.microsoft.com) writes:
> We have also restarted the SQL Server services and Server as well
> When we tried accessing database from SQL Server Management Studio we got
> connected to server but on click on Server to expand the objects
> (databases) we were getting following error.
> �Could not find database ID 2, name 'tempdb'. The database may be offline.
> Wait a few minutes and try again. �

Tempdb is recreated everytime SQL Server starts. If tempdb is not
there, well, something is very fishy.

> The same error was also getting recorded in Event Viewer.
> Now as per most of the reply it seems to hardware or Disk failure but we
> have not received any alarm of disk failure on the server.
> Now problem is how to confirm that this problem is due bad hardware or
> corrupted disk. Is there any tool to find out disk issues?

The event log may include warnings about hardware problems.

At the point where tempdb could not be found, you have tried to
navigate to that location in Explorer. I guess there would have been
a few problems.

As for the disk not showing any errors, well, I once had a conversation
with an IT guy a client whose database had gone belly-up. When I told
him the disk was bad, he told me that everything was OK in the SAN.
But I had read the logs frmo DBCC, and there were corruption errors
in them. And the errors in the error log before the database died
were very spooky. And were red dots in the Event Viewer for this
disk.

> How to confirm that reason of �SQL Server 2005 Failure - Logical
> consistency-based I/O error with Tempdb�

Generally, I think it is difficult to make a definite analysis post
mortem when everything has been repaired. But keep your eyes open,
run DBCC and check the logs.


--
Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

 | 
Pages: 1
Prev: Index DMV help needed
Next: on line documentation for SQLSERVER 2005