I have a merge publication that I always create with a script but has now stopped working with what appears to be a complete server problem.

The actual command i am executing is a simple

sp_addpublication_snapshot

but it fails with

Msg 50000, Level 16, State 1, Procedure sp_add_jobstep_internal, Line 253

The current transaction cannot be committed and cannot support operations that write to the log file. Roll back the transaction.

Msg 3931, Level 16, State 1, Procedure sp_MSadd_repl_job_unsafe, Line 376

The current transaction cannot be committed and cannot be rolled back to a savepoint. Roll back the entire transaction.

I have tried recreating the database from scratch, renaming it, renaming the publication, even disabling the publications altogether but it makes no difference. There is nothing in the sql or event logs and I cant do any work until Ive got past it - help!

I am using SQL 2005 SP2 if that makes any difference.

Thanks

Andy

It looks like you have an uncommitted transaction hanging around. Open a query window and type:

Commit Tran

GO

It will either tell you that there are no transactions to commit or commit the uncomplted transaction. At that point, re-run your script.

Hope this helps...

Scott

I am experiencing a similar issue. Have you been able to resolve this?

I found the answer and it was security related, of course.

The user that is creating the agentjob must have either the "sysadmin" fixed server role or the "db_owner" fixed database role.

i encountered this issue when uninstalling database replication, but when i want to reinstall replication, snapshot agent can not be created, and gave error message above.

i excuted sp_dropdistributor, but of no avail, which is always the final kill to my thinking.

finally, i excuted sp_removedbreplication, which solved the problem.

I had the same issue. 

After changing SQL Agent's account to domain account with sql admin rights, it got resolved!

-Uday

I just restart the server, and everything works fine.