Cannot shrink log file due to orphaned replicated transactions 3

[flstffatboy]

Hello all,

I have a SQL Server 2000 test database which is a backup and restored copy from our production database. The production database has transactional replication running on it but the test database does not.

I'm having issues with trying to shrink the transaction log for the test database. Im getting a message that replicated transactions exist in the log.

I have tried to run sp_removedbreplication on the test database and then backup and shrink the log but it will not work.

I have also tried to do a sp_detach_db, rename the log files and then tried to run a sp_attach_db but I keep getting the error below.


Server: Msg 1813, Level 16, State 2, Line 1
Could not open new database 'preproddb'. CREATE DATABASE is aborted.
Device activation error. The physical file name 'L:\Program Files\Microsoft SQL Server\MSSQL\Data\preproddb_Log.LDF' may be incorrect.
Device activation error. The physical file name 'L:\Program Files\Microsoft SQL Server\MSSQL\Data\preproddb_Log2_Log.LDF' may be incorrect.

Can you attach a SQL Server 2000 database without the log files? I thought the log files will be created if they do not exist.

Thank you in advance for your help!

FLSTF

 

[flstffatboy]

mrdenny,

I went ahead and Disabled publishing and subscriptions after I ran the sp_repldone and then the active portion of the log file disappeared. I think it worked even though I received the message in the previous response I had. I have just successfully truncated the log and everything appears to be fine.

I'm still baffled why it all happend and will continue to montor.

mrdenny and Ovatvvon, thank you very much for your help and time it was very much appreciated. Have a star each on me!

FLSTF

 

[mrdenny]

When you started the backup the log reader was probably in the middle of downloading a transaction so the read was logged but completion of the read wasn't in the log, so you needed to restart replication then remove replication in order to clear everything out and reset all the bits.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

My Blog

 

[Ovatvvon]

This is very interesting. I've learned something new, which is always good.

Thanks FLSTF for the star. And Denny, I'm giving you a star as well.


-Ovatvvon :-Q