Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
hmat911
Starting Member
8 Posts |
 Posted - 2007-04-05 : 19:34:01
|
| Have a Production server with the "publisher" database and another server (data warehouse) with the subscriber database. Occasionally we need to restore the Production database to the Training server so we have a duplicate database on Training for people to mess with. Once restored to Training, the transaction log continues to grow and will not truncate. The error message states there are transactions at the beginning of the log pending replication... The database on the Training server is not a replicated database (not a publisher), but it was restored from one. How can I truncate the transaction log on the Training server? Or how should I back up the Production database in order to restore on Training without running into replication issues?HM at 911 |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2007-04-05 : 19:51:07
|
| Just change the recovery model to SIMPLE. You can do this through EM through the GUI or using ALTER DATABASE in Query Analyzer.Tara Kizerhttp://weblogs.sqlteam.com/tarad/ |
 |
|
|
dinakar
Master Smack Fu Yak Hacker
2507 Posts |
Posted - 2007-04-05 : 19:54:55
|
| You can also remove the replication and its relate dissues on the Training server by running a sp_removedbreplication @dbname.************************Life is short. Enjoy it.************************ |
|
|
|
hmat911
Starting Member
8 Posts |
Posted - 2007-04-06 : 17:40:27
|
| The sp_removedbreplication does not work because it doesn't think there is any replication to remove. When I change the recovery model to SIMPLE it gives me an error and says I cannot backup the transaction log.I just got it to work. I faked it out by adding a replication Publisher with no subscribers. Then when I ran sp_removedbreplication it worked and my transaction log went back down to normal (reasonable size).Thanks everyone for the ideas.HM at 911 |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2007-04-06 : 18:20:51
|
| Well of course you can't backup the transaction log when the database recovery model is set to simple. That's the whole point. Does training really require the ability to restore to a point in time? If not, then you don't need transaction log backups.Tara Kizerhttp://weblogs.sqlteam.com/tarad/ |
|
|
|
Wanderer
Master Smack Fu Yak Hacker
1168 Posts |
Posted - 2007-06-25 : 08:23:47
|
| HMat911: I've just gone through the same pain, and your 'fake publisher' worked for me as well. Database was in SIMPLE moode, but trying to truncate log failed because it had transaction it still wanted to replicate. running sp_repldone didn't woprk, because of no publisher. By doing what you recommended, i.e. setting up a simple publisher (I went snapshot, 1 table). then I was able to run sp_repldone to mark all tranactions as replicated, and then remove the replciation. That allowed me to truncate the log, and regain the space.*##* *##* *##* *##* Chaos, Disorder and Panic ... my work is done here! |
|
|
|
|
|
|
|
|
Restore from Replicated Database