SQL Server Performance

Stack dump error and SQL service gets restarted daily

Discussion in 'General DBA Questions' started by Ebenraja, May 26, 2009.

  1. Ebenraja Member

    All,
    Please help me with this problem i face...
    I am using a SQL7.0 version server and recently for the past 1 week my SQL related services goes for a restart early morning at the same time daily.
    When i verified my Error log i was able to find the following 'Stack dump' listed below.
    The service pack for the SQL7.0 version is SP4 and i am not sure if this is related with the application..is it memory related ..please help me with this
    --------------------------------------------------------------------------------------------------------------------------------------------------
    2009-05-25 03:22:57.53 spid21 Using 'sqlimage.dll' version '4.0.5'
    Dump thread - spid = 21, PSS = 0x4a0df100, EC = 0x4a0df2b0
    Stack Dump being sent to F:MSSQL7logSQL00199.dmp
    *******************************************************************************
    *
    * BEGIN STACK DUMP:
    * 05/25/09 03:22:58 spid 21
    *
    * Input Buffer 3080 bytes -
    * S E T L O C K _ T I M E O U T 2 0 0 0 0 S E T I M P L I C I T _
    * T R A N S A C T I O N S O F F i f @ @ T R A N C O U N T > 0
    * C O M M I T T R A N S A C T I O N
    * S E T L a n g u a g e u s _ e n g l i s h
    * S E T A N S I _ W A R N I N G S O F F
    * S E T C U R S O R _ C L O S E _ O N _ C O M M I T O F F
    * S E T Q U E R Y _ G O V E R N O R _ C O S T _ L I M I T 0
    * S E T T R A N S A C T I O N I S O L A T I O N L E V E L R E A D
    * U N C O M M I T T E D
    * s e t n o c o u n t o n
    * s e t d a t e f i r s t 1 i f ( s e l e c t c o u n t ( * )
    * f r o m # T a b l e D e t a i l s ) > 0 a n d ( s e l e c t
    * d a t e p a r t ( d w , g e t d a t e ( ) ) ) i n ( 0 , 1 , 2 , 3 ,
    * 4 , 5 , 6 , 7 ) b e g i n d e c l a r e @ c o m m a n d v a r c
    * h a r ( 2 0 4 8 ) , @ d b n a m e v a r c h a r ( 2 5 5 ) , @ m o
    * d e s m a l l i n t , @ d b i d s m a l l i n t , @ t a b l e i
    * d i n t d e c l a r e R e o r g C u r s o r i n s e n s i t i v
    * e c u r s o r f o r s e l e c t t . D B N a m e , t . D B I D
    * , t . T a b l e I D f r o m # T a b l e D e t a i l s t ( n o
    * l o c k ) w h e r e l o w e r ( t . D B N a m e ) n o t i n (
    * ' # $ 0 ' , ' m a s t e r ' , ' m o d e l ' , ' n o r t h w i n d ' , '
    * p u b s ' , ' t e m p d b ' ) a n d u p p e r ( t . T a b l e T y p
    * e ) i n ( ' B ' , ' U ' ) a n d ( t . I n d e x I D = 1 o
    * r t . I n d e x I D = 2 5 6 ) a n d t . N u m R o w s > 0
    * o r d e r b y D B N a m e , T a b l e N a m e , T a b l e O w
    * n e r f o r r e a d o n l y
    * o p e n R e o r g C u r s o r f e t c h R e o r g C u r s o r i
    * n t o @ d b n a m e , @ d b i d , @ t a b l e i d w h i l e @
    * @ f e t c h _ s t a t u s = 0 b e g i n i f ( s e l e c t c
    * o u n t ( * ) f r o m m a s t e r . . s y s l o c k i n f o ( n o
    * l o c k ) w h e r e r s c _ d b i d = @ d b i d a n d r e q
    * _ m o d e n o t i n ( 3 , 8 ) a n d ( r s c _ t y p e = 2
    * o r ( r s c _ t y p e = 5 a n d r s c _ o b j i d = @ t
    * a b l e i d ) ) ) = 0 s e l e c t @ c o m m a n d = ' u s e
    * [ ' + r e p l a c e ( @ d b n a m e , c h a r ( 9 3 ) , c h a r (
    * 9 3 ) + c h a r ( 9 3 ) ) + ' ] d b c c s h o w c o n t i g ( '
    * + c o n v e r t ( v a r c h a r ( 2 0 ) , @ t a b l e i d ) +
    * ' ) ' e l s e s e l e c t @ c o m m a n d = ' u s e [ ' +
    * r e p l a c e ( @ d b n a m e , c h a r ( 9 3 ) , c h a r ( 9 3 ) + c
    * h a r ( 9 3 ) ) + ' ] s e l e c t ' ' T a b l e n o t a c c
    * e s s i b l e ' ' , o b j e c t _ n a m e ( ' + c o n v e r t ( v a
    * r c h a r ( 2 0 ) , @ t a b l e i d ) + ' ) ' e x e c u t e ( @
    * c o m m a n d ) f e t c h R e o r g C u r s o r i n t o @ d b n
    * a m e , @ d b i d , @ t a b l e i d E n d C l o s e R e o r g
    * C u r s o r d e a l l o c a t e R e o r g C u r s o r T r u n c a
    * t e t a b l e # T a b l e D e t a i l s e n d
    *
    *
    *******************************************************************************
    -------------------------------------------------------------------------------
    Short Stack Dump
    0x77f8850f Module(ntdll+850f) (LdrUnloadDll+36)
    0x007826f6 Module(sqlservr+3826f6) (ex_terminator+37)
    0x78006942 Module(MSVCRT+6942) (_unDNameEx+575)
    0x7800686a Module(MSVCRT+686a) (_unDNameEx+49d)
    0x7800699e Module(MSVCRT+699e) (_unDNameEx+5d1)
    0x780071b7 Module(MSVCRT+71b7) (_unDNameEx+dea)
    0x77fbb272 Module(ntdll+3b272) (wcstombs+13a4)
    0x77facdaa Module(ntdll+2cdaa) (RtlQueryTagHeap+22)
    0x7800716a Module(MSVCRT+716a) (_unDNameEx+d9d)
    0x78006c3c Module(MSVCRT+6c3c) (_unDNameEx+86f)
    0x78006b7c Module(MSVCRT+6b7c) (_unDNameEx+7af)
    0x780069fb Module(MSVCRT+69fb) (_unDNameEx+62e)
    0x780071b7 Module(MSVCRT+71b7) (_unDNameEx+dea)
    0x77fbb272 Module(ntdll+3b272) (wcstombs+13a4)
    0x77facc28 Module(ntdll+2cc28) (RtlSetUserFlagsHeap+1e2)
    0x77f91bc6 Module(ntdll+11bc6) (RtlSetEnvironmentVariable+cf)
    0x78007108 Module(MSVCRT+7108) (_unDNameEx+d3b)
    0x004f8998 Module(sqlservr+f8998) (SetEcFlagAndThrow+38)
    0x004f879e Module(sqlservr+f879e) (ex_raise2+332)
    0x004f8616 Module(sqlservr+f8616) (ex_raise+5f)
    0x00535dbc Module(sqlservr+135dbc) (SendRstatusStream+44)
    0x0040fd80 Module(sqlservr+fd80) (CParamExchange::processReturnStatus+4c)
    0x0040e5d6 Module(sqlservr+e5d6) (CMsqlExecContext::Execute+328)
    0x0040ddb0 Module(sqlservr+ddb0) (CSQLSource::Execute+232)
    0x00503fae Module(sqlservr+103fae) (CStmtExecStr::XretExecute+271)
    0x0040e9ab Module(sqlservr+e9ab) (CMsqlExecContext::ExecuteStmts+11b)
    0x0040e4c0 Module(sqlservr+e4c0) (CMsqlExecContext::Execute+1bb)
    -------------------------------------------------------------------------------------------
  2. satya Moderator

    When you are saying the server is getting restarted and created dump files at the same time regularly, then it is better to closely watch what kind of services/jobs/scripts are running?
    From the code that is displayed on the dump, it looks like that is causing a big issue.
  3. Ebenraja Member

    Thanks for the mail. The server is fine its the SQL serive that gets restarted.
    On the code that has generated am not abel to understand it promptly... but in that time there are no SQL jobs configured to run, I have scheduled a trace to run .lets me see if the trace gives some more detail.
  4. Ebenraja Member

    Thanks satya..it was my application tool that was causing the issue. .....
    -- Eben

  5. satya Moderator

    What kind of tool is that causing such a mishap on SQL Server?
    Is it a third party tool or inhouse developed?
  6. Ebenraja Member

    3rd party tool .. Idera SQl diag tool.......
    Regards
    Eben
  7. FrankKalis Moderator

    If you are sure, that the tool caused this, I would contact Idera support and let them know.
  8. Ebenraja Member

    Hi Frank,
    Kind of you...
    Problem is " Table growth statistics collection for all days was enabled for each day " in the server, when I unchecked the option the SQL service stopped getting restarted.
    The SQL version is SQL7.0 so i feel the overload could not have been withstandable as the same statistics option has been checked up in other SQL 8.0 versions where restart is not happening.
    Also as said by me before it happens only during that specific period.
    I dont know where i stand but now my problem is resolved and the server is stable.
    Thanks for your time Frank. I am still not certain if this issue is related with the tool but I feel so.
    Regrads,
    Eben

Share This Page