Merge replication error Msg 20092, Level 16, State 1

Recently I created a (fortunately) test environment in SQL 2005 for a merge replication that was to upgrade an existing SQL 2000 installation. I didn't want to re-initialise the subscriber as there was a large amount of data involved (about 115million rows). Once the subscriber was created (and snapshot created), I tested the merge by updating a particular set of data on the publisher first and then the subscriber to check that the replication worked. Publisher update went fine, data replicated successfully. However, when I tried to update the rows at the subscriber I got an error Msg 20092, Level 16, State 1 Procedure MSmerge_disabledml....

Table 'XXXXXX' into which you are trying to insert, update, or delete data is currently being upgraded or initialized for merge replication. On the publisher data modifications are disallowed until the upgrade completes and snapshot has successfully run. On subscriber data modifications are disallowed until the upgrade completes or the initial snapshot has been successfully applied and it has synchronized with the publisher.

Msg 3609, Level 16, State 1, Line 1

The transaction ended in the trigger. The batch has been aborted.



Fortunately the solution for this is quite straight forward. It seems that somehow during the creation of the subscriber (and I admit this was done a few times) the merge replication upgrade triggers were not removed.

To correct this scenario, you must run the following SQL on the subscriber, drop the subscription and re-create the subscription again. The script is (OK I know it uses a cursor but it's a one off fix script so who cares!):

DECLARE @sql NVARCHAR(4000),
@trigger NVARCHAR(100)

DECLARE trig_curs CURSOR for
SELECT NAME
FROM sys.triggers
WHERE name LIKE 'MSmerge%' AND PARENT_CLASS=1

OPEN trig_curs

FETCH NEXT FROM trig_curs INTO @trigger

WHILE @@FETCH_STATUS = 0
BEGIN

set @sql = 'drop trigger [' + @trigger + ']'

print @sql

exec sp_executesql @sql

FETCH NEXT FROM trig_curs INTO @trigger
END

CLOSE trig_curs
DEALLOCATE trig_curs

Deadlocks - solving

Great blog here on how to investigate the SQL causing deadlocks. Pretty comprehensive, clear steps concentrating on the information presented by SQL 2005.

Moving logins when migrating a server

This is how MS recommends migrating logins when moving a database. Very useful, and removes the need to run sp_change_users_login .

2007 best scifi stories - for free

Links to the 2007 nominated sci-fi books - all to read for free. Some good reads there and worth a visit.

Data Table Shrinking

One the the most annoying features of SQL Server is that the GUI based shrink commands rarely work. In a development environment we do not always have the space to store a database and it's predicted growth, so shrinking is a necessity.

The following script will shrink all the files in a database - data and log, and free up that much needed space. Just edit it and replace the logical file names. It will shrink any number of files - just add/remove the commands as required.

use master
go
BACKUP LOG [BSE] with TRUNCATE_ONLY
GO
DBCC SHRINKDATABASE(N'BSE')
GO
USE BSE
GO
DBCC SHRINKFILE(BSE_dat, 10)
GO
DBCC SHRINKFILE(BSE_dat2, 10)
GO
DBCC SHRINKFILE(BSE_dat3, 10)
GO
DBCC SHRINKFILE(BSE_dat4, 10)
GO
DBCC SHRINKFILE(BSE_Log, 3)
GO

Tally Table - loop replacement

Excellent article here on using Tally Tables instead of a loop. The performance increases are amazing. In essence the Tally Table is used to replace the loop, and using set based logic rather than a traditional loop gives high performance gains. Worth a read at least.

SQL String Formatting

Very nice set of functions to format strings in SQL. Have a look here for details