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.
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 ...
Any help is appreciated.
Thanks
|
SQL Locking up
denisJaubert
SQL Server parse and compile time:
CPU time = 7 ms, elapsed time = 7 ms.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
(1 row(s) affected)
Table 'Items'. Scan count 1, logical reads 3, physical reads 0, read-ahead reads 0.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
|--Clustered Index Update(OBJECT:([testdb].[dbo].[Items].[PK_Items]), SET:([Items].[Col1]=[Expr1004]), DEFINE:([Expr1004]=If ([Items].[col1]<>NULL) then [Items].[col1] else 0|2), WHERE:([Items].[col2]=1))
Jean-Michel Bezeau
You could run Performance Monitor to check Processor Time, Processor Queue Length, Disk Queue Length... etc. There are some scripts to check for blocking processes but since it is blocking updates on all tables this wouldn't help you much.
Profiler might give you more information about which batches are being executed against the database at that moment.
Long Tan
ash927
I checked. there are no transactions on that table. no foreign keys at all !!
but if i delete all the rows and then restart my services. the services start running fine again with no problems for hours together.
bigjefe
one other interesting thing i noticed is when i run this
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.
i ran this when the hanging was happening
mcpunjabi
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.
Kulwant
SET STATISTICS IO ON
SET STATISTICS TIME ON
delete from table1 where col=1
And also post the query plan here by using the next batch.
SET SHOWPLAN_TEXT ON
GO
delete from table1 where col=1
smileshalini
zoki977
This is a test environment so there is no backup done.
i just cleaned up the log. But one thing i want to know is. can this tlog increasing may be a cause for SQL locking up
Pavan526
Use sp_lock to get an overview of the locks. Maybe there are some transactions that aren't being committed. Are there many foreign keys linked to the table
Soulia
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
Makemagic
Ken Mcferren
im not sure if this is related . The ldf file for the database is toooooooooooo huge. it is like 8207 MB !!!
anyways i have taken care of that. i have set it to a max of 200mb and grow by 5 mb.
but any ideas on that if it could cause the problems im seeing
ginOMag
Maybe some other process is using up your CPU time at that moment