SQL server 2008 SP4

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

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

good ,sth like this but with other thinking.

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

the checkdb don't work for us man.

What do you mean? It returning error when you try to run it?

it don't but error message still coming.

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?

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

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

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 ?

I think we should have index rebuild and update statistic task for each DB on the server irrespective of the region.

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.

Okies...!!

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.

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 !

however, we are using SQL2008 SP4 ,it don't apply to us , right?

what is a  XQuery ?

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.

but it only for SQL server 2008 up do SP3, however our SP3 don't have this problem at all

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.

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

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.

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.

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.

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.

Victor, this is all I got from the log.

we will upgrade the SQL server to SQL2014 very soon, so let's see if it STILL happens.

tks all anyway.