Post reply

SQL 7 Can''t Remove Second Log File?

  • June 29, 2005 at 9:07 pm

    #186437

    I have a database powersuite with a data file and two log files.  I want to remove the second log file (called newlog) but cannot.  When I run the command to shrink and remove it, I get this:
     
    use powersuite

    go

    dbcc shrinkfile (newlog,emptyfile)

    go

    alter database powersuite remove file newlog

    go

     
    (1 row(s) affected)
     
    DBCC execution completed. If DBCC printed error messages, contact your system administrator.

    Server: Msg 5042, Level 16, State 2, Line 1

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

    I have searched everywhere and can't find a way to do this.  I have tried forching a switch of the log files to be able to remove it this way and backups with truncate option, etc but none worked.
     
    I refered to this article and used this procedure and still didn't get it to work:
     
    My log file refuses to shrink from the size it is at now.  Why is this?  How can I get rid of this thing?
     
    Thanks,
    Becker
     

  • June 30, 2005 at 7:28 am

    #569777

    Here's something you can look into for "possible cause"...

    You mention 2 log files - since the "emptyfile" option empties a file by moving the data to other files in the same filegroup, have you thought of looking at your "other" file to see if it has maxed out the size ?!?!

    If so, you should try shrinking the first log file and then removing your "newlog"!







    **ASCII stupid question, get a stupid ANSI !!!**

  • June 30, 2005 at 7:41 am

    #569791

    What recovery model do you use?

    If simple you can run

    backup log db_name with truncate_only

    this removes all inactive transactions from the log

    if you use full or bulk_logged model perform backup of transaction log.

    you can use this command to view which physical log file (FileId) holds the active portion of the log (status 2). You must ensure that the log file you want to remove isn't active.

    dbcc loginfo('db_name')

    If you can't move active portion to the other log file, long running transaction could be the cause.

    use dbcc opentran ('db_name') to check it.

    Needless to say, as soon as you remove log file, make a full backup of the db.

  • June 30, 2005 at 12:13 pm

    #570047

    I will try this tonight during off hours!

  • July 1, 2005 at 1:03 am

    #570240

    Can we delete a file in Primary filegroup??

  • July 1, 2005 at 3:18 am

    #570262

    Log files don't belong to any filegroups. But if you mean original log file that was created when the db was created, according to my testing (SQL 2000) it's not possible to drop it even if you add another log file and the primary one is empty. It shows error message that you cannot drop primary file or log file.

  • July 1, 2005 at 3:31 am

    #570264

    Martin

    Good Hunting.

  • July 1, 2005 at 8:01 am

    #570330

    There are also knonw issues with the virtual logs within the log file in SQL 7. MS for some reason never really corrected the issue in any service release to my knowledge. However there was a script posted all over the internet that can help break the virtual logs up and free the log file to where you should be able to delete it.

    Just go here.

    http://www.sqlservercentral.com/scripts/contributions/26.asp

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

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