Solved

SQL server 2008 SP4

Posted on 2016-11-13
29
66 Views
Last Modified: 2016-11-16
hi,

today we see an serious error message from SQL 2008 SP4 again:

DESCRIPTION:      Internal error. Buffer provided to read column value is too small. Run DBCC CHECKDB to check for any corruption.

and from our monitoring SW, it seems that when this kind of error message comes out, before that we will see tempdb disk queue long.

do you all see sth like on SQL 2008 SP4, if tempdb disk too busy, SQL server will have sth like that ?
0
Comment
Question by:marrowyung
  • 16
  • 8
  • 5
29 Comments
 
LVL 28

Accepted Solution

by:
Pawan Kumar earned 250 total points
ID: 41885812
try..

When did you get this error , Are you running a query involving temp DB ? If yes is your temp table has indexes ?  With this error ..

It looks like your DB is corrupt. You can restore it from the last good backup.

If you dont'want to use above option or you dont have backup then try below-

run DBCC CHECKDB

The above will tell you the minimum repair level required. If you are blessed then It will be REPAIR_REBUILD, which will attempt to fix the corruption without deleting any data.

Otherwise you will be have to use with REPAIR_ALLOW_DATA_LOSS, which will delete data and break referential integrity to try to fix the corruption.

Also note that you should run DBCC CHECKCONSTRAINTS after any repair operation.

Once your database is back to a usable state - you should create a SQL job to back your database up on a regular basis. Also test these backups so that you can restore in case of any issues.
0
 
LVL 1

Author Comment

by:marrowyung
ID: 41885901
"When did you get this error , Are you running a query involving temp DB ? If yes is your temp table has indexes ?  With this error .."

it doesn't matter on when I get this error, right? is this morning.

our queries always use tempdb as we use a lot of @, # and ##table.

as temp table create on the fly I can't see it ALL the time but they got a business to add primary key for all temp table.

"It looks like your DB is corrupt. You can restore it from the last good backup."

we dbcc checkdb before and consultant also say our DB do not have problem.

"Once your database is back to a usable state - you should create a SQL job to back your database up on a regular basis. Also test these backups so that you can restore in case of any issues."


done for sure.
0
 
LVL 48

Expert Comment

by:Vitor Montalvão
ID: 41885945
Are you starting to loop into your questions?
You already had a similar question around 4 months ago: https://www.experts-exchange.com/questions/28958456/Buffer-provided-to-read-column-value-is-too-small-Run-DBCC-CHECKDB-to-check-for-any-corruption.html
0
Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

 
LVL 1

Author Comment

by:marrowyung
ID: 41885977
good ,sth like this but with other thinking.

the checkdb don't work for us man. there are sth else.
0
 
LVL 48

Expert Comment

by:Vitor Montalvão
ID: 41885980
the checkdb don't work for us man.
What do you mean? It returning error when you try to run it?
0
 
LVL 1

Author Comment

by:marrowyung
ID: 41886012
it don't but error message still coming.
0
 
LVL 48

Expert Comment

by:Vitor Montalvão
ID: 41886082
The message only says to run DBCC CHECKDB to check for errors. Since isn't returning errors then isn't a db corruption.

Did you identify the query that is originating the error message?
How much memory do you have for your server and how much is reserved for SQL Server?
How big is your tempdb?
0
 
LVL 1

Author Comment

by:marrowyung
ID: 41887278
"Did you identify the query that is originating the error message?"

no. not much information at all, a lot of thing is running at that time including update statistics.

"How much memory do you have for your server and how much is reserved for SQL Server?

that box we have 384GB of RAM, already too much.

"How big is your tempdb?"

160GB.
0
 
LVL 1

Author Comment

by:marrowyung
ID: 41887279
I am not trying to make the update statistics to use 50% sample only but not full scan, as the CPU during this morning time always between 80-95%, I want to low it down to see if it is the cause of SQL 2008 with sP4 too busy.

but that update statistics don't use tempdb much but CPU
0
 
LVL 48

Assisted Solution

by:Vitor Montalvão
Vitor Montalvão earned 250 total points
ID: 41887993
Why do you think you need to run Update Statistics?
Don't you have Ola's index job running daily?
0
 
LVL 1

Author Comment

by:marrowyung
ID: 41888998
a lot of reason and server is different from server to server, I have tried that before and it kills a lot of operation on Asia time zone.

that's why we found out later that index rebulid and update statistic task are not the same across server, DB for Asia and for US is quite diff in terms of that.

today  that update statistics task has been disabled and error message at at this moment is only:


DESCRIPTION:      SSPI handshake failed with error code 0x8009030c while establishing a connection with integrated security; the connection has been closed


which I found out ONLY because someone login unsuccessfully? so if someone keep login unsuccessfully this message keep happening ?
0
 
LVL 28

Expert Comment

by:Pawan Kumar
ID: 41889140
I think we should have index rebuild and update statistic task for each DB on the server irrespective of the region.
0
 
LVL 1

Author Comment

by:marrowyung
ID: 41889155
yeah,

you can say this, but here, DB serve diff purpose. so data it store is diff.

just like the replication dataset for US and Asia is diff.
0
 
LVL 28

Expert Comment

by:Pawan Kumar
ID: 41889165
Okies...!!
0
 
LVL 1

Author Comment

by:marrowyung
ID: 41889174
we have error message like this too:

DESCRIPTION:      A user request from the session with SPID 105 generated a fatal exception. SQL Server is terminating this session. Contact Product Support Services with the dump produced in the log directory.

it only comes from SQL server 2008 with SP4 but not SP3, that's why I rise this ticket.
0
 
LVL 28

Expert Comment

by:Pawan Kumar
ID: 41889190
There is a fix from Microsoft for this via KB 2515286.. Have you tried the below ?

https://support.microsoft.com/en-us/kb/2587929

Hope it helps !
0
 
LVL 1

Author Comment

by:marrowyung
ID: 41889238
however, we are using SQL2008 SP4 ,it don't apply to us , right?
0
 
LVL 1

Author Comment

by:marrowyung
ID: 41889240
what is a  XQuery ?
0
 
LVL 28

Expert Comment

by:Pawan Kumar
ID: 41889248
XQuery is basically a language for querying the XML data. Consider XQuery for XML just like we have T-SQL for SQL Server. It is built on XPath expressions and supported by SQL Server.
0
 
LVL 1

Author Comment

by:marrowyung
ID: 41889251
but it only for SQL server 2008 up do SP3, however our SP3 don't have this problem at all
0
 
LVL 48

Expert Comment

by:Vitor Montalvão
ID: 41889260
that's why we found out later that index rebulid and update statistic task are not the same across server, DB for Asia and for US is quite diff in terms of that.
As DBA you should have a standard maintenance plan across all your server is all world. We here also have plenty of servers around the world but we make sure they all use the same maintenance plan scripts. This will make your daily tasks much easier to perform.

today  that update statistics task has been disabled and error message at at this moment is only:
If you have your reindexes jobs running daily you won't need to update statistics at all. In fact from SQL Server 2014 onwards, update statistics is automatic so you should start to work like this so in the future the migration will be smoother.
0
 
LVL 1

Author Comment

by:marrowyung
ID: 41889292
"As DBA you should have a standard maintenance plan across all your server is all world. We here also have plenty of servers around the world but we make sure they all use the same maintenance plan scripts. This will make your daily tasks much easier to perform."

Victor, as I said, out DB is serve diff purpose, I tried to do this before but it kill the SQL serve has the maintenance task locks  a lot of operation.

"If you have your reindexes jobs running daily you won't need to update statistics at all. "

why ? we are SQL 2008 only. we are not SQL 2014 yet.

I prefer that as this means it is done but not mean might be, should be.
0
 
LVL 48

Expert Comment

by:Vitor Montalvão
ID: 41889297
why ? we are SQL 2008 only. we are not SQL 2014 yet.
Even in MSSQL 2008 or 2005 I mostly didn't need to use Update Statistics task. Actually I only remembered to use it in one particular situation for a SAP table that has 2TB size and will took more than 8h to reindex so we decided for that particular table to only reindex once by year (during a maintenance window) and update statistics weekly.
0
 
LVL 48

Expert Comment

by:Vitor Montalvão
ID: 41889300
Now about your new error:
SSPI handshake failed with error code 0x8009030c while establishing a connection with integrated security; the connection has been closed
This is all you got from the error message? There's not a Reason part missing here? Check in the logs for the login that originated the error and then verify if the user is enabled, not locked or doesn't have his password expired in AD.
0
 
LVL 1

Author Comment

by:marrowyung
ID: 41889392
Victor,

is this one:

"DESCRIPTION:      A user request from the session with SPID 105 generated a fatal exception. SQL Server is terminating this session. Contact Product Support Services with the dump produced in the log directory.
"

we are using SQL 2008 SP4 on that box and only that box has problem.

"Check in the logs for the login that originated the error and then verify if the user is enabled, not locked or doesn't have his password expired in AD."

that account is not locked, it is normal, just a login failure.
0
 
LVL 48

Expert Comment

by:Vitor Montalvão
ID: 41889400
The SSPI handshake failed with error code 0x8009030c... error has been solved?

The other error is very generic. There's no more information in the message that can lead us to help you.
0
 
LVL 1

Author Comment

by:marrowyung
ID: 41890788
Victor, this is all I got from the log.
0
 
LVL 1

Author Comment

by:marrowyung
ID: 41890797
we will upgrade the SQL server to SQL2014 very soon, so let's see if it STILL happens.
0
 
LVL 1

Author Closing Comment

by:marrowyung
ID: 41890798
tks all anyway.
0

Featured Post

Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

821 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question