| Author |
Topic |
|
rtutus
Aged Yak Warrior
522 Posts |
 Posted - 2007-10-09 : 11:51:55
|
| Hi,the SQL DB freezes and no one can access the DB for 5 or 10 minutes. Even select queries don t execute, nothing is displayed.Until we kill the process id that s blocking in the sql activity monitor, only then the DB is released and people can work again.What does it mean that no query executes until we kill the processes ID? what could it be?Also, recently we created indexes and ran tuning adviser, is it possible that the creation of indexes cause the freeze of a DB? is that possible?Thanks a lot for your help |
|
|
Kristen
Test
22859 Posts |
Posted - 2007-10-09 : 12:06:01
|
Is the database bigger than, say, 10GB and has a file extension set to the default of 10% - rather than a fixed size extension of, say, 50MB ?If so, under SQL 2000, that could be causing serious timeouts when the database is extended.Although I would have expected pretty bad things to happen if you killed the SPID that was doing the extension!Might be worth trying to find out what the SPID is doing next time  Kristen |
 |
|
|
rtutus
Aged Yak Warrior
522 Posts |
Posted - 2007-10-09 : 12:29:36
|
quote:
Originally posted by Kristen
Is the database bigger than, say, 10GB and has a file extension set to the default of 10% - rather than a fixed size extension of, say, 50MB ?
No it s only 2G or 3Gquote:
Might be worth trying to find out what the SPID is doing next time Kristen
u mean by doingDBCC InetBUFFER spid or by some other means |
|
|
|
Kristen
Test
22859 Posts |
|
|
rtutus
Aged Yak Warrior
522 Posts |
Posted - 2007-10-09 : 14:28:01
|
| I m not sure if they can run the sp, it s pharmaceutical, and they re very not risky, it ll be hard to convince them install something for now.Anyway, I already can see the blocking process in Actvity monitor, it says number 59, when I display the properties it shows: sp_prepexec;1what s that mean and also: is there a way to know if the command runs as part of a transaction?Thanks a lot. |
|
|
|
Van
Constraint Violating Yak Guru
462 Posts |
Posted - 2007-10-09 : 14:36:34
|
| Try doing a dbcc inputbuffer(59) and see if you can figure out what it's doing... |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-10-09 : 14:39:14
|
| "what s that mean "What, you mean "sp_prepexec;1"? I have absolutely no idea. Could be anything."they re very not risky"I see it differently. Either the DBA is trusted, or not. If not then "game over", basically.If "trusted" then its down to the DBA to make sure that they don't run any risky code, and to know what they are doing. You can read the Sproc and decide for yourself what its doing, and what the risk is.Failing that use SQL Profiler, but you'll have to run it continuously until after the next slow down has finished ...Kristen |
|
|
|
rtutus
Aged Yak Warrior
522 Posts |
Posted - 2007-10-09 : 14:45:23
|
| sp_prepexec;1I think is the prepare statement that we put to prepare for a command, like in old VB6, we prepare the command before we run it.It s displayed on activity monitor, and i wanna know how to see if it s run as part of a transaction.P.S: good point Kristen, basically there is no DBA here. There s senior programmer who s taking charge of the issue, but i don t think he s lot of experience in DBs, so basically there is no DBA, and m just a contract junior programmer who s trying to burn his brain with much understanding :) :). But u guys helped me a lot learn stuff i ve never heard of b4. Muchas Gracias. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-10-09 : 15:07:11
|
| "I think is the prepare statement"Sure. Preparing what? Yeah, I've got no idea either.Stick SQL Profiler or Nigel's Sproc on it, then we'll know.We've got better things to do than guess. Divvy up some decent analytical data and I'm sure folk will help, but I ain't going to try if you can't do that.Kristen |
|
|
|
rtutus
Aged Yak Warrior
522 Posts |
Posted - 2007-10-10 : 08:48:07
|
| m totally with u Kristen,Sorry about that. thanks a lot. They found out the screen of the application that causes the pb. |
|
|
|
|
the DB freezes for 5 or 10 minutes