SQL Locking up - SQL Server Forums

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)

SQL Locking up

Author

Topic

dbuser123
Starting Member

20 Posts

Posted - 2005-12-25 : 10:54:44

My ADO based C++ application works fine for sometimes 15+hrs just fine. Im using SQL 2000. All of a sudden I dont know what happens all update operatins and insert operations start failing with 80040e31, timeout expired errors.
It seems as though the whole database is locked up.

And after 1hr or sometime 2 hrs everything is back to normal without any intervention.

if i intervene and i stop all my app services. i run sp_who and there are no connections to the database. I restart my application services and still have those 80040e31 errors come up.

Any ideas ?

Any help is appreciated.

Thanks

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-12-25 : 11:15:32

one possibility is an explicit transaction when wrong and never committed or rolled back. When the problem is occurring try running:
dbcc opentran
to see if that could be the cause.

Be One with the Optimizer
TG

dbuser123
Starting Member

20 Posts

Posted - 2005-12-25 : 11:29:36

Let us assume that one of the transactions failed to rollback/commit
but still the connection is closed.
not only that if the transaction is specific to a particular set of tables, the app fails to also update totally unrelated tables...

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-12-25 : 12:08:15

>>but still the connection is closed.
this could still be because open transactions

>>app fails to also update totally unrelated tables
This probably means it's not because of open transactions

Doesn't hurt to run dbcc opentran just to eliminate that as a possiblility

When the problem is occuring, can you perform DB operations using query analyzer?

Be One with the Optimizer
TG

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-12-25 : 12:17:58

Some other thoughts:

Start a sql profiler trace while the problem is occuring to confirm your calls are even reaching the server.
Check the server logs and event viewer.
Have you run sp_who2 active while the problem is occuring?
Is some db admin process happening that is unrelated to you application?

Be One with the Optimizer
TG

dbuser123
Starting Member

20 Posts

Posted - 2005-12-25 : 12:18:19

yes I forgot to add this. From the query analyzer im able to perform operations. like for example
there is a table with just 8 rows.

if i do a
delete from table1 where col=1
it takes 1.30 minutes to delete 8 rows...

Actually this is the problem. so an update also i think takes more than 30s and that is why it times out.
so probably if i increase the timeout to 3 minutes everything will be fine. I dont want to do that. rather i want to find out why the updtes are timing out ...

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-12-25 : 12:29:47

yeah, a 1 min 30 sec is a little too long to delete 8 rows. While that delete is running, check sp_who2 active to see if the delete is being blocked and what else is happening. If nothing else is going on (and that table isn't referenced as a foriegn key to other big tables) maybe the server processer, or disk activity might be maxed out. Is the sql server dedicated as just being the sql server? Is there a job processing on it?

Be One with the Optimizer
TG

dbuser123
Starting Member

20 Posts

Posted - 2005-12-25 : 13:40:03

Actually nothing much is happening on the server at the time when the deletion is being done.

dbuser123
Starting Member

20 Posts

Posted - 2005-12-27 : 07:45:46

one interesting thing i noticed. Update/Insert is locked for all the tables in that database at that time. But once i run an update query in the query analyzer and let it run to finish and it takes like 1.30 minutes then the whole database seems to reset and every other update works fine. after that all my app services are back to normal without even restarting them , because all the updates work fine !!
i dont know if it makes a difference but all my queries use username=sa.

database size: 7030MB
available space:693MB
no maintainance plan
it is set to automatically grow file by 10%
Auto Update statistics set
Torn Page set
auto create stats set

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-12-27 : 08:01:19

>>Have you run sp_who2 active while the problem is occuring?
>>While that delete is running, check sp_who2 active to see if the delete is being blocked and what else is happening
Still haven't heard the results of this...specifically look at the BlkBy column for the spid that's taking a long time.

couple other questions:
are you auditing changes to tables via triggers? (do you use a single table for all audits?)
are you altering any sql objects in your insert/update SPs?
are you using SPs or are you using sql statements generated by your application? if application, are you also controlling those transactions from your application?

EDIT:
also didn't hear results for:
>>When the problem is occurring try running: dbcc opentran
(just to eliminate this as a culprit)

Be One with the Optimizer
TG

dbuser123
Starting Member

20 Posts

Posted - 2005-12-27 : 08:23:32

>>Have you run sp_who2 active while the problem is occuring?
>>While that delete is running, check sp_who2 active to see if the delete
nothing unusual here...

couple other questions:
are you auditing changes to tables via triggers? (do you use a single table for all audits?)
are you altering any sql objects in your insert/update SPs?
are you using SPs or are you using sql statements generated by your application? if application, are you also controlling those transactions from your application?

no alerts/audits. i use SPs but call begintran/committran from the application.

EDIT:
also didn't hear results for:
>>When the problem is occurring try running: dbcc opentran
(just to eliminate this as a culprit)

ran it no open trans at that time

dbuser123
Starting Member

20 Posts

Posted - 2005-12-28 : 15:49:08

As I mentioned before when this was happening
as soon as i ran an update command and let it finish then everything was back to normal.
one thing i noticed is when i run dbcc opentran i get this

one other interesting thing i noticed is when i run some error...

dbcc opentran

Server: Msg 7969, Level 16, State 2, Line 1
No active open transactions.
Transaction information for database 'TestDB'.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

dbuser123
Starting Member

20 Posts

Posted - 2005-12-28 : 16:06:07

when i run sp_who2 when running the update command it display this. i have removed any irrelevant things...

The only thing i noticed is 71 is blocking the appservice update commands and that is fine . but what i dont understand is why that simple command of one row update should take 2 minutes and then the services are back to normal !!

51 RUNNABLE domain\user SQLSERV . TestDB SELECT INTO 516 85 12/28 07:11:09 SQL Query Analyzer 51
53 sleeping domain\user SQLSERV . TestDB AWAITING COMMAND 0 0 12/28 07:12:13 SQL Query Analyzer 53
54 sleeping sa APPSERVER 71 TestDB UPDATE 0 0 12/28 07:12:25 AppService1 54
56 sleeping sa APPSERVER . TestDB AWAITING COMMAND 0 0 12/28 07:12:37 AppService1 56
63 sleeping sa APPSERVER . TestDB AWAITING COMMAND 15 0 12/28 07:12:36 AppService1 63
69 sleeping sa APPSERVER . TestDB AWAITING COMMAND 0 0 12/28 07:12:31 AppService1 69
71 RUNNABLE domain\user SQLSERV . TestDB UPDATE 390 46 12/28 06:49:32 SQL Query Analyzer 71
75 sleeping sa APPSERVER 71 TestDB UPDATE 0 0 12/28 07:12:31 AppService275
76 sleeping domain\user SQLSERV . TestDB AWAITING COMMAND 47 6 12/27 07:09:50 SQL Query Analyzer 76
77 sleeping sa APPSERVER . TestDB AWAITING COMMAND 15 0 12/28 07:12:38 AppService277
80 sleeping domain\user SQLSERV . master AWAITING COMMAND 861 117 12/27 16:15:16 MS SQLEM 80
101 sleeping domain\user SQLSERV . TestDB AWAITING COMMAND 16 9 12/27 07:13:38 SQL Query Analyzer 101
102 sleeping domain\user SQLSERV . TestDB AWAITING COMMAND 32 0 12/28 06:41:27 SQL Query Analyzer 102

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-12-28 : 16:17:28

one possible red flag to me is your statement:
>>i use SPs but call begintran/committran from the application.

I prefer to keep all transaction control in the database. But whether it has to do with transactions or not:

Is there anyway you can make the problem happen with your application/service shut down? Perhaps capture all database calls for a period of time and then script them to run from concurrent connections (on the database). That would take the application out of the picture. If the problem happened then you know its something within the database.

Be One with the Optimizer
TG

dbuser123
Starting Member

20 Posts

Posted - 2005-12-28 : 16:32:24

Any idea what this is ?

dbcc opentran

Server: Msg 7969, Level 16, State 2, Line 1

Regarding running scripts i dont know if i can do that there are too many calls done. can i somehow may be use the profiler to run it by capturing the data there ?

dbuser123
Starting Member

20 Posts

Posted - 2005-12-28 : 16:37:54

Is there any kind of SQL logging i can setup to see if something unusual is happening at that time. it looks like there are no locks held but it is something on SQL which is blocking any and every update statement to run and it blocks it for 2 minutes +
and it is table independent and is database wide.
other databases on the same server are fine.
one other thing is let us say i run the upate statement itself in a begin tran/rollback tran even then everything is reset and app services start working fine. but if i stop the statement before it is completed everything is locked up...

dbuser123
Starting Member

20 Posts

Posted - 2005-12-28 : 17:37:59

i think im seeing a problem. The ldf file for the database is toooooooooooo huge. it is like 8207 MB !!!

dbuser123
Starting Member

20 Posts

Posted - 2005-12-30 : 08:31:27

TG , you think this can be the problem ? for past 2 days it seems to working fine as i changed the max for the log to 300MB

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-12-30 : 08:52:39

>>TG , you think this can be the problem ?
Yes.

If this is a development environment, I would think your recovery model should be Simple. The simple recovery model reclaims log space as needed to keep the log small. If this is for a production environment then you really need to put some consideration into your recovery model, backup plans, and all related db maintanence. (plenty of info about it in Books Online)

Good luck, but it sounds like you've got it figured out.

Be One with the Optimizer
TG

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources