December 31, 2002 at 9:37 am
I am having some trouble with one of my servers. The problem is, its a development server and I am constantly getting blocked different spid's. All I am doing on this server are very simplistic queries however it seems like everytime I go to run one via query analyzer or the console I get blocked and the query won't complete till I kill the process that is blocking it.
Does anyone know what this is occurring and what I can do to get rid of it.
Any help would be great.
Happy New Year
December 31, 2002 at 9:41 am
You will need to identify what the blocking SPID is actually locking. Once this is identified, you will need to determine who change process to not lock required resource for blocked SPID.
Gregory Larsen, DBA
If you looking for SQL Server Examples check out my website at http://www.geocities.com/sqlserverexamples
Gregory A. Larsen, MVP
January 1, 2003 at 12:30 am
default for "autocommit" is on check if it is off for you.......
or may be some other batch had left an open transactions which is creating blocking
or
close all the query anylizer windows and reopen and fire the query...
Prakash
Prakash Heda
Lead DBA Team - www.sqlfeatures.com
Video sessions on Performance Tuning and SQL 2012 HA
January 1, 2003 at 11:02 am
Use profiler to monitor all server activiity to see what is running when you get blocked. Also, some coding habits can be a root cause such as running a SELECT INTO which can block on the tempDb but I forget the details at the moment, create your table then do INSERT instead.
January 3, 2003 at 12:04 am
Use dbcc traceon(1204; 3605; -1) to have sqlserver log deadlock-situations in the sqlserver-logs. This gives good info.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply