update statistics failed

Question

update statistics failed

sqlfriend

SSC Guru

Points: 52794
More actions
January 14, 2013 at 10:38 am

#268712

I have a database maintenance plan to update statistics at night of weekend.
It looks it fails at updating the vcenter database
It failed with the following message:
Executing the query "UPDATE STATISTICS [dbo].[VPX_TEMPTABLE1]
WITH FU..." failed with the following error: "Transaction (Process ID 103) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
Any clues why this happen?

Viewing 6 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Grant Fritchey SSC Guru Points: 399289 More actions · Answer 1

It was a deadlock victim. So while it was trying to modify data, other processes came and caused a deadlock. You should just be able to rerun it. It's pretty rare to see deadlocks at this level though. You're not running all the stats updates as a single transaction are you?

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

sqlfriend SSC Guru Points: 52794 More actions · Answer 2

Thanks, it is using the update statistics task item in maintenance plan.

The View T-SQL, part of it is like:

UPDATE STATISTICS [dbo].[VPX_TEMPTABLE0]

WITH FULLSCAN

GO

use [vCenterDB2]

GO

UPDATE STATISTICS [dbo].[VPX_TEMPTABLE1] WITH FULLSCAN

GO

use [vCenterDB2]

GO

UPDATE STATISTICS [dbo].[VPX_TEMPTABLE2]

WITH FULLSCAN

GO

use [vCenterDB2]

GO

SQLRNNR SSC Guru Points: 281344 More actions · Answer 3

I have seen vcenter do this on quite a few occassions. IIRC there was a patch for it.

I would also only do these during offhours with that database.

Please provide the stats update code being used.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

sqlfriend SSC Guru Points: 52794 More actions · Answer 4

SQLRNNR (1/14/2013)
I have seen vcenter do this on quite a few occassions. IIRC there was a patch for it.
I would also only do these during offhours with that database.
Please provide the stats update code being used.

Thanks much, is there any article about this?

Also when you say: provide the stats update code being used, I am not sure what it means?

Is the my previous post of update statistics statement the code?

Thanks much

SQLRNNR SSC Guru Points: 281344 More actions · Answer 5

That was a snippet. Are you using a job that generates code and then executes that or do you have an entire scripted out set of commands that you run?

Whichever it is, provide it so we can determine other answers (like Grants question about the single transaction)

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events