Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding

  • Mani-584606

    SSChampion

    Points: 11788

    Hi,

    We have log shipping configured in SQL Server 2008 R2 SP2.

    Log shipping copy job is failing and I'm getting the below error most of the times when I'm trying to see the view history for that job.

    Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding

    and sometimes it's showing the actual error as below:

    Message

    2012-08-07 13:01:52.03 *** Error: The handle is invalid.

    (mscorlib) ***

    Please advise.

  • Orlando Colamatteo

    SSC Guru

    Points: 182268

    This may be the GUI tming out trying to build the job history due to too much volume, or a slow or overburdened server. Does your server generate a lot of job history? When is the last time you cleaned it out?

    -- delete all job history older than a couple weeks, adjust to suit

    DECLARE @dt DATETIME ;

    SET @dt = DATEADD(day, -15, GETDATE())

    EXEC msdb.dbo.sp_purge_jobhistory

    @oldest_date = @dt ;

    GO

    __________________________________________________________________________________________________
    There are no special teachers of virtue, because virtue is taught by the whole community. --Plato

  • pnaveenkumar.reddy405

    SSC Veteran

    Points: 299

    even i am facing the same problem..and the sql server machines i have are..newly created ones,how do i have any job history....?

    please recommend any other solution....

    thank you

    P.Naveen kumar reddy

  • Orlando Colamatteo

    SSC Guru

    Points: 182268

    How new? Log Shipping jobs can fill up the job history in a hurry. So can jobs with very frequent schedules, e.g. run every 15 seconds. Have you checked the job history directly via the tables in msdb? There are many reasons why the UI can timeout. Is your server extremely busy?

    __________________________________________________________________________________________________
    There are no special teachers of virtue, because virtue is taught by the whole community. --Plato

  • pnaveenkumar.reddy405

    SSC Veteran

    Points: 299

    not at all busy......it's all a new configured ones....no transaction ran except a few before a full backup taken.....no work currently is being done on it.............?

  • Orlando Colamatteo

    SSC Guru

    Points: 182268

    Did you check the job tables to see what was in them? If you're running replication or log shipping or other subsystems you might be surprised at what is in there.

    Like I said, if it's not job history it could be any number of other things including your install of SSMS or other client/network conditions.

    Google for ssms times out job history and you may find a nice workaround or two that you like better.

    __________________________________________________________________________________________________
    There are no special teachers of virtue, because virtue is taught by the whole community. --Plato

  • pnaveenkumar.reddy405

    SSC Veteran

    Points: 299

    yes i did check everything.....after all i didn't configure any.....?i have all protocols,remote admin connections,query governor cost limit enabled for sure.....

  • Orlando Colamatteo

    SSC Guru

    Points: 182268

    All I can say is look for a workaround because there is no way for us to know what is happening in your environment as there are too many variables.

    If you see the results of the link I posted there is a Connect item open for this exact issue and Microsoft says performance was improved in SSMS 2012.

    __________________________________________________________________________________________________
    There are no special teachers of virtue, because virtue is taught by the whole community. --Plato

  • pnaveenkumar.reddy405

    SSC Veteran

    Points: 299

    a last thing....what would be the possible error reason for a newly installed sql server machine with no job alerts n nothing in it....?if i am configuring it for the first time.............?

    thank you

    P.Naveen kumar reddy

  • Orlando Colamatteo

    SSC Guru

    Points: 182268

    Could be lots of things. Some are related to SQL Server, some related to your environment at large, e.g. network issues, slow server in general due to non SQL Server processes, blocking on the server due to activity in msdb although you're saying there are no scheduled jobs (which begs the question, why are you trying to view job history). The issue you're experiencing is usually related to an over-abundance of job history, however if that is not the issue then there are simply too many possibilities and without having direct access to your environment to start testing some things I cannot even venture a valid guess at what it could be...good luck.

    __________________________________________________________________________________________________
    There are no special teachers of virtue, because virtue is taught by the whole community. --Plato

  • chandan_jha18

    SSChampion

    Points: 11264

    Mani-584606 (8/7/2012)


    Hi,

    We have log shipping configured in SQL Server 2008 R2 SP2.

    Log shipping copy job is failing and I'm getting the below error most of the times when I'm trying to see the view history for that job.

    Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding

    and sometimes it's showing the actual error as below:

    Message

    2012-08-07 13:01:52.03 *** Error: The handle is invalid.

    (mscorlib) ***

    Please advise.

    You question has two questions inside it:

    1) Log shipping job fails intermittently: This could be due to the network issue. If I were you, I would have kept a report file for each job and the report file can give us some clues about the job failure.

    for me, it has often been: 'specified network no longer available ' or 'insufficient disk space'

    2) You try to open up the job history and it times out- Is something blocking your msdb tables? Are the history tables becoming too big? Try opening this history again and side by side Run sp_who2 or sp_WhoIsActive procedure to find out what process is blocking you.

    Thanks

    Chandan

  • Leon Venediktou

    SSC Rookie

    Points: 41

    This is the case and solution to my server a SQL Server 2008R2 installation.
    Cause of pain, the job agent history log was let unlimited, none of the history ticks were ticked.
    I was fighting on two fronts, small HDD size and large MSDB and TEMPDB system databases.

    1. checked MSDB size, Data 7gb Log 33gb - this was due to running the sp_purge_jobhistory - bloats MSDB and tempDB
    2. checked via script the msdb for large objects; table dbo.sysjobhistory had loads of rows and size wise was the bulk of the 7gb MSDB.
    3. i made sure i can shrink tempdb (restart sqls erver) and MSDB by putting it to single mode, shrink and multi again.
    4. Kept run sp_purge_jobhistory with shorter and shorter 'oldest date' keeping an eye on size of MSDB and TEMPDB shrinking as i go. this was probably not essential but i am a bit careful with large purges. you could try one off, keep only 14 days for example.
    5. rerun the object dbo.sysjobhistor size check in MSDB, noticed that the actual size was due to the table index being huge although the rows are not only 100.
    6. open mdsb  table dbo.sysjobhistory and found the two index the clustered and the non clustered; right click and check fragmentation. 96% - ticked Reorganise.
    7. right click on MSDB and task shring database - this shrunk the msdb to a mnimum size 120 MB

    Went on Sql Agent job and clicked for the history - no time out and faster than ever!

    MSDB Object size code.

    USE msdb
    GO
    SELECT TOP(10)
      o.[object_id]
      , obj = SCHEMA_NAME(o.[schema_id]) + '.' + o.name
      , o.[type]
      , i.total_rows
      , i.total_size
    FROM sys.objects o
    JOIN (
      SELECT
        i.[object_id]
       , total_size = CAST(SUM(a.total_pages) * 8. / 1024 AS DECIMAL(18,2))
       , total_rows = SUM(CASE WHEN i.index_id IN (0, 1) AND a.[type] = 1 THEN p.[rows] END)
      FROM sys.indexes i
      JOIN sys.partitions p ON i.[object_id] = p.[object_id] AND i.index_id = p.index_id
      JOIN sys.allocation_units a ON p.[partition_id] = a.container_id
      WHERE i.is_disabled = 0
       AND i.is_hypothetical = 0
      GROUP BY i.[object_id]
    ) i ON o.[object_id] = i.[object_id]
    WHERE o.[type] IN ('V', 'U', 'S')
    ORDER BY i.total_size DESC

  • Jeff Moden

    SSC Guru

    Points: 994284

    Thanks for sharing that, Leon.  A lot of people forget about such things.  MSDB IS a database and it does need to be maintained.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

    Helpful Links:
    How to post code problems
    Create a Tally Function (fnTally)

Viewing 13 posts - 1 through 13 (of 13 total)

You must be logged in to reply to this topic. Login to reply