Log space issue

  • Hi friends,

    here i need some information regarding Log space.

    Today i have faced one issue. for one my database log file is occupying 390GB space due to that less space in drive.

    so checked in DBCC SQLPERF(LOGSPACE) here log space used is 2.8Gb

    select

    log_reuse_wait_desc from sys.databases

    where name = 'Database' here Nothing is showing.here we can't do shink because this is my production database.

    so can anyone suggest me on this how can i fix this issue

    my database is in simple recover model

  • Monitor the log usage for a couple of weeks (especially after maintenance). See from that how large the log needs to be to support the app. Shrink the log to a little larger than that.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • thanks for your reply.

    How can i monitor the log file. we have 390gb of log file so if i shink we can do it up to 350gb or we can do it more.

  • The same command you specified in your initial post works fine. Monitor on a regular basis for a couple of weeks, see how big the log needs to be, then you know how large the log needs to be.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • You are performing log backups, correct?

    These help manage the size of the log.

  • Hi Steve

    here we have a database with simple recovery model

  • In that case, follow Gail's advice. Record the peak log usage and shrink to that (leave a pad).

  • We can monitor but presently we dont have the space in drive we have only 3 gb left. i have to fix this immediately.

    can you please advice how can i increase the space for temporarily.then we can monitor the log size.

  • You can add another log file to the filegroup.

    http://technet.microsoft.com/en-us/library/ms189253.aspx#TsqlProcedure

  • thank you all for your valuable information.

    here i have shrink the log now we got 30 gb space. here we have 360gb log now. can i reduce it up to 300gb.

    i want to know if we reduce it how it will effect to our database.

  • It doesn't affect the database, but if the space is needed, the log will grow if autogrow is enabled, and stop accepting transactions if it isn't.

    The size of the log should be set based on the workload. Nothing else.

  • New persopn (10/19/2013)


    here i have shrink the log now we got 30 gb space. here we have 360gb log now. can i reduce it up to 300gb.

    No way to answer that. We don't know your environment, we don't know how large the log needs to be. Maybe you can, maybe you can't, maybe it'll grow straight back, maybe it won't.

    You need to do some investigations, see what it is that requires the most log space, how much it needs, whether or not it can be tuned.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • Thanks for u r information. i will monitor the log for few days and i will let you know how much it is occupying.

  • If your log has experienced lots of autogrowth you might want to check the number of vlfs. I've seen 3 cases in the wild where vlfs have been extremely high, mostly on 2008 r2 sp1 (sp2 has the fix).

    Why is it that people who can't take advice always insist on giving it? - James Bond, Casino Royale

  • How can i check whether how many virtual log files in log

Viewing 15 posts - 1 through 15 (of 28 total)

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