sql server replication: I am new to SQL Server replication. For some reason, in Microsoft SQL
Server Management Studio (for SQL Server 2005), if I run an "alter function"
statement, "alter procedure" statement, or "alter view" statement, for an
object that is an article in a merge publication, the command never
finishes. Last night I left an "alter function" statement running, and it
was still "executing query" this morning. An "alter table table_1 add
newColumn varchar(10) null" works for a table that is an article in a merge
publication.

This issue occurs even with a SQL Server 2005 database with just one table,
one view, and one UDF. I created a merge publication that only had one
article in it -- for the UDF. When I run the following

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER FUNCTION [dbo].[UDF1] ( @param1 nvarchar(15), @param2 nvarchar(15) )
RETURNS nvarchar(46)
AS
BEGIN
RETURN @param1 + ' ' + @param2
END

it just says "Executing query..." and never finishes.

If in the publication properties I set "Replicate schema changes" to False,
then the "alter function" statement finishes.

The publication doesn't currently have any subscribers. (The same issue
occurs when there is a subscriber.)

According to http://msdn2.microsoft.com/en-us/library/ms152562(SQL.90).aspx,
tables, views, procedures, UDF's, and triggers that are in publications can
be modified using "alter". I was able to make such changes in the past when
doing some earlier testing, but now it isn't working. (I don't remember if
I used the "alter" statements then or made schema changes by some other
means.) I researched Agents for a while, brainstorming that perhaps there
were some failed jobs in a queue from the past that maybe SQL Server Agent
was trying to run. That didn't seem to be related, because I disabled all
jobs listed under SQL Server Agent / Jobs in the Object Explorer in
Microsoft SQL Server Management Studio, and the "alter function" statement
still hung.

Nels

My 2nd post in this thread isn't showing up yet in google groups, perhaps
because I attached a text file. In case it won't show up due to the
attachment, here is my 2nd post repeated (without the attachment):

I forgot to mention that I ran SQL Server Profiler while the "alter
function" statement was being processed. See attached tab-delimited text
file. The UDF included in this log is different that the one in my original
post.

Something in this file that might be useful in determining why the "alter
function" hangs:

"
The replication agent has not logged a progress message in 10 minutes. This
might indicate an unresponsive agent or high system activity. Verify that
records are being replicated to the destination and that connections to the
Subscriber, Publisher, and Distributor are still active.
"

Nels

Nels,
please run sp_who2 to see if there is any blocking involved.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)

Hi Paul.

While the "alter function" query was running I ran sp_who2. I didn't see
any values in BlkBy. There are a few rows with status = suspended,
including one with DBName = distribution. (See below.)

The number of records varied (I think at least sometimes 29, 28, and maybe
26). Here is one variation (28 rows):
1 BACKGROUND [login1] . . NULL RESOURCE
MONITOR 0 0 04/03 08:37:17
1 0
2 SUSPENDED [login1] . . NULL LOG WRITER
31 0 04/03 08:37:17
2 0
3 BACKGROUND [login1] . . NULL LAZY WRITER
31 0 04/03 08:37:17
3 0
4 BACKGROUND [login1] . . master SIGNAL
HANDLER 0 0 04/03 08:37:17
4 0
5 BACKGROUND [login1] . . NULL LOCK MONITOR
0 0 04/03 08:37:17
5 0
6 sleeping [login1] . . master TASK MANAGER
0 0 04/03 08:37:17
6 0
7 BACKGROUND [login1] . . master TRACE QUEUE
TASK 0 0 04/03 08:37:17
7 0
8 sleeping [login1] . . NULL UNKNOWN TOKEN
0 0 04/03 08:37:17
8 0
9 sleeping [login1] . . master TASK MANAGER
0 35 04/03 08:37:17
9 0
10 BACKGROUND [login1] . . master TASK MANAGER
0 0 04/03 08:37:17
10 0
11 SUSPENDED [login1] . . master CHECKPOINT
421 105 04/03 08:37:17
11 0
12 BACKGROUND [login1] . . master BRKR EVENT
HNDLR 0 33 04/03 08:37:17
12 0
13 sleeping [login1] . . master TASK MANAGER
0 0 04/03 08:37:17
13 0
14 sleeping [login1] . . master TASK MANAGER
0 0 04/03 08:37:17
14 0
15 sleeping [login1] . . master TASK MANAGER
0 0 04/03 08:37:17
15 0
16 sleeping [login1] . . master TASK MANAGER
0 0 04/03 08:37:17
16 0
17 sleeping [login1] . . master TASK MANAGER
0 0 04/03 08:37:17
17 0
18 BACKGROUND [login1] . . master BRKR TASK
0 0 04/03 08:37:17
18 0
19 BACKGROUND [login1] . . master BRKR TASK
0 0 04/03 08:37:17
19 0
20 sleeping [login1] . . master TASK MANAGER
0 0 04/03 08:37:17
20 0
51 sleeping [login2] [hostName1] . msdb
AWAITING COMMAND 15 3 04/03 08:37:56 SQLAGENT90 - Id<2660>
51 0
52 sleeping [login2] [hostName1] . msdb
AWAITING COMMAND 312 12 04/03 08:37:59 SQLAgent - Generic Refresher
52 0
53 SUSPENDED [login1] [hostName1] . distribution
WAITFOR 22438 82 04/03 08:37:59 SQLAgent - TSQL JobStep (Job
0xCBEF439F1F506E4592181E8D17881734 : Step 1) 53 0
54 sleeping [login2] [hostName1] . msdb
AWAITING COMMAND 15 0 04/03 09:20:59 SQLAgent - Alert Engine
54 0
55 sleeping [login2] [hostName1] . msdb
AWAITING COMMAND 0 72 04/03 09:20:00 SQLAgent - Job invocation engine
55 0
56 sleeping [login2] [hostName1] . master
AWAITING COMMAND 968 77 04/03 09:20:08 Microsoft SQL Server Management
Studio 56 0
57 RUNNABLE [login2] [hostName1] . [DBName1]
SELECT INTO 157 11 04/03 09:20:59 Microsoft SQL Server Management
Studio - Query 57 0
58 RUNNABLE [login2] [hostName1] . [DBName1]
SELECT 56312 97 04/03 09:20:11 Microsoft SQL Server Management
Studio - Query 58 0

Another variation (29 rows):
1 BACKGROUND [login1] . . NULL RESOURCE
MONITOR 0 0 04/03 08:37:17
1 0
2 SUSPENDED [login1] . . NULL LOG WRITER
31 0 04/03 08:37:17
2 0
3 BACKGROUND [login1] . . NULL LAZY WRITER
31 0 04/03 08:37:17
3 0
4 BACKGROUND [login1] . . master SIGNAL
HANDLER 0 0 04/03 08:37:17
4 0
5 BACKGROUND [login1] . . NULL LOCK MONITOR
0 0 04/03 08:37:17
5 0
6 sleeping [login1] . . master TASK MANAGER
0 0 04/03 08:37:17
6 0
7 BACKGROUND [login1] . . master TRACE QUEUE
TASK 0 0 04/03 08:37:17
7 0
8 sleeping [login1] . . NULL UNKNOWN TOKEN
0 0 04/03 08:37:17
8 0
9 sleeping [login1] . . master TASK MANAGER
0 35 04/03 08:37:17
9 0
10 BACKGROUND [login1] . . master TASK MANAGER
0 0 04/03 08:37:17
10 0
11 SUSPENDED [login1] . . master CHECKPOINT
437 107 04/03 08:37:17
11 0
12 BACKGROUND [login1] . . master BRKR EVENT
HNDLR 0 33 04/03 08:37:17
12 0
13 sleeping [login1] . . master TASK MANAGER
0 0 04/03 08:37:17
13 0
14 sleeping [login1] . . master TASK MANAGER
0 0 04/03 08:37:17
14 0
15 sleeping [login1] . . master TASK MANAGER
0 0 04/03 08:37:17
15 0
16 sleeping [login1] . . master TASK MANAGER
0 0 04/03 08:37:17
16 0
17 sleeping [login1] . . master TASK MANAGER
0 0 04/03 08:37:17
17 0
18 BACKGROUND [login1] . . master BRKR TASK
0 0 04/03 08:37:17
18 0
19 BACKGROUND [login1] . . master BRKR TASK
0 0 04/03 08:37:17
19 0
20 sleeping [login1] . . master TASK MANAGER
0 0 04/03 08:37:17
20 0
21 sleeping [login1] . . master TASK MANAGER
0 0 04/03 08:37:17
21 0
51 sleeping [login2] [hostName1] . msdb
AWAITING COMMAND 15 3 04/03 08:37:56 SQLAGENT90 - Id<2660>
51 0
52 sleeping [login2] [hostName1] . msdb
AWAITING COMMAND 312 12 04/03 08:37:59 SQLAgent - Generic Refresher
52 0
53 SUSPENDED [login1] [hostName1] . distribution
WAITFOR 23219 82 04/03 08:37:59 SQLAgent - TSQL JobStep (Job
0xCBEF439F1F506E4592181E8D17881734 : Step 1) 53 0
54 sleeping [login2] [hostName1] . msdb
AWAITING COMMAND 15 0 04/03 09:21:59 SQLAgent - Alert Engine
54 0
55 sleeping [login2] [hostName1] . msdb
AWAITING COMMAND 0 72 04/03 09:20:00 SQLAgent - Job invocation engine
55 0

I ran "dbcc inputbuffer" for the entries in sp_who2 with a CPUTime > 0. For
spid 53

(53 SUSPENDED [login1] [hostName1] . distribution
WAITFOR 54828 106 04/03 08:37:59 SQLAgent - TSQL JobStep (Job
0xCBEF439F1F506E4592181E8D17881734 : Step 1) 53 0 )

dbcc inputbuffer(53) returned

Language Event 0 exec dbo.sp_replmonitorrefreshjob

I cancelled the "alter function" query and then manually ran "exec
dbo.sp_replmonitorrefreshjob", which hangs ("executing query..."). So I'm
guessing that this is related to the issue.

Nels

Nels,
to be honest, I'd be thinking at this stage of opening a support case. You
could try "kill 53" (assuming it's still the same spid) and then try
altering the function, but it sounds like the problem isn't blocking and is
something more fundamental.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)