SQL 2000 Removing Secondary Log File

  • 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.

    3327673446481929416521280

    Backup log.

    DBCC LOGINFO (databasename again)

    3327673446481929416501280

    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:

    53399993399993399992399992

    The file doesn't empty itself.

    Run DBCC LOGINFO:

    332767344648192941712640

    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

  • 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

  • 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

  • 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

  • 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