SQL Server Performance Forum – Threads Archive
sql locking up
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 …
If i let the deletion from query analyzer complete and then restart the services then everything is fine again !!
Any help is appreciated. Thanks
Run Profiler to trace all work, and find long queries or sp. May you have to optimize those queries.
Luis Martin
Moderator
SQL-Server-Performance.com Although nature commences with reason and ends in experience it is necessary for us to do the opposite, that is to commence with experience and from this to proceed to investigate the reason.
Leonardo Da Vinci Nunca esperes el reconocimiento de tus hijos, eso ocurrirá luego de tu muerte
All postings are provided “AS IS†with no warranties for accuracy.
Do you have an autoshrink set on your db? Do you have any maintenance jobs scheduled? What are db settings regarding initial size and increments both on data and log files?
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
Well I don’t recomend to all user login with sa. Who will be responsable for any problem? No maintainance plan is wrong. You have at least planing for: Integrity, Optimization, Index Rebuild and Update Statistic even when you have auto update statistics on.
Luis Martin
Moderator
SQL-Server-Performance.com Although nature commences with reason and ends in experience it is necessary for us to do the opposite, that is to commence with experience and from this to proceed to investigate the reason.
Leonardo Da Vinci Nunca esperes el reconocimiento de tus hijos, eso ocurrirá luego de tu muerte
All postings are provided “AS IS†with no warranties for accuracy.
quote:Originally posted by dbuser123
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
What about autoshrink option, is it on? Based on info you provided it looks like data file was expanded recently. What are log file(s) settings? Try to add another 3 GB file in default file group (probably primary) and follow-up how long it will run without problems this time. I guess problem might be caused by data file and/or log file growth.
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
Can you also check the activity on TEMPDB while running the resource intensive queries? Satya SKJ
Contributing Editor & Forums Moderator
http://www.SQL-Server-Performance.Com
This posting is provided “AS IS†with no rights for the sake of knowledge sharing.
No TempDB activity.
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.
i ran this when the hanging was happening
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.
You can run profiler to find out locks.
Luis Martin
Moderator
SQL-Server-Performance.com Although nature commences with reason and ends in experience it is necessary for us to do the opposite, that is to commence with experience and from this to proceed to investigate the reason.
Leonardo Da Vinci Nunca esperes el reconocimiento de tus hijos, eso ocurrirá luego de tu muerte
All postings are provided “AS IS†with no warranties for accuracy.
i think im seeing a problem. The ldf file for the database is toooooooooooo huge. it is like 8207 MB !!!
Im sure i need to fix that. But can that be causing the problems im having ?
Hi,
may suggest to read article on Vyas’s site http://vyaskn.tripod.com/watch_your_timeouts.htm
and search other relavent threads in the forum Regards Hemantgiri S. Goswami
[email protected]
"Humans don’t have Caliber to PASS TIME , Time it self Pass or Fail Humans" – by Hemantgiri S. Goswami
The problem could be there is no schedule to backup the transaction log or using too much of bulk inserts. 8GB of transaction log is not big enough to worry and you may need to only if there are any disk space concerns. In any case if you can maintain the schedule of trnasaction log then the size will be taken care of, also consider the other scheduled jobs and bulk insert jobs that can contribute size increase in Tlog. For information check thishttp://www.sql-server-performance.com/absolutenm/templates/?a=260&z=1 blog.
quote:Originally posted by dbuser123
Im sure i need to fix that. But can that be causing the problems im having ?
Satya SKJ
Contributing Editor & Forums Moderator
http://www.SQL-Server-Performance.Com
This posting is provided “AS IS†with no rights for the sake of knowledge sharing.
What I want to know is if the transaction log was causing the problem ??
one thing to add is all my SPs create a lot of temp databases for local processing..
Check the disk contention during Transaction log or user database backup.
Use PERFMON to capture disk related counters for further assessment. It is an usual behaviour all your queries using temp database to process and make sure there is no blocking is happened. You can take help of blocker script or use this KBAhttp://support.microsoft.com/default.aspx?scid=kb;en-us;271509 for further investigations.
quote:Originally posted by dbuser123
What I want to know is if the transaction log was causing the problem ??
Satya SKJ
Contributing Editor & Forums Moderator
http://www.SQL-Server-Performance.Com
This posting is provided “AS IS†with no rights for the sake of knowledge sharing.
What recovery model do you have?
Also what kind of backup are you running?
Luis Martin
Moderator
SQL-Server-Performance.com Although nature commences with reason and ends in experience it is necessary for us to do the opposite, that is to commence with experience and from this to proceed to investigate the reason.
Leonardo Da Vinci Nunca esperes el reconocimiento de tus hijos, eso ocurrirá luego de tu muerte
All postings are provided “AS IS†with no warranties for accuracy.
This is an internal stress test server. The recovery model was set to Full.
I think the main problem was that the log file was autogrowing at 10% unlimited growth !! for past 2 days it looks like it is running fine. i have now set the max to 300 mb growth. obviously i expect customers to have a backup plan..
If recovery is full and log is to big, I suppose the backup plan is daily and no transaction log backup each, said 1 hour, at all. Luis Martin
Moderator
SQL-Server-Performance.com Although nature commences with reason and ends in experience it is necessary for us to do the opposite, that is to commence with experience and from this to proceed to investigate the reason.
Leonardo Da Vinci Nunca esperes el reconocimiento de tus hijos, eso ocurrirá luego de tu muerte
All postings are provided “AS IS†with no warranties for accuracy.
]]>