consistency errors

Can somebody explain to me what is a consistency error and
how does it happens. Is there a way to prevent them ?A consistency error, in its most general form, is when an inconsistency
exists, based on some rules.
In SQL Server, a consistency error is when the structural or logical
integrity of the database or a table within the database has been broken.
Some examples:
1) page X in the database thinks its allocated to table Y, but scanning
table Y does not show any links to page X.
2) A record in the clustered index for table A does not have exactly one
matching record in non-clustered index B on table A
3) A record in the clustered index for table J has a text/image column with
timestamp K, but no text was found with timestamp K in the text index for
table J
There are literally hundreds of such consistency rules inside SQL Server,
all of which are verified with the DBCC CHECKDB command (and related
commands).
Such consistency errors are usually caused by bad hardware corrupting pages
in the database. Given that there's no way to prevent hardware going bad,
the issue is really how to ensure you can recover from a hardware-caused
corruption with minimal downtime and data loss. This involves having a
disaster recovery plan and a solid backup strategy.
I don't have pointers to hand to the existing best-practices docs and KB
articles but I'm sure an MVP will reply-group with them.
Regards,
Paul.
--
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Pollus" <anonymous@.discussions.microsoft.com> wrote in message
news:01e201c3b2c7$3d7af450$a501280a@.phx.gbl...
> Can somebody explain to me what is a consistency error and
> how does it happens. Is there a way to prevent them ?|||As Paul has already explained some issues, especially
hardware problems, can't be totally prevented. You want to
monitor your systems to stay on top of any potential
problems which can arise. Chapter 4 in the Operations Guide
on TechNet has some good checks, recommended frequencies,
info on backups, availability, etc:
http://www.microsoft.com/technet/prodtechnol/sql/maintain/operate/opsguide/sqlops4.asp
Also related would be the 2003 August and September issues
of SQL Magazine had several articles on disaster prevention,
disaster recovery:
www.sqlmag.com
-Sue
On Mon, 24 Nov 2003 12:12:09 -0800, "Pollus"
<anonymous@.discussions.microsoft.com> wrote:
>Can somebody explain to me what is a consistency error and
>how does it happens. Is there a way to prevent them ?|||> I don't have pointers to hand to the existing best-practices docs and KB
> articles but I'm sure an MVP will reply-group with them.
Below is my general recommendations (after going though it in the MVP group). I don't have KB
pointers, though...
Here are the general recommendations for handling a suspect or corrupt database:
0. Ensure you have a backup strategy that you can use to recover from hardware failures (including
corruption). I recommend performing both database and log backup in most situations.
1. If you can run DBCC CHECKDB against the database: Search Books Online and KB for the error
numbers that CHECKDB gives you. There might be specific info for that type of error.
2. Find out why this happened. Check eventlog, do HW diagnostics etc.; search Books Online and KB
for those errors. You don't want this to happen again! If the database is suspect, the file might
have been in use by for instance an anti-virus program and restarting SQL Server might be all that
is needed - but you still want to read logs etc to find out what happened.
3. If there is a hardware problem, ensure the faulty hardware is replaced.
4. Backup the log. This assumes that log backup schedule is in place, of course. If the database is
suspect, then the NO_TRUNCATE option for the RESTORE command must be used. Also, you might want to
do a file backup of the mdf and ldf files, for extra safety.
5. Restore is the best thing to do now. If you managed to backup log as per step 4, then you will
most probably have zero dataloss. You should restore the latest clean database backup and the
subsequent log backups including the one taken in above step.
If the database isn't suspect, then DBCC with a REPAIR option might be a secondary option but this
will often result in loss of data. Additional solutions, depending on the errors, may be to manually
rebuild non-clustered indexes, manually drop and reload a table if the data is static, and so on.
If the database is suspect, a secondary option can be to try to "un-suspect" the database using
sp_resetstatus. Read about it (books online, KB, google etc). It might help but if the database is
too damaged, it might just pop back to suspect again. There's also something called "emergency mode"
which is a "panic" status you can set in order to try to get data out of a damaged database. I think
the name of that option speaks for itself. Again search the net for info.
If you feel uncertain with above steps, I recommend letting MS hand-hold you through the steps
appropriate for your particular situation.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Paul S Randal [MS]" <prandal@.online.microsoft.com> wrote in message
news:eQgnZsssDHA.3436@.tk2msftngp13.phx.gbl...
> A consistency error, in its most general form, is when an inconsistency
> exists, based on some rules.
> In SQL Server, a consistency error is when the structural or logical
> integrity of the database or a table within the database has been broken.
> Some examples:
> 1) page X in the database thinks its allocated to table Y, but scanning
> table Y does not show any links to page X.
> 2) A record in the clustered index for table A does not have exactly one
> matching record in non-clustered index B on table A
> 3) A record in the clustered index for table J has a text/image column with
> timestamp K, but no text was found with timestamp K in the text index for
> table J
> There are literally hundreds of such consistency rules inside SQL Server,
> all of which are verified with the DBCC CHECKDB command (and related
> commands).
> Such consistency errors are usually caused by bad hardware corrupting pages
> in the database. Given that there's no way to prevent hardware going bad,
> the issue is really how to ensure you can recover from a hardware-caused
> corruption with minimal downtime and data loss. This involves having a
> disaster recovery plan and a solid backup strategy.
> I don't have pointers to hand to the existing best-practices docs and KB
> articles but I'm sure an MVP will reply-group with them.
> Regards,
> Paul.
> --
> Paul Randal
> Dev Lead, Microsoft SQL Server Storage Engine
> This posting is provided "AS IS" with no warranties, and confers no rights.
> "Pollus" <anonymous@.discussions.microsoft.com> wrote in message
> news:01e201c3b2c7$3d7af450$a501280a@.phx.gbl...
> > Can somebody explain to me what is a consistency error and
> > how does it happens. Is there a way to prevent them ?
>

connectivity to the databse in SQL server 7

Can somebody tell me, how can I connect to the server(OS WIN NT server 4.0). I have created one database(microsoft SQl server 7.0) in my company server.And I tried to connect using ODBC Data source administrator to connect to it. It's failed. This error prompted.

Connection failed
SQL state'28000'
SQL server driver'18456'
[Microsoft ][ODBC SQL server driver ][SQL server] Login failed for user 'faraidba']

do I need check my login ID for the database I have created or my ODBC driver is obsolete. Please help as I'm new with it.Did you select NT authentication or Server authentication?
I suspect it was server authentication, You have either supplied an invalid username, an invalid password, or some combination of both.
If you used NT authentication, your problem is that your network id has not been setup as a valid login.

If you wish to test your login you can always use I/OSQL.EXE. If you are using NT authentication you should be able to login in using "OSQL -S <your server name> -E", and if using Server authentication try "OSQL -S <your server name> -U <your user id> -P <your password>". If either of these work then you have a problem with ODBC.

give this some thought and let me know what you find.|||Thanx.

I've followed ur suggestion. I used the correct login name and password. The network library: I chose 'name pipes' connection in ODBC to connect to the server and this message propmted;

Connection failed
SQL state:'08004'
SQL serve error:4062
Server rejected the connection;
Access to selected database has been denied

Should I use TCP/IP(network libraries) instead?|||Thanx.

I've followed ur suggestion. I used the correct login name and password. The network library: I chose 'name pipes' connection in ODBC to connect to the server and this message propmted;

Connection failed
SQL state:'08004'
SQL serve error:4062
Server rejected the connection;
Access to selected database has been denied

Should I use TCP/IP(network libraries) instead?

I try to connect using server authentication.|||Okay, I think your problem is that the usreid you are using has not been granted access to the DB you are trying to connect to.

Again if you use I/OSQL.EXE to try and connect as I outlined before you would have connected to your default database. Most of the time this is the master db. If you try usng I/OSQL again but add "-d <your db name>" and see if you don't get a similar message or connect as before and issue the T-SQL command "use <your db name> go".

The NETWORK Library you are using is just fine. The fact that you have connected to the SQL engine is evident by the error message.|||you're correct. now i'm able to do the connection.

hope to seek ur help next time.. ;)
thanks, freind

laziaf238
KL,
Malaysia

Connectivity Problem for Expert

Hi All,
I have an intriguing problem and I wanted to see if somebody could help me.
ENVIRONMENT:
Server Side: 2 identical Windows servers 2000 with SQL Server 2000
Enterprise Edition SP3a
Client Side: workstation with Windows 2000 PRO
Protocols enabled in the server and client: TCP/IP and Named Pipes.
1) I create a new user in the domain, DM001\testesql
2) I create a new login in the SQL Server (one in each SQL Server). For
this, I used script below:
EXEC sp_grantlogin 'DM001\testesql'
GO
EXEC sp_defaultdb 'DM001\testesql', 'Pubs'
GO
USE Pubs
GO
EXEC sp_grantdbaccess 'DM001\testesql', 'testesql'
GO
EXEC sp_addrolemember 'db_owner', 'testesql'
GO
sp_helpuser
THE PROBLEM:
After this, I connected myself in one workstation with the new user of
domain (DM001\testesql)and try a connection via osql utility using the
command line below. Well, in 1 server the connection was made successfully
and got the waited result, but in the other server I received the error:
Login failed for user '(null)'. Reason: Not associated with a trusted SQL
Server connection.
Comand line for SQLServer1 ==> successfully
C:\osql - SSQLServer1 - and - Q"select TOP 1 au_lname, au_fname from
pubs..authors".
--Result:
au_lname au_fname
--- --
Bennet Abraham
(1 row(s) affected)
Comand line for SQLServer2 ==> failed
C:\osql - SSQLServer2 - and - Q"select TOP 1 au_lname, au_fname from
pubs..authors".
Login failed for user '(null)'. Reason: Not associated with a trusted SQL
Server connection.
Please, somebody has idea of that can be happening?
Thx
Nilton Pinheiro
Message posted via http://www.droptable.com
Sorry... SQL Server is set up to use Mixed Mode Authentication in both the
servers.
Thx
Nilton
Message posted via http://www.droptable.com
|||Hi... I solve this problem change permission in Local Policy
thx
Message posted via http://www.droptable.com
|||If you try to connect to a SQL Server using windows
authentication and the error is:
Login failed for user '(null)'.
this generally indicates that the user can't be validated
through the domain controller or the local security database
so null is passed to SQL Server. So it's generally
indicative of an issue with the account, access to the
domain controller or something along those lines.
Check the event logs on the client PC and look for any
network, domain related issues. Check the event logs on the
DC as well.
-Sue
On Wed, 04 May 2005 20:33:12 GMT, "Nilton Pinheiro via
droptable.com" <forum@.nospam.droptable.com> wrote:

>Hi All,
>I have an intriguing problem and I wanted to see if somebody could help me.
>ENVIRONMENT:
>Server Side: 2 identical Windows servers 2000 with SQL Server 2000
>Enterprise Edition SP3a
>Client Side: workstation with Windows 2000 PRO
>Protocols enabled in the server and client: TCP/IP and Named Pipes.
>1) I create a new user in the domain, DM001\testesql
>2) I create a new login in the SQL Server (one in each SQL Server). For
>this, I used script below:
>EXEC sp_grantlogin 'DM001\testesql'
>GO
>EXEC sp_defaultdb 'DM001\testesql', 'Pubs'
>GO
>USE Pubs
>GO
>EXEC sp_grantdbaccess 'DM001\testesql', 'testesql'
>GO
>EXEC sp_addrolemember 'db_owner', 'testesql'
>GO
>sp_helpuser
>
>THE PROBLEM:
>After this, I connected myself in one workstation with the new user of
>domain (DM001\testesql)and try a connection via osql utility using the
>command line below. Well, in 1 server the connection was made successfully
>and got the waited result, but in the other server I received the error:
>Login failed for user '(null)'. Reason: Not associated with a trusted SQL
>Server connection.
>Comand line for SQLServer1 ==> successfully
>C:\osql - SSQLServer1 - and - Q"select TOP 1 au_lname, au_fname from
>pubs..authors".
>--Result:
>au_lname au_fname
>--- --
>Bennet Abraham
>(1 row(s) affected)
>Comand line for SQLServer2 ==> failed
>C:\osql - SSQLServer2 - and - Q"select TOP 1 au_lname, au_fname from
>pubs..authors".
>Login failed for user '(null)'. Reason: Not associated with a trusted SQL
>Server connection.
>Please, somebody has idea of that can be happening?
>Thx
>Nilton Pinheiro
|||Hi Nilton,
I am having similar issue, can you share what steps you followed to resolve
this issue.
--Manoj
"Nilton Pinheiro via droptable.com" wrote:

> Hi... I solve this problem change permission in Local Policy
> thx
> --
> Message posted via http://www.droptable.com
>

Connectivity Problem for Expert

Hi All,
I have an intriguing problem and I wanted to see if somebody could help me.
ENVIRONMENT:
Server Side: 2 identical Windows servers 2000 with SQL Server 2000
Enterprise Edition SP3a
Client Side: workstation with Windows 2000 PRO
Protocols enabled in the server and client: TCP/IP and Named Pipes.
1) I create a new user in the domain, DM001\testesql
2) I create a new login in the SQL Server (one in each SQL Server). For
this, I used script below:
EXEC sp_grantlogin 'DM001\testesql'
GO
EXEC sp_defaultdb 'DM001\testesql', 'Pubs'
GO
USE Pubs
GO
EXEC sp_grantdbaccess 'DM001\testesql', 'testesql'
GO
EXEC sp_addrolemember 'db_owner', 'testesql'
GO
sp_helpuser
THE PROBLEM:
After this, I connected myself in one workstation with the new user of
domain (DM001\testesql)and try a connection via osql utility using the
command line below. Well, in 1 server the connection was made successfully
and got the waited result, but in the other server I received the error:
Login failed for user '(null)'. Reason: Not associated with a trusted SQL
Server connection.
Comand line for SQLServer1 ==> successfully
C:\osql - SSQLServer1 - and - Q"select TOP 1 au_lname, au_fname from
pubs..authors".
--Result:
au_lname au_fname
--- --
Bennet Abraham
(1 row(s) affected)
Comand line for SQLServer2 ==> failed
C:\osql - SSQLServer2 - and - Q"select TOP 1 au_lname, au_fname from
pubs..authors".
Login failed for user '(null)'. Reason: Not associated with a trusted SQL
Server connection.
Please, somebody has idea of that can be happening?
Thx
Nilton Pinheiro
Message posted via http://www.droptable.comSorry... SQL Server is set up to use Mixed Mode Authentication in both the
servers.
Thx
Nilton
Message posted via http://www.droptable.com|||Hi... I solve this problem change permission in Local Policy
thx
Message posted via http://www.droptable.com|||If you try to connect to a SQL Server using windows
authentication and the error is:
Login failed for user '(null)'.
this generally indicates that the user can't be validated
through the domain controller or the local security database
so null is passed to SQL Server. So it's generally
indicative of an issue with the account, access to the
domain controller or something along those lines.
Check the event logs on the client PC and look for any
network, domain related issues. Check the event logs on the
DC as well.
-Sue
On Wed, 04 May 2005 20:33:12 GMT, "Nilton Pinheiro via
droptable.com" <forum@.nospam.droptable.com> wrote:

>Hi All,
>I have an intriguing problem and I wanted to see if somebody could help me.
>ENVIRONMENT:
>Server Side: 2 identical Windows servers 2000 with SQL Server 2000
>Enterprise Edition SP3a
>Client Side: workstation with Windows 2000 PRO
>Protocols enabled in the server and client: TCP/IP and Named Pipes.
>1) I create a new user in the domain, DM001\testesql
>2) I create a new login in the SQL Server (one in each SQL Server). For
>this, I used script below:
>EXEC sp_grantlogin 'DM001\testesql'
>GO
>EXEC sp_defaultdb 'DM001\testesql', 'Pubs'
>GO
>USE Pubs
>GO
>EXEC sp_grantdbaccess 'DM001\testesql', 'testesql'
>GO
>EXEC sp_addrolemember 'db_owner', 'testesql'
>GO
>sp_helpuser
>
>THE PROBLEM:
>After this, I connected myself in one workstation with the new user of
>domain (DM001\testesql)and try a connection via osql utility using the
>command line below. Well, in 1 server the connection was made successfully
>and got the waited result, but in the other server I received the error:
>Login failed for user '(null)'. Reason: Not associated with a trusted SQL
>Server connection.
>Comand line for SQLServer1 ==> successfully
>C:\osql - SSQLServer1 - and - Q"select TOP 1 au_lname, au_fname from
>pubs..authors".
>--Result:
>au_lname au_fname
>--- --
>Bennet Abraham
>(1 row(s) affected)
>Comand line for SQLServer2 ==> failed
>C:\osql - SSQLServer2 - and - Q"select TOP 1 au_lname, au_fname from
>pubs..authors".
>Login failed for user '(null)'. Reason: Not associated with a trusted SQL
>Server connection.
>Please, somebody has idea of that can be happening?
>Thx
>Nilton Pinheiro|||Hi Nilton,
I am having similar issue, can you share what steps you followed to resolve
this issue.
--Manoj
"Nilton Pinheiro via droptable.com" wrote:

> Hi... I solve this problem change permission in Local Policy
> thx
> --
> Message posted via http://www.droptable.com
>