Results 1 to 10 of 10
  1. #1
    Join Date
    Dec 2007
    Posts
    288

    Unanswered: time-out occurred while waiting for buffer latch -- type 4

    I have a SQL Server 2005 Server (32bit) running SQL Server 2005 Standard Edition and SP3.

    The Server is a 4 Processor box (Two Dual core CPU's) - each processor at 2.87 GZh

    I have 16MB of RAM and three Instances running - one Default and two named.

    In my default instance only, I have been seeing this error Intermittently :


    Code:
    A time-out occurred while waiting for buffer latch -- type 4, 
    bp 02DA3904, page 1:18248, stat 0x1c0000b, database id: 2, 
    allocation unit Id: 141667044818944/423142021529600, task 0x00B851F8 : 
    0, waittime 300, flags 0x80039, owning task 0x008D9E28. 
    Not continuing to wait.
    The error is always associated with Database ID 2. The Database itself appears healthy and I have not had any complaints of permformance issues.

    in googling this error, I found things like:
    FIX: After you enable the Address Windowing Extensions (AWE) feature in SQL Server 2005, access violations may occur, and SQL Server 2005 may stop responding (Cumulative update package 4 for SQL Server 2005 Service Pack 2 ) where this error is fixed with Cumulative update package 4 for SQL Server 2005 Service Pack 2 (I am already at SP3
    -
    I also found (in this forum) where there was an incompatiblity issue with a certain version of Diskeeper - we are not running diskkeeper.
    --

    Description of common causes of SQL Server error message 844 or error message 845 indicates that it could be a setup problem, hardware problem, ... all very generic ...

    I'm not sure what to make of this error and how to begin addressing it- Can anyone shed some light on this for me???

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    The odds are very good that you've set either the size or the growth for TempDb inappropriately. TempDb is database #2 (oh, that statement is loaded in so many ways ).

    Fixing this problem is more art than science. You need to figure out what size to make TempDb, and how to "grow" it efficiently when growth is needed. One simple but expensive way to do this is to figure out how big TempDb gets on a relatively regular basis by checking its size on disk at SQL Server shutdown or before startup. Use that size as the startup size. There are many possible approaches, and this is just a "brute force" answer.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  3. #3
    Join Date
    Dec 2007
    Posts
    288
    Thank you for the reply Pat!

    All three of my TempDBs are set up the same.

    initial size 8
    Grow by 10% (data and log)
    autoclose - False
    autoshrink - False
    autoupdate statistics - True

    not sure what other options would make a difference.
    Last edited by itsonlyme44; 03-29-10 at 14:22. Reason: typo

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    The problem is that the SQL Server instance that is reporting the errors seems to use TempDb differently than the other instances do. My guess would be that it makes much heavier use or processes larger TempDb objects than the other instances.

    Each SQL Server instance has its own TempDb. Sometimes one SQL Server will use TempDb much differently than usual.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  5. #5
    Join Date
    Dec 2007
    Posts
    288
    That makes sense. The Default Instance on this box is the catch-all Instance. Many one-off Databases whereas the other instances have fewer databases and are single-application instances.

    What recomendations (if any) can you make for tuning my TEMPDB parameters?

    Make the initial size larger and set it to grow by a # of mg rather than a percentage??

  6. #6
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Sizing TempDb for volatile/active servers is an art. There are many, many opinions and methods for doing it and all of them require some insight into how your SQL Instance uses TempDb.

    Reread my first response for a "brute force" solution.

    Setting a fixed size growth amount is nearly always a good idea.

    If possile, setting both the initial size and the growth factor to some multiple of 16 Mb is usually a good idea

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  7. #7
    Join Date
    Dec 2007
    Posts
    288
    Thank you!!!!

  8. #8
    Join Date
    Dec 2007
    Posts
    288
    One more quick question. Once I find my TempDB 'sweet spot', Can I make the changes via the MGT studio .. ie, change TempDB initial size and growth rate and perhaps add additional files, turn off auto-statistics etc.. and then jsut restart the instance for the changes to take effect?

  9. #9
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Yes, SSMS (SQL Server Management Studio) is actually what I'd recommend using to configure your SQL Server unless you're very, very comfortable writing queries to do it.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  10. #10
    Join Date
    Dec 2007
    Posts
    288
    Thank you for your support Pat !!

Posting Permissions

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