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

HI all,

get an error like this for SQL server, any idea?

"DESCRIPTION:      Internal error. Buffer provided to read column value is too small. Run DBCC CHECKDB to check for any corruption."
LVL 1
marrowyungSenior Technical architecture (Data)Asked:
Who is Participating?
 
Michael PfisterCommented:
Or deactivate it  for a while and see if the error goes  away. You can check the SQL server log directly
0
 
Michael PfisterCommented:
I assume you ran DBCC CHECKDB and there was no error?
Please post exact SQL server version service pack and hotfix level.

How to determine the version, edition and update level of SQL Server and its components
0
 
Michael PfisterCommented:
If its SQL 2005, make sure SP2 is installed. Then grab and install the latest cumulative update Cumulative update package 17 for SQL Server 2005 Service Pack 2
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
marrowyungSenior Technical architecture (Data)Author Commented:
it is SQL 2008 with SP4.
0
 
Nakul VachhrajaniTechnical Architect, Capgemini IndiaCommented:
Please find the steps to dig out more troubleshooting information below:
1. Check the SQL Server Error Logs - what is the error that has been logged in the error logs?
2. Perform a CHECKDB with DATA PURITY on the tables involved in the query
3. If CHECKDB is successful, rebuild the indexes on the tables involved in the query
4. Check your code to confirm whether your UPDATE and DELETE statements have WITH (NOLOCK) in them
0
 
marrowyungSenior Technical architecture (Data)Author Commented:
hi,

sorry, has a week vocation and back now.

"1. Check the SQL Server Error Logs - what is the error that has been logged in the error logs?"

exactly that error message I post.

"2. Perform a CHECKDB with DATA PURITY on the tables involved in the query"

i am sorry, it is about the whole database, right?  not a particular table, right?

we don't have a single query to cause that and we only receive that error.

so we do this :

 
 DBCC CHECKDB  <Database name> with DATA PURITY 

Open in new window


with this, it is not going to repair anything for me but just check ? I am worry about once it runs, my user database corrupt even further.

    if only do DBCC checkdB, it will only check the current user database? how about I want to find out which one has DBCC checkdb error, any script for me to do this ?

"3. If CHECKDB is successful, rebuild the indexes on the tables involved in the query"

we rebuild index every week, problem still there.

"4. Check your code to confirm whether your UPDATE and DELETE statements have WITH (NOLOCK) in them"

we usually do select with no lock, does it matter ?

but why is this related?
0
 
Michael PfisterCommented:
Before we try to reapir something we need to know whats the problem and how bad it is.
So please run the DBCC statement as soon as possible and when there is low or no usage of the database

https://msdn.microsoft.com/en-us/library/ms176064.aspx
0
 
marrowyungSenior Technical architecture (Data)Author Commented:
hi,

I found this one also, I am ok with it but just concern about the whole statement.
0
 
Michael PfisterCommented:
0
 
Michael PfisterCommented:
You can even omit the DATA PURITY parameter, this check is on by default if its a native SQL 2008 (not upgraded from 2005).
0
 
marrowyungSenior Technical architecture (Data)Author Commented:
so dbcc checkDB and it should only check the DB first anyway and what else I should do ?
0
 
Michael PfisterCommented:
Depends how many databeses are hosted on your system.

If its only a handful, run dbcc checkdb <database name> manually for each database.
If there are many databases use the TechNet script
0
 
marrowyungSenior Technical architecture (Data)Author Commented:
I forgot if it will fix it for us too or just report ?
0
 
Michael PfisterCommented:
just Report. For fixing you need to put in single user mode.
0
 
marrowyungSenior Technical architecture (Data)Author Commented:
oh my god. but it also mean this operation is safe, right ?

but in case the pure dbcc checkdb and it reports error, what  should be the next checkdb command to run in single user mode?
0
 
Michael PfisterCommented:
This really depends on the problems it detects.
Microsft doesn't recommend to fix the database but instead restore from a good backup. I know this is not always possible.

I posted the link to MSDN above, but REPAIR_REBUILD should be the first option to try.

Note that dbcc checkdb will have some impact on the performance of your SQL Server!
0
 
marrowyungSenior Technical architecture (Data)Author Commented:
"Microsft doesn't recommend to fix the database but instead restore from a good backup. I know this is not always possible."

sorry, the backup can be corrupted as well, so sometime why should we relies on corrupted backup ?
0
 
marrowyungSenior Technical architecture (Data)Author Commented:
hi all,

our DBCC checkDB command return good result ,what is next then ?

CHECKDB found 0 allocation errors and 0 consistency errors in database

Open in new window

0
 
Michael PfisterCommented:
Check SQL server Service Pack and hotfix level
0
 
marrowyungSenior Technical architecture (Data)Author Commented:
nono, I know the version .taht one is SQL server 2008 with SP4, Win serve r2008 R2 .
0
 
Michael PfisterCommented:
Ok, good news for the database but bad news for finding the root cause of the problem.
Could be some SQL statement sent from the application that uses the database.
When does this happen? Any errors on the application side? Which application?

It probably worked some time before so the question is what has changed?
0
 
Nakul VachhrajaniTechnical Architect, Capgemini IndiaCommented:
It could still be a bad index (try rebuilding clustered indexes during one of the scheduled maintenance windows). Also, review instances of queries in the application code (app + stored procedures + ad-hoc queries) for use of WITH (NOLOCK) in UPDATE, INSERT or DELETE statements. Asking SQL Server to take higher-level locks in these operations has been known to cause index corruption.
0
 
marrowyungSenior Technical architecture (Data)Author Commented:
"Could be some SQL statement sent from the application that uses the database.
When does this happen? Any errors on the application side? Which application?"

today less message like that, can see later.

it keep happening during the last 2 weeks.

Nakul Vachhrajani,

"Asking SQL Server to take higher-level locks in these operations has been known to cause index corruption."

why is it related? the checkdb don't detect this ?

"Also, review instances of queries in the application code (app + stored procedures + ad-hoc queries) for use of WITH (NOLOCK) in UPDATE, INSERT or DELETE statements. Asking SQL Server to take higher-level locks in these operations has been known to cause index corruption."

it seems we need to double check much more query we build for that, nearly impossible.
0
 
marrowyungSenior Technical architecture (Data)Author Commented:
any more idea about this ?
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
Do you still having that error?
0
 
marrowyungSenior Technical architecture (Data)Author Commented:
as we restart 3 times and now the other error message, the same thing still here"


DESCRIPTION:      A user request from the session with SPID 317 generated a fatal exception. SQL Server is terminating this session. Contact Product Support Services with the dump produced in the log directory.
0
 
Michael PfisterCommented:
Since it terminates the session you even can't check with sp_who who is causing this ...
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
That's another error. Can you prove it's related to the previous one?
0
 
marrowyungSenior Technical architecture (Data)Author Commented:
mpfister,

we are using idera DM and the only error there is, can't connect to that DB.

Victor, I can't proof it. :):)
0
 
Michael PfisterCommented:
So it looks the idera DM is causing the error message? Never used it ...
I'd talk to their support. Or turn it off for  a day to check if your errors disappear.
0
 
marrowyungSenior Technical architecture (Data)Author Commented:
"So it looks the idera DM is causing the error message? Never used it ..."


no. i mean I read from DM..
0
 
Michael PfisterCommented:
But there has to be an error on the calling application side that causes this ....
0
 
marrowyungSenior Technical architecture (Data)Author Commented:
yes, but no other information right now.

what I can see it, just before that happen, what error log we have, I will focus on that.
0
 
marrowyungSenior Technical architecture (Data)Author Commented:
so you guy seldom experience this ?
0
 
marrowyungSenior Technical architecture (Data)Author Commented:
hi,

this error comes again:

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

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

Open in new window


before that, I just saw Idera Compliance manager is collecting trace file from that node.

 that node having this error message comes out is running SQL server 2008 with SP4, the other one also auditing by idera CM DO NOT haave problem is running SQL server 2008 with SP3.

I am wondering why ..
0
 
Nakul VachhrajaniTechnical Architect, Capgemini IndiaCommented:
Maybe Idera is not compatible with SP4. Can you open a support case with Idera to see if they support SP4?
0
 
marrowyungSenior Technical architecture (Data)Author Commented:
Nakul Vachhrajani,

already doing this at the same time, they are checking it.

mpfister,

"Or deactivate it  for a while "

deactivate CM ?
0
 
Michael PfisterCommented:
Yes, disable Idera Compliance manager for a while if possible.
0
 
marrowyungSenior Technical architecture (Data)Author Commented:
I send to them first and they reply with nothing now, let see.
0
 
marrowyungSenior Technical architecture (Data)Author Commented:
today is the first day we disable the CM on that SQL server box, so far no that message anymore.

will update you all.
0
 
marrowyungSenior Technical architecture (Data)Author Commented:
DESCRIPTION:    A user request from the session with SPID 207 generated a fatal exception. SQL Server is terminating this session. Contact Product Support Services with the dump produced in the log directory.

Is caused by CM is the audited SQL server is running SQL server 2008 with SP4 when DML audit is turned on
this is the same thing I think we found out and tell you all in other ticket:

I guess now we know why that box stop without any reason as we are setting up Idera compliance manager (CM) to audit a SQL 2008 server with SP4, it doesn't even happen to SP3 at all.

This one:


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

Is caused by CM is the audited SQL server is running SQL server 2008 with SP4 when Before and AFTER audit is on.
0
 
marrowyungSenior Technical architecture (Data)Author Commented:
tks all.
0
 
Michael PfisterCommented:
Glad it helped. Hope CM provides a workaround/fix for that.
0
 
marrowyungSenior Technical architecture (Data)Author Commented:
they started to know it. they said they are fixing it.


their product can't audit table under replication.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.