Getting Timeout Expired erros when updating a single table

Question

Getting Timeout Expired erros when updating a single table

roger.e.knoell

SSC Veteran

Points: 211
More actions
July 6, 2012 at 11:27 am

#277549

Help!
Have intermittent problem of timeouts when users are trying to update one table in database. other table updates are fine. problem could last for a moment to several hours. When the problem is occurring, not only are the users unable to update, but i can't even execute the update directly in SSMS via query or edit table interface. I receive the Timeout Expired error just like users do. Reboots sometimes help but not always so i don't think thats the answer. The timeout value for the database server is 600 seconds (under remote server connection section of server properties in SSMS). I have run sp_who2 and checked the sys.dm_tran_locks table and no locks are being held on this table for any extended amount of time.

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

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

Timeout is a client-side setting. The remote query timeout setting is for remote queries (across linked servers) and has no bearing at all on queries executed on that SQL Server.

The default timeout for a .Net connection is 30 seconds.

Have you checked for blocking? (the blocked process report may help)

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

roger.e.knoell SSC Veteran Points: 211 More actions · Answer 2

i was looking at sp_who2 for which process may be blocked or blocking... do you have another sugesstion

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

As I mentioned above, the blocked process report. Also, chapter 6: http://www.simple-talk.com/books/sql-books/troubleshooting-sql-server-a-guide-for-the-accidental-dba/

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

roger.e.knoell SSC Veteran Points: 211 More actions · Answer 4

If you mean the Reports - All Blocking Transactions from the database context report, no i did not run that during the anomolies. I will next time it occurs but as of right now its running.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

No I didn't.

http://msdn.microsoft.com/en-us/library/ms191168.aspx

http://msdn.microsoft.com/en-us/library/ms181150.aspx

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

ksrikanth77 SSCrazy Points: 2232 More actions · Answer 6

For checking the blocking you can also use when you get this timeout issue.

Select * from Sysprocess where Blocked >0

Thanks

S.R.Kundur