Agent & Replication - missing and missed!

I can't believe this. MSDE was a great product and I'm sure that SQL Express is even better. The difference is that I can't migrate any of my company's applications to it because they rely on a Replication architecture (requiring Replication Publication). And SQL Agent, how are you supposed to keep the database in good shape if you can't run Maintenance Plans at scheduled intervals?

I know, there are third party products out there to compensate for the lack of the SQL Agent, but they don't solve the replication problem. Besides, the Agent still exists in other editions - it's not like it has been dropped completely. Why do we have to lose functionality and features? We have already lost the use of a second CPU and memory > 1GB. Why do they need to keep taking stuff out? Why don't they just limit access to the new feature-set?

I would rather lose the new features such as Report Server and Full Text Indexing and get the SQL Agent and Replication Publication back. I can almost understand that with a free product, they don't want to keep piliing new features in just to give them away - even though I'm sure the sale of the larger editions more than covers the R&D investment in their construction. Why can't we chose? Agent or Report Server, Replication or Full Text Indexing? I don't need some of these new features (others might), I just want the features that I used to have. Perhaps SQL 2005 setup can be made to choose 2 out of the 4 features?

Whats more, I can't even say that I'll just stick with MSDE, as it now has a limited support life. Microsoft announced that any SQL Server version prior to 2005 SP1 will NOT be supported on Vista or Longhorn Server (nor does it support anything less than Windows 2000 SP4 or Windows XP SP2 where MSDE does).

OK - let me get this right. We had something that has been taken away in the new version. Perhaps I don't deserve the new version if I don't pay for it - fine - I'll stick to the old one I already have. BUT wait... I can't use that on the new platforms coming out.

Are my entry-level customers going to pay for the Workgroup Edition (add AUD$1200 to my entry level edition application of AUD$1500) to replace the Replication Publisher and provide a Maintenance plan? Is anyone going to pay for the re-architecting and re-construction of my application to work around these new limitations?

I guess that will be me. Is this what happens for being loyal to MS products?

Please - give back the SQL Agent and Replication publication.

Joe

Joe, I completley agree with you, and its really sad that no one from microsoft has bothered to answer your comments.

Agent & Replication - missing and missed!

I can't believe this. MSDE was a great product and I'm sure that SQL Express is even better. The difference is that I can't migrate any of my company's applications to it because they rely on a Replication architecture (requiring Replication Publication). And SQL Agent, how are you supposed to keep the database in good shape if you can't run Maintenance Plans at scheduled intervals?

I know, there are third party products out there to compensate for the lack of the SQL Agent, but they don't solve the replication problem. Besides, the Agent still exists in other editions - it's not like it has been dropped completely. Why do we have to lose functionality and features? We have already lost the use of a second CPU and memory > 1GB. Why do they need to keep taking stuff out? Why don't they just limit access to the new feature-set?

I would rather lose the new features such as Report Server and Full Text Indexing and get the SQL Agent and Replication Publication back. I can almost understand that with a free product, they don't want to keep piliing new features in just to give them away - even though I'm sure the sale of the larger editions more than covers the R&D investment in their construction. Why can't we chose? Agent or Report Server, Replication or Full Text Indexing? I don't need some of these new features (others might), I just want the features that I used to have. Perhaps SQL 2005 setup can be made to choose 2 out of the 4 features?

Whats more, I can't even say that I'll just stick with MSDE, as it now has a limited support life. Microsoft announced that any SQL Server version prior to 2005 SP1 will NOT be supported on Vista or Longhorn Server (nor does it support anything less than Windows 2000 SP4 or Windows XP SP2 where MSDE does).

OK - let me get this right. We had something that has been taken away in the new version. Perhaps I don't deserve the new version if I don't pay for it - fine - I'll stick to the old one I already have. BUT wait... I can't use that on the new platforms coming out.

Are my entry-level customers going to pay for the Workgroup Edition (add AUD$1200 to my entry level edition application of AUD$1500) to replace the Replication Publisher and provide a Maintenance plan? Is anyone going to pay for the re-architecting and re-construction of my application to work around these new limitations?

I guess that will be me. Is this what happens for being loyal to MS products?

Please - give back the SQL Agent and Replication publication.

Joe

Joe, I completley agree with you, and its really sad that no one from microsoft has bothered to answer your comments.

After SP4, proc w. Cursor doesnt release keylocks

Existing Stored Procedure, has been running well on SQL since 7.0.
(but needed some tweaking to migrate to 2000).

Now all of a sudden after installing SP4 of SQL 2000,
this process slows down, and SQL Spotlight shows the number of locks
just climbing throughout the processing run.

According to the MS Knowledge Base Articles on KeyLocks .. this was a
problem that was *fixed* in the service pack ... where as for me it is
now broken.

Article ID: 260652
PRB: Nested Loop Join That Uses A "BOOKMARK LOOKUP ...WITH PREFETCH"
May Hold Locks Longer http://support.microsoft.com/kb/260652/
Article ID: 828096
FIX: Key Locks Are Held Until the End of the Statement for Rows That
Do Not Pass Filter Criteria http://support.microsoft.com/kb/828096/
Anybody else have this issue, or have any "eazy" solutions?

The proc cursors thru a list and runs a proc on each item in the "work
list".
This is an existing system
with no plans to turn the process into a set oriented one,
as is going away shortly.rawheiser (rawheiser@.gmail.com) writes:

Quote:

Originally Posted by

Now all of a sudden after installing SP4 of SQL 2000,
this process slows down, and SQL Spotlight shows the number of locks
just climbing throughout the processing run.
>
According to the MS Knowledge Base Articles on KeyLocks .. this was a
problem that was *fixed* in the service pack ... where as for me it is
now broken.
>
Article ID : 260652
PRB: Nested Loop Join That Uses A "BOOKMARK LOOKUP ...WITH PREFETCH"
May Hold Locks Longer http://support.microsoft.com/kb/260652/
>
Article ID : 828096
FIX: Key Locks Are Held Until the End of the Statement for Rows That
Do Not Pass Filter Criteria http://support.microsoft.com/kb/828096/
>
Anybody else have this issue, or have any "eazy" solutions?
>
The proc cursors thru a list and runs a proc on each item in the "work
list".
This is an existing system with no plans to turn the process into a set
oriented one, as is going away shortly.

It's not clear why think your problem is related to the problem discussed
in the KB article. Apparently, your procedure has a cursor, so there
might be other reasons for these locks.

Is the entire loop one transaction?

As a shot in the dark, if the cursor declaration just says:

DECLARE cur CURSOR FOR

try adding INSENSITIVE before CURSOR.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||It IS an insensitive cursor.

Looking thru DBArtisan I see the KeyLocks in its lock display window,
and in SQL Spotlight I see the number of locks jump by 1000 or so every
few seconds.

How can I monitor what procedure is locking what resource?

Other than injecting some debugging code into the procedures?|||rawheiser (rawheiser@.gmail.com) writes:

Quote:

Originally Posted by

It IS an insensitive cursor.

As I said, it was a shot in the dark.

Quote:

Originally Posted by

Looking thru DBArtisan I see the KeyLocks in its lock display window,
and in SQL Spotlight I see the number of locks jump by 1000 or so every
few seconds.

And it retains the locks?

Quote:

Originally Posted by

How can I monitor what procedure is locking what resource?

Maybe my aba_lockinfo can be of help? If your procedure releases the locks,
then you will have to get the right moment, but if it retains the locks
you will see what it is. You find aba_lockinfo at
http://www.sommarskog.se/sqlutil/aba_lockinfo.html.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Thanks for the link, I will look at it for future issues.
(and browse about your site for other goodies).

I did find the problem,
though I am not exactly sure of the mechanism involved.

The outer procedure was calling a procedure
that a procedure three levels down was also calling,
when I removed the duplicate updating,
it then flew like the wind.

I also changed a Set NoCount OFF to ON, which may have helped as well.

The proc is called by SQLAgent,
and I'm not exactly sure where all that 'client chatter'
goes to when the server is running the process.
(the NULL device?).