Post reply

Truncate Log File

  • January 19, 2012 at 8:39 am

    #249847

    Hi All,

    I am running SQL Server 2008R2 and am getting the following error:

    Msg 9002, Level 17, State 6, Line 2

    The transaction log for database '360MetaVerseNew' is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in sys.databases

    i know i need to shrink/truncate the log file but have no idea how to do it?

    i have tried some methods found in some forums such as:

    Use [360MetaVerseNew]

    Go

    select name,recovery_model_desc from sys.databases

    GO

    Alter database [YourDatabaseName] SET RECOVERY SIMPLE

    GO

    Declare @[H:\Logs\360MetaVerse_log.ldf] sysname

    select @[H:\Logs\360MetaVerse_log.ldf]=Name from sys.database_files where Type=1

    print @[H:\Logs\360MetaVerse_log.ldf]

    DBCC Shrinkfile(@LogFileLogicalName,100)

    but i am getting multiple various errors.

    Any help would be appreciated, thanks in advance

  • January 19, 2012 at 8:46 am

    #1435929

    Have a look through this article for some steps in resolving this

    http://qa.sqlservercentral.com/articles/Transaction+Log/72488/

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • January 19, 2012 at 8:53 am

    #1435937

    No, you don't need to shrink the log file. It's full. It has no free space in it. Therefore you need to grow the file, not shrink it.

    Shrinking a full log is like trying to fit the contents of a 10 liter container that is full of water into a 5 liter. It can't happen.

    What you need to do is figure out why the log is full and fix the root cause. See the article that Jason posted above, and please read through this - Managing Transaction Logs[/url]

    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

  • January 20, 2012 at 5:13 am

    #1436385

    Might be worth executing a CHECKPOINT first.

Viewing 4 posts - 1 through 3 (of 3 total)

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