Timeout errors when deleting, even selecting data

Question

Post reply

Timeout errors when deleting, even selecting data

chrisph

Hall of Fame

Points: 3701
More actions
July 5, 2010 at 2:11 pm

#222892

Hi all,
One large database (~25GB) is having issues deleting data (one row in one small table), it gives a timeout error, and just now when I did a select all statement on that one small table I received another timeout error.
Nothing in the logs points to anything.
Tried restarting the SQL Server service thinking it could be a lock, no luck.
Thoughts anyone?

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

chrisph Hall of Fame Points: 3701 More actions · Answer 1

The particular table we are testing this on has no foreign keys, just one primary key and 19 rows with 4 columns.

I am at a loss why the delete statement is timing out.

Please help.

Leo.Miller SSChampion Points: 13051 More actions · Answer 2

After starting the select or delete have you done an sp_who2 from another MS conection to see if there is any blocking?

This is the most likely cause.

Leo

Leo
Nothing in life is ever so complicated that with a little work it can't be made more complicated.

chrisph Hall of Fame Points: 3701 More actions · Answer 3

Leo what should I see out of the ordinary when I run exec sp_who2?

David.Poole SSC Guru Points: 76035 More actions · Answer 4

sp_who2 should give you details as to who is accessing your SQL Server.

I've found that SELECT * FROM sysprocesses WHERE Blocked<>0 is also quite revealing. Blocked is the SPID number that is blocked so it is quite easy to work out who is blocking what.

LinkedIn Profile

chrisph Hall of Fame Points: 3701 More actions · Answer 5

Thanks guys, I learned something valuable from your comments.

In actuality it was the database design, the table had many foreign keys attached to it. I didn't design the database so the guy doing it is going to create indexes etc.

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 6

You posted some confusing stuffs

chrisph (7/5/2010)
The particular table we are testing this on has no foreign keys

chrisph (7/5/2010)
In actuality it was the database design, the table had many foreign keys attached to it.

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

balaji.ganga SSCrazy Points: 2280 More actions · Answer 7

Hi,

Check it if any blocks is availabe or not?

check it if any open transactions or not?

Check it if any fragmentation occurred or not?

Once you checked those things, you may get a solution..

With Cheers

Balaji.G

chrisph Hall of Fame Points: 3701 More actions · Answer 8

Bhuvnesh (7/5/2010)
You posted some confusing stuffs
chrisph (7/5/2010)
The particular table we are testing this on has no foreign keys
chrisph (7/5/2010)
In actuality it was the database design, the table had many foreign keys attached to it.

At first I thought the table had no foreign keys, I was mistaken, it had several attached to its primary key from other tables. The user who added tables informed me later, and after I did a db diagram of all tables saw all the links. Is there a better way to query this pk/fk information?

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 9

select object_name(constid),object_name(id),* from sys.sysconstraints

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)