Post reply

Database Log file not able to delete

  • August 20, 2014 at 5:32 am

    #292481

    Hi Experts,

    I am not able to delete a db log file .I used the below command to delete but its throwing error.

    USE ABC

    GO

    DBCC SHRINKFILE('ABC_Log4', EMPTYFILE)

    GO

    ALTER DATABASE [ABC_raf] REMOVE FILE [ABC_Log4]

    GO

    Cannot shrink log file 5 (ABC_Log4) because the logical log file located at the end of the file is in use.

    (1 row(s) affected)

    DBCC execution completed. If DBCC printed error messages, contact your system administrator.

    Msg 5042, Level 16, State 2, Line 1

    The file 'ABC_Log4' cannot be removed because it is not empty.

  • August 28, 2014 at 12:09 pm

    #1741710

    hi,

    Here ,

    first ---R u trying to delete ndf file ?

    second-- If yes, make sure file groups are in primary file group. otherwise you cant delete a file which is in operation or use..

  • August 30, 2014 at 7:53 pm

    #1742094

    It means there is still an active transaction in that ldf.

    So, take couple of log backups and try to shrink again with empty file option and then delete it.

    Let me know how it went.

    Regards,
    SQLisAwe5oMe.

  • September 1, 2014 at 3:15 am

    #1742189

    Thanks SQLisAwe5oMe

  • September 1, 2014 at 3:22 am

    #1742190

    The EmptyFile option of shrinkfile is only relevant to data files. Specifying the option when shrinking log files has no effect whatsoever. You cannot move the active portion of the log with a shrink file and you cannot delete a log file that contains any portion of the active log.

    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

  • September 2, 2014 at 1:39 am

    #1742313

    Thanks Gail..

Viewing 6 posts - 1 through 5 (of 5 total)

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