Prev: command
Next: Distributed Transaction on SQL 2005
From: SQL Programmer on 14 Jul 2010 10:40
Hello:

We have two database integrity checks jobs in SQL 2005 that are failing.
One job is for our financial app databases, while the other is for the SQL
system databases.

I know how to conduct the typical fix for the financial app databases--run
DBCC CHECKDB to find the questionable tables that require the DBCC
UPDATEUSAGE script run against those tables.

But, it concerns me that the database integrity job against the system
databases is failing. Regardless, can I safely run the same DBCC CHECKDB and
DBCC UPDATEUSAGE scripts to eliminate the issues causing the system database
job to fail?

SQL Programmer (it's just a name)
From: Erland Sommarskog on 14 Jul 2010 18:15
SQL Programmer (SQLProgrammer(a)discussions.microsoft.com) writes:
> We have two database integrity checks jobs in SQL 2005 that are failing.
> One job is for our financial app databases, while the other is for the SQL
> system databases.
>
> I know how to conduct the typical fix for the financial app databases--run
> DBCC CHECKDB to find the questionable tables that require the DBCC
> UPDATEUSAGE script run against those tables.
>
> But, it concerns me that the database integrity job against the system
> databases is failing. Regardless, can I safely run the same DBCC
> CHECKDB and DBCC UPDATEUSAGE scripts to eliminate the issues causing the
> system database job to fail?

I have never heard of DBCC UPDATEUSAGE being good for fixing integrity
errors, but maybe that is something I've missed.

What error messages do you get from DBCC CHECKDB?


--
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: SQL Programmer on 14 Jul 2010 21:33
Update:

I looked in the SQL logs and they say Failed: (0) Alter failed for server
'ComputerName\\InstanceName'.

According to research that I did, the following needs to be run in SQL:

sp_configure 'allow updates', 0
reconfigure with override
go
--and possibly:
sp_configure 'awe enabled', 0
RECONFIGURE
GO

Is this correct? And, if turning off allow updates is the answer, what's
the reason for having allow updates anyway? I don't recall ever turning that
option on.

This is 64-bit, by the way.


"Erland Sommarskog" wrote:

> SQL Programmer (SQLProgrammer(a)discussions.microsoft.com) writes:
> > We have two database integrity checks jobs in SQL 2005 that are failing.
> > One job is for our financial app databases, while the other is for the SQL
> > system databases.
> >
> > I know how to conduct the typical fix for the financial app databases--run
> > DBCC CHECKDB to find the questionable tables that require the DBCC
> > UPDATEUSAGE script run against those tables.
> >
> > But, it concerns me that the database integrity job against the system
> > databases is failing. Regardless, can I safely run the same DBCC
> > CHECKDB and DBCC UPDATEUSAGE scripts to eliminate the issues causing the
> > system database job to fail?
>
> I have never heard of DBCC UPDATEUSAGE being good for fixing integrity
> errors, but maybe that is something I've missed.
>
> What error messages do you get from DBCC CHECKDB?
>
>
> --
> 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 15 Jul 2010 04:25
SQL Programmer (SQLProgrammer(a)discussions.microsoft.com) writes:
> Update:
>
> I looked in the SQL logs and they say Failed: (0) Alter failed for server
> �ComputerName\\InstanceName�.
>
> According to research that I did, the following needs to be run in SQL:
>
> sp_configure 'allow updates', 0
> reconfigure with override
> go
> --and possibly:
> sp_configure 'awe enabled', 0
> RECONFIGURE
> GO
>
> Is this correct? And, if turning off allow updates is the answer,
> what�s the reason for having allow updates anyway? I don�t recall ever
> turning that option on.

'Allow updates' should definitely be 0. There are extreme situations
where you want to update the system catalog directly, in which case
you set it to 1 for the occasion.

'awe enabled' is, if I remember correctly, a no-op on 64-bit SQL Server.




--
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: command
Next: Distributed Transaction on SQL 2005