Post reply

SQL 2000 Removing Secondary Log File

  • June 9, 2008 at 4:55 pm

    #380992

    Hi,

    I am working on reindexing a very big database (over 300 GB). The existing log drive doesn't have enough space for the operation. So what I did is to add a secondary ldf to another drive and performed the reindexing. After all this I need to remove the secondary ldf and couldn't.

    This is what I have done:

    DBCC LOGINFO (databasename)

    It displays one active virtue log for the secondary log file.

    3 3276734464 8192 94165 2 128 0

    Backup log.

    DBCC LOGINFO (databasename again)

    3 3276734464 8192 94165 0 128 0

    Status reset to 0, all good.

    Run DBCC OPENTRAN:

    Replicated Transaction Information:

    Oldest distributed LSN : (94170:30899:15)

    Oldest non-distributed LSN : (0:0:0)

    The open transaction is not on the secondary log file. By the way, the database is set up to have transactional replication.

    DBCC SHRINKFILE (logical log file, EMPRTYFILE), results:

    5 3 399993 399993 399992 399992

    The file doesn't empty itself.

    Run DBCC LOGINFO:

    3 3276734464 8192 94171 2 64 0

    The status became 2 again, back to square one.

    Can anyone have a look for me? Have I missed anything?

    Thank you in advance.

    Alan

  • June 10, 2008 at 9:09 am

    #826733

    Try running DBCC opentran just after running DBCC SHRINKFILE with emptyfile statement and check whether file is getting used during that time or not.

    Also, try deleting the log file using query analyzer alter database dbname remove file filename or using enterpirse manager just after emptying the file.

    Manu

  • June 10, 2008 at 5:23 pm

    #827009

    Thanks Manu,

    Does the log file sequence number for the secondary log file I want to empty need to be the last?

    The reason I am asking is everytime after I run DBCC Shrinkfile with emptyfile option, there is always one more log file sequence number with open transaction on it immediately behind the secondary log file sequence number.

    Regards,

    Alan

  • June 11, 2008 at 8:43 am

    #827295

    Alan,

    I have never gone that much deeper so cant comment on it. Better if you try using the GUI or isqlw option. I never had any issues in deleting mdf and ldf files when they were not required.

    Do let me know the results..

    Manu

  • June 12, 2008 at 5:28 pm

    #828179

    Hi Manu,

    We have finally managed to remove the sec log file by changing the database into single user mode. Backup log, Shrinkfile with empty switch.

    So the trick is the single user mode.

    Thanks for your time looking into this

    Alan

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

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