SQL Server Performance Forum – Threads Archive

sql 2k5 hung

Hello Team,
We are running SQL 2K5 64bit on wind 2k3. It was running fine since 5/30/2007 until last firday. We submit jobs they run for at least 6 to 7 hrs. after certain time they just running forever, (more than 48 hrs) 1. Indexes are in good shape
2. Stats are in good shape.
3. I can access the server without any problem.
4. No disk bad info in SQL SERVER logs. & No crash DUMP All I see is in Sightline( our performance mon tool). stright line at CPu usage 50 to 52 % not much physical disk activity after restarting server same 48 hr job finished in 30 min

What sql service pack and build you are on? SQL Server 2000 or SQL Server 2005 may temporarily stop responding on a Windows Server 2003 Service Pack 1-based computer
http://support.microsoft.com/kb/922658
MohammedU.
Microsoft SQL Server MVP
Moderator
SQL-Server-Performance.com All postings are provided â€œAS ISâ€ with no warranties for accuracy.

What kind of job you are running that is taking this long?
Also check whether any hotfixes on the windows or with windows update, change within operating system do have such downtrend inperformance as we have had similar situation.
Check SQLAgent log for more information. Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
@http://www.askasqlguru.com/ This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.

Windows NT 5.2 Build 3790 CSD Service Pack2 Microsoft SQL Server version 9.00.3042.00 Satya, I could not find any details in SQL AGENT Error Log. As I said these jobs are not that big after restart they completed in 30 min. So, They might have gone into some kind of loop (SQL SERVER internal code , not my stored procedure loop <img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />) never cameout. I just want to bring it to everyones attention that While CPU /IO intensive jobs running , I did try to ALTER the file size to MAX SIZE (this will avioid evry time autogrow) I have seen BUG CHECK file created. (Satya Do you want me send this file to you? you may send it to microsoft) my boss does not want to call MS-PSS to send CRASH DUMP few contents from error log here is the sample code ====================== 2007-07-16 09:58:32.56 spid55 Timeout occurred while waiting for latch: class ‘FCB’, id 0000000177C0B148, type 3, Task 0x0000000003812EF8 : 0, waittime 300, flags 0x1000000019, owning task 0x00000000060096D8. Continuing to wait. 2007-07-16 09:58:32.58 spid55 Using ‘dbghelp.dll’ version ‘4.0.5’ 2007-07-16 09:58:32.61 spid82 Timeout occurred while waiting for latch: class ‘FGCB_ADD_REMOVE’, id 000000008234BD58, type 2, Task 0x000000000BE08C58 : 0, waittime 300, flags 0x1a, owning task 0x0000000003812EF8. Continuing to wait. 2007-07-16 09:58:32.64 spid55 **Dump thread – spid = 55, PSS = 0x000000028E055BF0, EC = 0x000000028E055C00 2007-07-16 09:58:32.64 spid55 ***Stack Dump being sent to G:databasesmsaapolloprodMSSQL.2MSSQLLOGSQLDump0008.txt 2007-07-16 09:58:32.64 spid55 * ******************************************************************************* 2007-07-16 09:58:32.64 spid55 * 2007-07-16 09:58:32.64 spid55 * BEGIN STACK DUMP: 2007-07-16 09:58:32.64 spid55 * 07/16/07 09:58:32 spid 55 2007-07-16 09:58:32.64 spid55 * 2007-07-16 09:58:32.64 spid55 * Latch timeout 2007-07-16 09:58:32.64 spid55 * 2007-07-16 09:58:32.64 spid55 * Input Buffer 264 bytes - 2007-07-16 09:58:32.64 spid55 * DECLARE @RC int; EXEC Rpt_CMCR_Single_Final_Dataset_Batch @p_ 2007-07-16 09:58:32.64 spid55 * Batch_ID=14877, @p_Report_ID=35939, @[email protected] OUTPUT 2007-07-16 09:58:32.64 spid55 * 2007-07-16 09:58:32.64 spid55 * ******************************************************************************* 2007-07-16 09:58:32.64 spid55 * ——————————————————————————- 2007-07-16 09:58:32.64 spid55 * Short Stack Dump 2007-07-16 09:58:32.76 spid55 Stack Signature for the dump is 0x0000000176C6B746

your sproc could not acquire a latch and it timed out because of that.
a latch is a light weight lock:
http://www.sqldev.net/articles/locklatch.htm _______________________________________________
Causing trouble since 1980
blog:http://weblogs.sqlteam.com/mladenp

See now it is coming out with exception errors, looks like you have to call up MS PSS in this case to rectify.
I would suggest to do it with support contract as it is X64 bit issue. Run SQLDiag and collect all the logs you can collect for further assessment. Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
@http://www.askasqlguru.com/ This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.

Satya, Thanks for the info . However, Our folks do not want to go with MS PSS at this time. I don’t care if they dont care . All I can do is suggest . <img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ /> Did you experience the same kind of scenario. IF heavy duty jobs running you try to ALTER DATA FILES you may get this error. It may not be in 64 bit ,Also happening in 32 also.

]]>