Post reply

Transaction Log File removal

  • November 23, 2016 at 9:51 am

    #323296

    I have a database with two transaction log files, I have deleted one log file using alter database remove file , when I run sp_helpdb it shows me the file that i deleted is not there, however when I check database properties the file still shows there, how do i make sql show correct information without restarting the instance?

  • November 23, 2016 at 10:00 am

    #1914274

    You probably haven't refreshed the GUI. I used to make that mistake all the time!

    John

  • November 23, 2016 at 10:07 am

    #1914275

    I doubt that, my PBM still report, it has two log files and policy is failing. btw I closed the mgmt studio and re-opened it, nothing has changed

  • November 23, 2016 at 10:07 am

    #1914276

    i took database offline and online again.. still same issue..

  • November 23, 2016 at 10:12 am

    #1914278

    Curious. What does this return?SELECT COUNT(*)

    FROM sys.database_files

    WHERE type_desc = 'LOG'

    John

  • November 23, 2016 at 10:48 am

    #1914290

    the result is 2

  • November 23, 2016 at 10:52 am

    #1914292

    the log file state is offline.. I wonder why..

  • November 23, 2016 at 11:00 am

    #1914294

    fixed.. the solution was to change database recovery model to simple and back to full, my assumption is that 2nd log file was still holding some information, that is why it didn't really get removed with alter database remove file.

  • December 6, 2016 at 9:43 am

    #1916287

    in full recovery a log backup will cure this issue

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

    "Ya can't make an omelette without breaking just a few eggs" 😉

Viewing 9 posts - 1 through 9 (of 9 total)

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