SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Blocking SPIDS


Blocking SPIDS

Author
Message
vik_steve
vik_steve
SSC Rookie
SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)

Group: General Forum Members
Points: 28 Visits: 10
I have a SQL2K database that is having issues with SPIDS. I have backed up the db, detached it, removed tempdb so it is rebuilt etc, but as soon as I bring the db back on-line the blocking spids re-appear (they are 50+ so are not system connections). I try killing each process in turn but then new ones appear.

This seems to be affecting our php website as it cannot create a connection to it.

Any ideas?
Gianluca Sartori
Gianluca Sartori
SSC-Insane
SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)

Group: General Forum Members
Points: 24401 Visits: 13362
What's the command text for these processes? What's the host originating the process: the same as the SQL Server or a different machine?

--Gianluca Sartori

How to post T-SQL questions
spaghettidba.com
@spaghettidba
Adi Cohn
Adi Cohn
SSCrazy Eights
SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)

Group: General Forum Members
Points: 8331 Visits: 6595
You should try and find out the cause of the blocking and not recreate the TempDB or do something else with no connection to the blocking problem that you have. According to what you wrote there is a good chance that you have blocking chain (or maybe few blocking chains). A blocking chain is where each process is blocking a different process. For example Proccess A is blocking Process B. Process B is blocking Process C which is blocking process D. When we have a blocking chain, the most interesting process is the head of the blocking chain – that is the process that started the chain. In the example that I wrote this is process A. There is a good chance that if I'll solve the problem that caused Process A to block Process B, then all the blocking chain will be released. In order to find the head of the blocking chain you can use this SQL Statement:


select Blocking.spid as HeadOfBlockingChain
from master.dbo.sysprocesses Blocking inner join master.dbo.sysprocesses Blocked
on Blocking.spid = Blocked.Blocked
where Blocking.Blocked = 0



After you get the list of blocking chains headers, you can check what those processes are doing. You can use dbccinput buffer or fn_get_sql function if you have service pack 3 or later. You can find more details about dbcc inputbuffer and fn_get_sql on BOL.

Adi

--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
vik_steve
vik_steve
SSC Rookie
SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)

Group: General Forum Members
Points: 28 Visits: 10
The applications is web based (php). I have managed to stabilise the database:

The error in log file was:

Autogrow of file ** in database ** cancelled or timed out after 3344 ms. Use ALTER DATABASE to set a smaller FILEGROWTH or to set a new size.

Having monitored the saze of the data file it hasn't really grown recently. It's 18GB in size but has been for months. I'm not sure why this has suddenly become an issue?

I set it to 2% and all seems OK. Could this be a hardware (although there is plenty of free disc space) / memory / processor issue?
Adi Cohn
Adi Cohn
SSCrazy Eights
SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)

Group: General Forum Members
Points: 8331 Visits: 6595
vik_steve (6/8/2009)
The applications is web based (php). I have managed to stabilise the database:

The error in log file was:

Autogrow of file ** in database ** cancelled or timed out after 3344 ms. Use ALTER DATABASE to set a smaller FILEGROWTH or to set a new size.

Having monitored the saze of the data file it hasn't really grown recently. It's 18GB in size but has been for months. I'm not sure why this has suddenly become an issue?

I set it to 2% and all seems OK. Could this be a hardware (although there is plenty of free disc space) / memory / processor issue?


This changes the picture. You should first manually add more space to the database. Add enough space so it won't have to use the auto grow. Using the auto grow is not recommended because it can add to the file's fragmentation. It should be used only if the DBA did not notice that the database ran out of place and you don't want that DML statements will fail because of space problems.

When you configure the autogrow set it by MB and not percentage. If you'll set it by MB it will always get the same addition to the file. If you'll set it by percentage, it will always get different addition size and you might get the timeout problem again. You should set it to a size that will be enough for your operations but not to much so it won't fail because of time out.

Adi

--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
vik_steve
vik_steve
SSC Rookie
SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)

Group: General Forum Members
Points: 28 Visits: 10
Thanks for the replies everyone.

So once the Autogrow kicked and timed-out, would this connection not be released by SQL and all the other blocked connections then successfully processed - or does it start looping until successful?

Also, when the DB performs an normal autogrow (successfully), is this logged in the ErrorLog file or stored in a system table somehwere? I need to know if a successful autogrow has occured since setting it to 2%.

As per the advice, I will in future use a set MB amount based on average increase in database size.

Thanks, Steve
Roshan Jospeh
Roshan Jospeh
Hall of Fame
Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)

Group: General Forum Members
Points: 3175 Visits: 2093
In master execute

select * from sysperfinfo where counter_name like '%Log growth%'



This will give total number of log growths

----------------------------------------------------------------------------------------------------------------------------------------------------
Roshan Joe

Jeff Moden -Forum Etiquette: How to post data/code on a forum to get the best help
vik_steve
vik_steve
SSC Rookie
SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)

Group: General Forum Members
Points: 28 Visits: 10
Is there a table that stores when the last autogrow happened on a DB?
vik_steve
vik_steve
SSC Rookie
SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)

Group: General Forum Members
Points: 28 Visits: 10
It seems that this can only be detected via the use of an lert?
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search