Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Blocking SPIDS Expand / Collapse
Author
Message
Posted Saturday, June 6, 2009 2:56 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, June 8, 2009 9:47 AM
Points: 6, 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?
Post #730187
Posted Monday, June 8, 2009 1:31 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 10:37 AM
Points: 5,018, Visits: 10,538
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

Get your two-cent-answer quickly
spaghettidba.com
@spaghettidba
Post #730515
Posted Monday, June 8, 2009 2:23 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 3:01 PM
Points: 2,117, Visits: 5,445
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/
Post #730526
Posted Monday, June 8, 2009 2:25 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, June 8, 2009 9:47 AM
Points: 6, 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?
Post #730529
Posted Monday, June 8, 2009 2:43 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 3:01 PM
Points: 2,117, Visits: 5,445
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/
Post #730538
Posted Monday, June 8, 2009 6:50 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, June 8, 2009 9:47 AM
Points: 6, 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

Post #730647
Posted Monday, June 8, 2009 7:15 AM


SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Tuesday, August 19, 2014 3:08 AM
Points: 617, Visits: 1,273
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
Custom cleanup script for backups
Post #730661
Posted Monday, June 8, 2009 8:09 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, June 8, 2009 9:47 AM
Points: 6, Visits: 10
Is there a table that stores when the last autogrow happened on a DB?
Post #730695
Posted Monday, June 8, 2009 9:47 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, June 8, 2009 9:47 AM
Points: 6, Visits: 10
It seems that this can only be detected via the use of an lert?
Post #730762
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse