MS SQL 2005 timesout

[rrajarat]

I have a small (~90MB) database that my production is recorded on and every 5 minutes this info is put into a txt file and sent via FTP to another offsite Server. Then the record that have been sent get flagged. The last couple of days my server timesout on the update and after trying the update a few time the whole DB becomes unresponsive and my production doesn't get recorded (leading more problems). My solution that last 2 days has been to reboot the server and try the update in a few hours.

What could be causing this and what should I do?

 

[mrdenny]

You've got some blocking going on most likley. You can use sp_who2 to identify which processes are blocking. Once you've identified your code which is causing the problem you'll want to update the code so that the blocking isn't being caused.

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[rrajarat]

thanks for the reply mrdenny. What is sp_who2 and how do I use it? Also how do analyze the information I get back?

 

[mrdenny]

sp_who2 is a system stored procedure that can be run in Query Analyzer. It shows basic information for every connection on the SQL Server including what process is blocking which other processes.

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[Catadmin]

All you do is open up a QA window and literally type sp_who2. The results are fairly easy to read and there's a column that lists the blocking.

RUn DBCC InputBuffer(spid) to find out what the blocking process is doing. Just replace the word spid with the approriate Process ID.



Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"

 

[rrajarat]

I tried sp_who2 and DBCC InputBuffer() and found that the processor that are blocking each other don't even access the same table. Strange thing is this happened yesterday when I was out of the building and when I got in in 15 min nothing was accessing the DB (lunch time so no production). I had stopped the program that was tranfering the data about 2 hours ago so it was not cuasing the locks. i ran sp_who2 and no process' were blocked but when I went to the floor and tried to enter production (does an insert command) the shop floor program couldn't access the DB. I then ran sp_who2 again and nothing was blocked. So I restarted the server and evrything was ok again.

Now I am not sure if this is a blocking/Locking issue.

 

[mrdenny]

If nothing is showing blocking than there isn't any blocking. What's the waittype's that are listed for the stalled spids.

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[rrajarat]

mrdenny,

This issue happened again last night and this time there were only 2 apps running that would access the DB. They are both used to record production, so there will be an insert from 2 PCs (same app on both) every minute (this is average). Again I wasn't in the office to run some diagnostics but I am thinking that there might be a network/comunication issue.

 

[rrajarat]

Well I don't know if this is progress but I was able to get my shopfloor PCs to react the way they do when the issues occur (whatever they may be). I simply took my server of the network and predictably the vb apps accessing the DB were very slow.

Now my question is can a networking delay/issue cause timeouts for the client and cause the DB (sql server) to not respond? DB gets tied up trying to send success/failure messages to the client. Can an ADO connection that gets opened but doesn't get closed cause DB issues or does SQL Server just drop these connections after some time?

 

[mrdenny]

SQL should just drop the connection when it goes away.

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com