February 25, 2012 at 8:26 am
Hi, All
I have a problem when I truncating a table at subscriber. It's no response. The sp_who2 shows it's locked by a system process:
SPIDStatusLoginHostNameBlkByDBNameCommandCPUTimeDiskIOLastBatchProgramNameSPIDREQUESTID
6 BACKGROUND sa . .masterUNKNOWN TOKEN 44796165902601/01 00:00:00 6 0
The story is: This table is a huge table (about 100 million records) in publisher, and when initialize the subscription, it always showing 'Bulk copying to table and waiting server response' in the Replication Monitor (snapshot finished). After half day, it still stuck there. Then I want to drop the replication. I've run sp_dropsubscription,sp_droppublication and sp_replicationdboption(,,false) successfully. But when I drop tables, this large table cannot be dropped successful. It's been locked by the above system process.
Has anybody know how to terminate the above system process? I think this process is the Bulk inserting that huge table session.
FYI: I'm using transaction replication. SQL Server 2005. Windows 2003 SP2.
Regards
Billy
February 25, 2012 at 6:54 pm
This morning, I can truncate this table now. I've check the sp_who2, it shows:
SPIDStatusLoginHostNameBlkByDBNameCommandCPUTimeDiskIOLastBatchProgramNameSPIDREQUESTID
6 BACKGROUND sa . .masterSIGNAL HANDLER 0002/26 03:17:05 6 0
Maybe that batch command is finished. I'll continue drop all tables to re-setup the replication. Hope everything working fine.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply