Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Troubleshooting tied up resources 1

Status
Not open for further replies.
LonnieJohnson

LonnieJohnson

Programmer
Apr 16, 2001
2,628
US
I need some direction. We are using sql server 2005. Occasionally our applications that try and communicate with a database experience time out errors. This affects all web apps and windows based apps. We try to the Activity Manager in the Management node of Enterprise Manager to see what can be tying up resources and never see anything. Our only recourse is to restart the server. That of course kicks all users out of all applications on all databases. It used to happen rarely now it is happening about once a week.


ANY HELP OR DIRECTION WOULD BE APPRECIATED!


ProDev, Builders of Affordable Software Applications
Visit me at ==>
May God bless you beyond your imagination!!!
 
Sort by date Sort by votes
Sounds to me like you may be running in to a lock issue. Basically, multiple computers try to access the same data at the same time. The next time this happens, open a query window and run [!]sp_who2[/!]. You should see a bunch of rows and columns. The first column is SPID (the process ID). Any SPID less than 50 belongs to the SQL Server database engine. Any SPID greater than 50 is a user connection. There should be a column for BlkBy. Check to see if there are any rows that have data in the BlkBy column. If there are, you have a blocking situation.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom
 
Upvote 0 Downvote
Thanks for the quick response. We will definately try it.

ProDev, Builders of Affordable Software Applications
Visit me at ==>
May God bless you beyond your imagination!!!
 
Upvote 0 Downvote
Nice. This is like the Activity Monitor with a little extra info. Thanks.

ProDev, Builders of Affordable Software Applications
Visit me at ==>
May God bless you beyond your imagination!!!
 
Upvote 0 Downvote
I forgot to mention that you can get the query that was issued. Once you see what the SPID is that is blocking, you can run:

DBCC INPUTBUFFER(###)

You would put the SPID in place of the ###.


-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom
 
Upvote 0 Downvote
Thanks. That sounds like it is a significant part of the process.

:-D


ProDev, Builders of Affordable Software Applications
Visit me at ==>
May God bless you beyond your imagination!!!
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

bharons
  • Locked
  • Question
Pick up value from combobox
Replies
0
Views
265
bharons
bharons
Cpreston
  • Locked
  • Question
Update with a month name 1
Replies
9
Views
380
gmmastros
gmmastros
leifoet
  • Locked
  • Question
How can I update the input of a form (rows and columns) to the database in one query ?
Replies
1
Views
151
Chris Miller
Chris Miller
TheBugSlayer
  • Locked
  • Question
Transaction with SELECT and UPDATE with XLOCK causing deadlocks 2
Replies
10
Views
359
gmmastros
gmmastros
Cpreston
  • Locked
  • Question
Update another column if text in another exists 2
Replies
4
Views
110
Cpreston
Cpreston

Part and Inventory Search

Sponsor

Back
Top