Solved

SQL server 2008 SP4

Posted on 2016-11-13
29
35 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 18

Accepted Solution

by:
Pawan Kumar Khowal 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 45

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

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 45

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 45

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 18

Expert Comment

by:Pawan Kumar Khowal
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 18

Expert Comment

by:Pawan Kumar Khowal
ID: 41889165
Okies...!!
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
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 18

Expert Comment

by:Pawan Kumar Khowal
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 18

Expert Comment

by:Pawan Kumar Khowal
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 45

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 45

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 45

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 45

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

Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

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…
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

743 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

11 Experts available now in Live!

Get 1:1 Help Now