April 20, 2006 at 3:33 pm
I have a sql server 7.0 database which I have restored to a new sql server 2005 instance. (I have attempted doing the restore with the "preserve the replication settings" option checked and with it unchecked.) Every time the restore fails at 100% and then gives the error:
"An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
------------------------------
A system assertion check has failed. Check the SQL Server error log for details (Microsoft SQL Server, Error: 3624)"
The database is then shown as being offline. I can bring it online in single-user mode (compatability mode 70) and change it to multiuser and compatibility mode 90.
Once I do that I can see the system tables for distribution are still in place (MSreplication_subscriptions, sysarticles, sysarticleupdates, syspublications, sysreplservers, syssubscriptions) and populated. If I look under Replication/Local Subscriptions I can see two subscriptions associated with the recently restored database. If I try to refresh Replication/Local Publications, though, I see nothing and get this error:
"Failed to retrieve data for this request. (Microsoft.SqlServer.SmoEnum)
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
------------------------------
Invalid column name 'allow_queued_tran'.
Invalid column name 'options'. (Microsoft SQL Server, Error: 207)"
I can remove the local subscriptions, but I cannot access or delete the local publications. Nor can I add new publications to the database. If I do any intensive work in the database the transaction log fills up and cannot be shrunk or truncated and the log_reuse_wait_desc column in sys.databases shows the log is waiting on REPLICATION.
Does anyone have recommendations on how to cleanup the artifacts of replication?
April 24, 2006 at 8:00 am
This was removed by the editor as SPAM
June 22, 2006 at 5:11 am
Hade the same problem with a database.
This is how i solved my problem
1. Restored the database to a alternate sql 7 server
2. Enbled that server for replication
3. Made on publication form the database (no subscribers just created the publication)
4. Disabled replication on the server (Got a error here that i ignored)
5. Made a new backup file
Restored the new backup file to the sql 2005 without any errors and can now create publications from the database.
Hope this works for you to.
/Magnus Wiberg
June 22, 2006 at 3:18 pm
Mangus,
Thanks for your response. Earlier this week I opened a support case with Microsoft. What they ended up having me do was
1. restore the database to the sql server 2005 server (it will fail @ 100% complete).
2. bring the database online, set options to 90 compatability and multi-user.
3. run sp_dboption 'database_name','published','true'
4. run sp_repldone NULL, NULL, 0, 0, 1
5. run sp_removedbreplication
November 24, 2006 at 7:26 pm
Thanks for posting this solution. It worked well for me with a problem I was having with a sql 2k database that had been a publisher and had been restored on a new server (sql 2005) without first removing replication. For some reason the transaction log could not be shrunk. Any attempt to shrink yielded this error:
Server: Msg 9002, Level 17, State 6, Line 26
The transaction log for database 'xxxx' is full. To find out why space in the log cannot be reused,
see the log_reuse_wait_desc column in sys.databases
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply