SQL Server Performance

sql 2k5 hung

Discussion in 'SQL Server 2005 Performance Tuning for DBAs' started by SQLDBAS, Jul 16, 2007.

  1. SQLDBAS New Member

    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
  2. MohammedU New Member

    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.
  3. satya Moderator

    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.
  4. SQLDBAS New Member

    Windows NT 5.2 Build 3790 CSD Service Pack2<br />Microsoft SQL Server version 9.00.3042.00 <br /><br /><br />Satya, I could not find any details in SQL AGENT Error Log. As I said these jobs are not <br />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.<br /><br />I just want to bring it to everyones attention that <br /><br />While CPU /IO intensive jobs running ,<br />I did try to ALTER the file size to MAX SIZE (this will avioid evry time autogrow)<br />I have seen BUG CHECK file created. (Satya Do you want me send this file to you?<br />you may send it to microsoft) my boss does not want to call MS-PSS to send CRASH DUMP few contents from error log <br />here is the sample code<br />======================<br />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.<br />2007-07-16 09:58:32.58 spid55 Using 'dbghelp.dll' version '4.0.5'<br />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.<br />2007-07-16 09:58:32.64 spid55 **Dump thread - spid = 55, PSS = 0x000000028E055BF0, EC = 0x000000028E055C00<br />2007-07-16 09:58:32.64 spid55 ***Stack Dump being sent to G:databasesmsaapolloprodMSSQL.2MSSQLLOGSQLDump0008.txt<br />2007-07-16 09:58:32.64 spid55 * *******************************************************************************<br />2007-07-16 09:58:32.64 spid55 *<br />2007-07-16 09:58:32.64 spid55 * BEGIN STACK DUMP:<br />2007-07-16 09:58:32.64 spid55 * 07/16/07 09:58:32 spid 55<br />2007-07-16 09:58:32.64 spid55 *<br />2007-07-16 09:58:32.64 spid55 * Latch timeout<br />2007-07-16 09:58:32.64 spid55 *<br />2007-07-16 09:58:32.64 spid55 * Input Buffer 264 bytes -<br />2007-07-16 09:58:32.64 spid55 * DECLARE @RC int; EXEC Rpt_CMCR_Single_Final_Dataset_Batch @p_<br />2007-07-16 09:58:32.64 spid55 * Batch_ID=14877, @p_Report_ID=35939, @p_ReturnCode=@RC OUTPUT<br />2007-07-16 09:58:32.64 spid55 * <br />2007-07-16 09:58:32.64 spid55 * *******************************************************************************<br />2007-07-16 09:58:32.64 spid55 * -------------------------------------------------------------------------------<br />2007-07-16 09:58:32.64 spid55 * Short Stack Dump<br />2007-07-16 09:58:32.76 spid55 Stack Signature for the dump is 0x0000000176C6B746<br />
  5. spirit1 New Member

  6. satya Moderator

    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.
  7. SQLDBAS New Member

    Satya,<br />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=':)' /> <br /><br />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.<br />

Share This Page