SQL Database Replication : Repairing

SQL Database Replication : Repairing

I recently came across an issue where SQL database replication was failing between two nodes. The replication queue was full of un-replicated transactions,I could verify this looking at the following tables:

  • MSrepl_queuedtraninfo
  • MSreplication_queue

In this case there was over 8 million unreplicated transactions. Due to the shear number of unreplicated transactions I decided that it would probably be best to simply re-seed the database on the remote node. The process below outlines how this was achieved.

Please note that this process requires permissions to be reset on the subscriber once completed.

1. Perform backup of publisher (COMMS-S01) and subscriber (COMMS-M01) database.
2. Stop OTS (stop OTS service on COMMS-TRANS01/02 and kill any OTS.exe processes via task manager)
3. Stop the log reader and distribution replication agents.
4. Use the following commands to flush the replication queue: [less than one minute to run]
a. EXEC sp_repldone @xactid = NULL, @xact_segno = NULL, @numtrans = 0, @time = 0, @reset = 1
b. EXEC sp_replflush
5. Verify that the queue is now empty on the subscriber: [less than one minute to run]
a. EXEC sp_replshowcmds
b. DBCC opentrans
6. Now un-publish and truncate the transaction log on the publisher (comms-s01):
7. Finally shrink the database on both comms-s01:
8. Drop the OTSTrack database on COMMS-M01 (standby)- this will be recreated during replication setup.
9. Check the rowcount of the following tables in the distribution database (should not be 8 million):
a. MSrepl_queuedtraninfo
b. MSreplication_queue
10. Re-republish the database and configure sunscriptions as per GL documentation.
11. Check that replication agents are started, if not start log reader first then distribution agent.
12. Re-create user accounts on COMMS-M01 using attached ‘script.’
13. Run Stored Procedure Permissions Script on subscriber (comms-s01)
14. Start OTS (start OTS service on COMMS-TRANS01/02)

Citrix : Enable Session Time Zone Redirection

Citrix : Enable Session Time Zone Redirection

It may be that like me, you have Citrix servers in one time zone and users in another. It is possible to configure per-session time zones based upon the connecting client device time zone. I have tested this with XenApp 5.0 running on Windows Server 2003 R2 x64 SP2, with clients connecting via Citrix Online client v10 & 11, RDP and Itium thin client devices.

First you need to add the following registry entries on each server:

Windows Registry Editor Version 5.00

“Flag Data Type”=dword:00000004
“Settings Data Type”=dword:00000001

Next, in Group Policy enable Time Zone redirection:

Computer Configuration >  Windows Components > Terminal Services > Cliet / Server Data Redirection > Allow Time Zone Redirection > Set to Enabled

Finally, in a Citrix policy:

HDX Plug and Play >  Time Zones > DoNot Use Clients’ Local Time >Disabled

This requires no reboots of any kind, a new session logon should show that the redirectionis working.

Windows 2008 / 7: McAfee AV Auto Updates Fail

Windows 2008 R2 / 7: McAfee AV Auto Updates Fail

You may notice that automatic DAT updates, and patches fail to apply on Windows 7 and 2008 R2 systems. This is a known issue and is resolved using the process outlined in the following McAfee KB article:


By redefining the deployment task you add support for Winodws NT6.1 (Windows 7)