June 6, 2009 at 2:56 am
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?
June 8, 2009 at 1:31 am
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
June 8, 2009 at 2:23 am
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/
June 8, 2009 at 2:25 am
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?
June 8, 2009 at 2:43 am
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/
June 8, 2009 at 6:50 am
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
June 8, 2009 at 7:15 am
In master execute
select * from sysperfinfo where counter_name like '%Log growth%'
This will give total number of log growths
June 8, 2009 at 8:09 am
Is there a table that stores when the last autogrow happened on a DB?
June 8, 2009 at 9:47 am
It seems that this can only be detected via the use of an lert?
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply