SELECT works, but UPDATE,INSERT,DELETE timeout

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

SELECT works, but UPDATE,INSERT,DELETE timeout

Author

Topic

joelbrda
Starting Member

5 Posts

Posted - 2005-09-26 : 10:40:36

I have an intranet app with about 30 or so people hitting it once or twice a day. It will work for days or weeks without any problem at all. Then it will timeout on any command that isn't a SELECT. I browse databases in Enterprise Manager, etc but anything other than SELECT just doesn't work. I've looked at all the error logs and tried profiling all commands, but can't seem to ever find what causes this. The only fix is to restart the SQL service. Has anybody had this problem?

SqlStar
Posting Yak Master

121 Posts

Posted - 2005-09-26 : 18:59:00

Hi,

I think problem is related to lock, connection and memory management. 1. Are you closing the opened connection every time?
2. You should kill/destroy the objects in your front-end applications.

Please check these with your application's code.

Rafiq
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
"You have to dream before your dreams can come true" -- President Of India

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-09-27 : 00:51:32

See if this helps you
http://vyaskn.tripod.com/watch_your_timeouts.htm

Madhivanan

Failing to plan is Planning to fail

sachinsamuel
Constraint Violating Yak Guru

383 Posts

Posted - 2005-09-27 : 00:59:27

I agree with joelbrda. The problem is in the application. In case this happens again, please check the locks in the database. Also check the events in event viewer for any unseen error.

Regards
Sachin

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2005-09-27 : 17:19:21

Did you look in sp_who2, sp_lock, and sysprocesses to see what the active processes are? You need to find out if you have locking and blocking.

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

joelbrda
Starting Member

5 Posts

Posted - 2005-09-27 : 17:36:42

So for the SP_lock what am I looking at?

56 4 0 0 DB S GRANT
57 4 0 0 DB S GRANT
58 5 0 0 DB S GRANT
61 5 0 0 DB S GRANT
62 5 0 0 DB S GRANT
63 5 0 0 DB S GRANT
64 5 0 0 DB S GRANT
66 5 0 0 DB S GRANT
67 5 0 0 DB S GRANT
67 1 85575343 0 TAB IS GRANT

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2005-09-27 : 17:42:17

All those are shared or intent shared locks. Look up locking in SQL Server Books Online. You really need to focus on if you have U, IX, SIX, or X leading up to failure point or you have exclusive locks at the time you're having issues. I'm assuming you know how to check for blocking already?

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

joelbrda
Starting Member

5 Posts

Posted - 2005-09-28 : 11:13:14

No, not really too much. If I set a Lock timeout, will that prevent blocking if a second cn is waiting for a first?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources