SQL 2000 Removing Secondary Log File

Question

SQL 2000 Removing Secondary Log File

Alan-314321

Default port

Points: 1439
More actions
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.
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

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

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

MANU-J. SSC-Dedicated Points: 31126 More actions · Answer 1

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

Alan-314321 Default port Points: 1439 More actions · Answer 2

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

MANU-J. SSC-Dedicated Points: 31126 More actions · Answer 3

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

Alan-314321 Default port Points: 1439 More actions · Answer 4

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