Results 1 to 7 of 7

Thread: Timeout

  1. #1
    Join Date
    Jan 2006
    Posts
    74

    Question Unanswered: Timeout

    Hiya all

    I'm currently supporting an sqlserver db, user is having timeout error message, there is no other description, does anyone know how I go about solving this? She uses an ODBC link I have tried changing the 30000 miliseconds setting but it doesnt work. Any ideas would be most grateful.

    Thanks
    Bav
    Last edited by bmistry; 01-05-06 at 09:06.

  2. #2
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    Have you run on a trace on the part of the application in question to identify the long running query. Pay attention to duration.

    Have you checked for blocking using sp_who,sp_who2 and sp_lock or the current activity window in the EM?
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  3. #3
    Join Date
    Jan 2006
    Posts
    74
    Hi sean

    I have not yet ran a trace will do so.

    I have just started work on SqlServer so am unaware of how I need to check for blocking using sp_who,sp_who2 and sp_lock or the current activity window in the EM, how do I do this?

  4. #4
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    execute sp_who in the query analyzer and see if you have anything other than 0 in the blk field.

    once you have identified the long running query (if there is no blocking), follow Bretts instruction in the sticky on the top of this page.

    Oh yeah, try reading the manual before flying the plane as the blind dude would say...

    http://www.microsoft.com/downloads/d...DisplayLang=en
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  5. #5
    Join Date
    Jan 2006
    Posts
    74
    I looked up the error on the website and was told to run the below query

    IF EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'PurgeRevisionsByDate')
    BEGIN
    PRINT 'Dropping Procedure PurgeRevisionsByDate'
    DROP Procedure dbo.PurgeRevisionsByDate
    END

    GO

    PRINT 'Creating Procedure PurgeRevisionsByDate'
    GO

    create procedure dbo.PurgeRevisionsByDate
    (
    @RevisionDate datetime
    )
    with encryption
    as
    /**
    @exception - @RevisionDate value is earlier than the last revision purge
    */
    declare @LastPurgeDate datetime
    declare @SQL varchar(255)

    set nocount on

    /*Verify whether the RevisionDate value is earlier than the last revision purge.*/
    select @LastPurgeDate = LastRevisionPurge from Utility

    if @LastPurgeDate is not null and @LastPurgeDate >= @RevisionDate
    return 1 /*The RevisionDate value is earlier than the last revision purge.*/


    create table #Revisions
    (
    Id int not null
    )

    insert into #Revisions
    select ID from Node where Type != 256 AND ArchivedWhen <= @RevisionDate
    UNION
    Select ID From Node where Type = 256 AND NodeGUID not in
    (select NodeGUID from NodeResource where NodeGUID is not null) AND ArchivedWhen <= @RevisionDate


    DELETE FROM NodeProperty WHERE NodeId in (SELECT Id FROM #Revisions)
    DELETE FROM NodeRole WHERE NodeId in (SELECT Id FROM #Revisions)
    DELETE FROM NodeResource WHERE NodeId in (SELECT Id FROM #Revisions)
    DELETE FROM NodePlaceholderContent WHERE NodeId in (SELECT Id FROM #Revisions)
    DELETE FROM NodePlaceholder WHERE NodeId in (SELECT Id FROM #Revisions)
    DELETE FROM LayoutProperty WHERE NodeId in (SELECT Id FROM #Revisions)
    DELETE FROM NodeLayout WHERE NodeId in (SELECT Id FROM #Revisions)
    DELETE FROM UserRoleMember WHERE NodeId in (SELECT Id FROM #Revisions)
    DELETE FROM NodeLock WHERE NodeId in (SELECT Id FROM #Revisions)
    DELETE FROM Node WHERE Id in (SELECT Id FROM #Revisions)


    DECLARE @dateGMT datetime, @dateCurrent datetime
    select @dateCurrent = GETDATE()
    exec GetGMTTime @dateCurrent, @dateGMT out

    if @RevisionDate > @dateGMT
    set @RevisionDate = @dateGMT

    if exists (select * from Utility )
    update Utility set LastRevisionPurge = @RevisionDate
    else
    insert into Utility (LastRevisionPurge) values(@RevisionDate)

    return 0
    go


    GRANT EXEC ON dbo.PurgeRevisionsByDate TO CMSSystem

    GO

    --exec PurgeRevisionsByDateDemkal '2005-07-06 02:00:18.273'

    which I did but came back with the below error message:

    Dropping Procedure PurgeRevisionsByDate
    Creating Procedure PurgeRevisionsByDate
    Cannot add rows to sysdepends for the current stored procedure because
    it depends on the missing object 'GetGMTTime'. The stored procedure will still be created.
    Server: Msg 4604, Level 16, State 1, Line 3
    There is no such user or group 'CMSSystem'.

    I'am I heading in the right direction or is there something more simplier I can do?

  6. #6
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    this looks application specific to some software you might be using?
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  7. #7
    Join Date
    Jan 2006
    Posts
    74
    I didnt think it was a database problem as I can see nothing wrong with it.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •