Solved

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

Posted on 2016-07-19
45
157 Views
Last Modified: 2016-09-13
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."
0
Comment
Question by:marrowyung
  • 23
  • 17
  • 3
  • +1
45 Comments
 
LVL 28

Expert Comment

by:Michael Pfister
ID: 41720261
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
 
LVL 28

Expert Comment

by:Michael Pfister
ID: 41720265
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
 
LVL 1

Author Comment

by:marrowyung
ID: 41720277
it is SQL 2008 with SP4.
0
 
LVL 11

Expert Comment

by:Nakul Vachhrajani
ID: 41721003
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
 
LVL 1

Author Comment

by:marrowyung
ID: 41737061
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
 
LVL 28

Expert Comment

by:Michael Pfister
ID: 41737079
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
 
LVL 1

Author Comment

by:marrowyung
ID: 41737203
hi,

I found this one also, I am ok with it but just concern about the whole statement.
0
 
LVL 28

Expert Comment

by:Michael Pfister
ID: 41737249
0
 
LVL 28

Expert Comment

by:Michael Pfister
ID: 41737252
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
 
LVL 1

Author Comment

by:marrowyung
ID: 41738749
so dbcc checkDB and it should only check the DB first anyway and what else I should do ?
0
 
LVL 28

Expert Comment

by:Michael Pfister
ID: 41738793
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
 
LVL 1

Author Comment

by:marrowyung
ID: 41740082
I forgot if it will fix it for us too or just report ?
0
 
LVL 28

Expert Comment

by:Michael Pfister
ID: 41740198
just Report. For fixing you need to put in single user mode.
0
 
LVL 1

Author Comment

by:marrowyung
ID: 41740286
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
 
LVL 28

Expert Comment

by:Michael Pfister
ID: 41740334
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
 
LVL 1

Author Comment

by:marrowyung
ID: 41742191
"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
 
LVL 1

Author Comment

by:marrowyung
ID: 41746716
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
 
LVL 28

Expert Comment

by:Michael Pfister
ID: 41746828
Check SQL server Service Pack and hotfix level
0
 
LVL 28

Expert Comment

by:Michael Pfister
ID: 41746830
0
 
LVL 1

Author Comment

by:marrowyung
ID: 41746841
nono, I know the version .taht one is SQL server 2008 with SP4, Win serve r2008 R2 .
0
 
LVL 28

Expert Comment

by:Michael Pfister
ID: 41747353
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
 
LVL 11

Expert Comment

by:Nakul Vachhrajani
ID: 41748317
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
Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

 
LVL 1

Author Comment

by:marrowyung
ID: 41748520
"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
 
LVL 1

Author Comment

by:marrowyung
ID: 41762227
any more idea about this ?
0
 
LVL 45

Expert Comment

by:Vitor Montalvão
ID: 41766590
Do you still having that error?
0
 
LVL 1

Author Comment

by:marrowyung
ID: 41774483
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
 
LVL 28

Expert Comment

by:Michael Pfister
ID: 41774541
Since it terminates the session you even can't check with sp_who who is causing this ...
0
 
LVL 45

Assisted Solution

by:Vitor Montalvão
Vitor Montalvão earned 125 total points
ID: 41774550
That's another error. Can you prove it's related to the previous one?
0
 
LVL 1

Author Comment

by:marrowyung
ID: 41775672
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
 
LVL 28

Expert Comment

by:Michael Pfister
ID: 41775872
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
 
LVL 1

Author Comment

by:marrowyung
ID: 41776077
"So it looks the idera DM is causing the error message? Never used it ..."


no. i mean I read from DM..
0
 
LVL 28

Expert Comment

by:Michael Pfister
ID: 41783673
But there has to be an error on the calling application side that causes this ....
0
 
LVL 1

Author Comment

by:marrowyung
ID: 41784142
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
 
LVL 1

Author Comment

by:marrowyung
ID: 41784145
so you guy seldom experience this ?
0
 
LVL 1

Author Comment

by:marrowyung
ID: 41784197
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
 
LVL 11

Assisted Solution

by:Nakul Vachhrajani
Nakul Vachhrajani earned 125 total points
ID: 41784247
Maybe Idera is not compatible with SP4. Can you open a support case with Idera to see if they support SP4?
0
 
LVL 28

Accepted Solution

by:
Michael Pfister earned 250 total points
ID: 41784262
Or deactivate it  for a while and see if the error goes  away. You can check the SQL server log directly
0
 
LVL 1

Author Comment

by:marrowyung
ID: 41784520
Nakul Vachhrajani,

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

mpfister,

"Or deactivate it  for a while "

deactivate CM ?
0
 
LVL 28

Expert Comment

by:Michael Pfister
ID: 41784523
Yes, disable Idera Compliance manager for a while if possible.
0
 
LVL 1

Author Comment

by:marrowyung
ID: 41785662
I send to them first and they reply with nothing now, let see.
0
 
LVL 1

Author Comment

by:marrowyung
ID: 41787507
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
 
LVL 1

Author Comment

by:marrowyung
ID: 41793678
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
 
LVL 1

Author Closing Comment

by:marrowyung
ID: 41793690
tks all.
0
 
LVL 28

Expert Comment

by:Michael Pfister
ID: 41793853
Glad it helped. Hope CM provides a workaround/fix for that.
0
 
LVL 1

Author Comment

by:marrowyung
ID: 41795612
they started to know it. they said they are fixing it.


their product can't audit table under replication.
0

Featured Post

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

759 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now