Post reply

Extreme wait-time when taking a SQL Server database online

  • January 19, 2012 at 5:38 am

    #148946

    Hello,

    I don't understand something. I have a database (about 5 Go) on SQL Server 2005 SP3 Enterprise Edition 32bits. When I tried to put it online I have a extreme wait-time (40 sec to become online). On the same instance, I have another database much more bigger (about 60 Go) and the time for the database to become online is only 1 sec.

    Any idea ?

    Thanks,

    PS : I performed a checkdb on this database and I got no error.

  • January 19, 2012 at 6:33 am

    #1435782

    Probably the number of vlfs in the log file, excessive numbers slow recovery.

    run dbcc loginfo(yourdbname)

    and see how many rows returned, thats how many vlfs you have

    ---------------------------------------------------------------------

  • January 19, 2012 at 6:43 am

    #1435789

    When I ran dbcc loginfo() on my database I got 8262 rows.

  • January 19, 2012 at 6:46 am

    #1435791

    thats a high number. How big is the log?

    I would recommend scheduling an outage to shrink the log right down then grow it again in sensibly sized chunks.

    see http://www.sqlskills.com/BLOGS/KIMBERLY/post/Transaction-Log-VLFs-too-many-or-too-few.aspx

    ---------------------------------------------------------------------

  • January 19, 2012 at 6:51 am

    #1435797

    log file 2Go

  • January 19, 2012 at 6:56 am

    #1435801

    ERIC CRUDELI (1/19/2012)

    log file 2Go

    ?

    ---------------------------------------------------------------------

  • January 19, 2012 at 7:00 am

    #1435808

    Sorry

    The size of my *.LDF is 2 Go with log space used 26% (DBCC SQLPERF(LOGSPACE))

  • January 19, 2012 at 7:11 am

    #1435819

    OK, 8000 vlfs in 2GB is a lot, I suspect your current growth factor for the log is 1MB, leading to fragmentation,

    You need to shrink your log file right down, then manually grow it to 1GB, and then again to 2GB. Then permanantly change the log growth factor to 1GB

    ---------------------------------------------------------------------

  • January 19, 2012 at 9:25 am

    #1435983

    Thanks I solved my issue.

    Could you explain me how to determine if the number of VLFS is too high ?

  • January 19, 2012 at 10:24 am

    #1436035

    Eric, thats a big question, there are only rules of thumb, but if your recovery times are too long, thats a clue its too high. I would say the number of VLFs should ideally be < 100.

    Can I refer you to the link I posted above, read that and the other links it refers too.

    ---------------------------------------------------------------------

  • January 19, 2012 at 10:32 am

    #1436042

    Thanks for all 😀

    Eric

  • January 19, 2012 at 10:33 am

    #1436044

    pleasure

    ---------------------------------------------------------------------

Viewing 12 posts - 1 through 11 (of 11 total)

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