Prev: Instead of UNION?
Next: Simple Round call results in Arithmetic overflow
From: Erland Sommarskog on 29 Jul 2010 07:43
HP (HP(a)text.com) writes:
> Thanks for reply
>
> I have run CHECKDB and get below message
> CHECKDB found 0 allocation errors and 0 consistency errors in database
> 'db_Name'.
>
> I think there may be another issue.


Ah, I did not look closely on the original error message. The database which
has corruption issues is tempdb (database 2). The good news is that there is
no problem with data loss. Still something needs to be done. Is tempdb on
the C: disk, or elsewhere? If it's on the C-disk, maybe all you need to do
is to replace this disk.

Corruption errors in tempdb may be more difficult to catch. Since tables
gets created and dropped, allocation errors may come and go.


--
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: HP on 29 Jul 2010 08:18
Hi,

But why i m not getting that error while running that sp in sql?
and I have also checkdb for tempdb and no error for the same.

Thanks,
Hemant

"Erland Sommarskog" <esquel(a)sommarskog.se> wrote in message
news:Xns9DC48BA11640CYazorman(a)127.0.0.1...
> HP (HP(a)text.com) writes:
>> Thanks for reply
>>
>> I have run CHECKDB and get below message
>> CHECKDB found 0 allocation errors and 0 consistency errors in database
>> 'db_Name'.
>>
>> I think there may be another issue.
>
>
> Ah, I did not look closely on the original error message. The database
> which
> has corruption issues is tempdb (database 2). The good news is that there
> is
> no problem with data loss. Still something needs to be done. Is tempdb on
> the C: disk, or elsewhere? If it's on the C-disk, maybe all you need to do
> is to replace this disk.
>
> Corruption errors in tempdb may be more difficult to catch. Since tables
> gets created and dropped, allocation errors may come and go.
>
>
> --
> 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 29 Jul 2010 11:07
HP (HP(a)text.com) writes:
> But why i m not getting that error while running that sp in sql?

Because you probably had a different query plan. By default when you connect
to SQL Server, the setting ARITHABORT is OFF. But when you connect with
SSMS, SSMS set this setting ON by default. (Which you can control under
Options->Query Execution.) ARITHABORT is a cache key, meaning that different
settings will yield different cache entries. If the plans are created at
different occassions, and with different input parameters (SQL Server sniffs
the input values on compilation), you can get different plans. Normally this
is testified in different execution times. This time the difference was a
lot more brutal.

The ironic things is that as long as ANSI_WARNINGS is ON (which it is by
default), ARITHABORT has no functional impact whatsoever.

> and I have also checkdb for tempdb and no error for the same.

Not surprising. Since the situation in tempdb is dynamic, consistency errors
can come and go. Still, they don't come out of nowhere. They *can* be due to
bugs in SQL Server, but hardware errors are more likely. Then again, since
lot of tempdb is in cache, maybe this be a token of a bug. Unless you have
faulty a memory board. But if the latter, you would see other misery on the
machine as well, like crashing applications etc, blue screens etc.

I think you should keep on eye on tempdb, maybe run DBCC on it every now and
then, provided that this is permissible with the rest of the situation on
the server. If nothing more appears, but this query continues to error out,
then there is probably some bug of some sort. Exactly which version of SQL
Server do you have? That is, what does SELECT @@version report?


--
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: HP on 30 Jul 2010 02:41
Hi,
Thanks for ur reply......
I found solution at : http://support.microsoft.com/kb/916086

Thanks,
HP

"Erland Sommarskog" <esquel(a)sommarskog.se> wrote in message
news:Xns9DC4AE2CB138Yazorman(a)127.0.0.1...
> HP (HP(a)text.com) writes:
>> But why i m not getting that error while running that sp in sql?
>
> Because you probably had a different query plan. By default when you
> connect
> to SQL Server, the setting ARITHABORT is OFF. But when you connect with
> SSMS, SSMS set this setting ON by default. (Which you can control under
> Options->Query Execution.) ARITHABORT is a cache key, meaning that
> different
> settings will yield different cache entries. If the plans are created at
> different occassions, and with different input parameters (SQL Server
> sniffs
> the input values on compilation), you can get different plans. Normally
> this
> is testified in different execution times. This time the difference was a
> lot more brutal.
>
> The ironic things is that as long as ANSI_WARNINGS is ON (which it is by
> default), ARITHABORT has no functional impact whatsoever.
>
>> and I have also checkdb for tempdb and no error for the same.
>
> Not surprising. Since the situation in tempdb is dynamic, consistency
> errors
> can come and go. Still, they don't come out of nowhere. They *can* be due
> to
> bugs in SQL Server, but hardware errors are more likely. Then again, since
> lot of tempdb is in cache, maybe this be a token of a bug. Unless you have
> faulty a memory board. But if the latter, you would see other misery on
> the
> machine as well, like crashing applications etc, blue screens etc.
>
> I think you should keep on eye on tempdb, maybe run DBCC on it every now
> and
> then, provided that this is permissible with the rest of the situation on
> the server. If nothing more appears, but this query continues to error
> out,
> then there is probably some bug of some sort. Exactly which version of SQL
> Server do you have? That is, what does SELECT @@version report?
>
>
> --
> 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 30 Jul 2010 05:10
HP (HP(a)text.com) writes:
> Hi,
> Thanks for ur reply......
> I found solution at : http://support.microsoft.com/kb/916086

Ah, that's great news!

--
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

First  |  Prev  | 
Pages: 1 2
Prev: Instead of UNION?
Next: Simple Round call results in Arithmetic overflow