Msmq Missing Messages After Applying SP3a To SQL Server
Jan 12, 2004
Used to work fine:
(windows 200 server, sql server 2000, JDBC driver SP2.)
Two java processes, one sending messages (using jdbc and sql as temp storage), second is receiving and placing on msmq.
After installing SP3a for SQL server, under high load (200 a sec) there are missing messages in msmq.
Installed everything from scratch on separate machine, works fine until SP3a is applied.
Any help is highly appreciated
Hello, I am getting many of these messages in my server's event log (approximately 13 every 5 seconds or so). I have tried clearing the queue with "END CONVERSATION @ConvHandle WITH CLEANUP;" but the event log keeps getting messages. I have attached an example below.
Type: Information Event Source: MSSQLSERVER Event Category: (2) Event ID: 9724 Date: 7/10/2007 Time: 3:52:37 PM Description: The activated proc [dbo].[SqlQueryNotificationStoredProcedure-32e779eb-edcb-44d1-ba30-93f46ef9d9f8] running on queue HoudiniPlatform.dbo.SqlQueryNotificationService-32e779eb-edcb-44d1-ba30-93f46ef9d9f8 output the following: 'Could not find stored procedure 'dbo.SqlQueryNotificationStoredProcedure-32e779eb-edcb-44d1-ba30-93f46ef9d9f8'.'
I'm in the process of trying to optimize a stored procedure with many queries. The execution plan provides a missing non-clustered index on nearly every query, and they're all fairly similar. The only real difference between them are what's in the INCLUDE statement. The two key columns are listed in every missing index. Let's say each query is approximately 5% of the total batch and 90% of the queries all fall into the category I listed above. How should I go about creating the missing indexes? Create all of the missing indexes or create one generic one that has all the INCLUDE columns? Create a minimal index with just a few of the common INCLUDE columns?
Here's an example of what I'm talking about with the missing indexes:
/* USE [DB] GO CREATE NONCLUSTERED INDEX [<Name of Missing Index, sysname,>] ON [dbo].[TABLE_1] ([COLUMN_A],[COLUMN_B]) INCLUDE ([C4ABCD],[C4ARTX],[C4ASTX],[C4ADNB],[C4AFNB],[C4BKVA]) GO */ /*
The Query Processor estimates that implementing the following index could improve the query cost by 99.9044%.
*/ /* USE [DB] GO CREATE NONCLUSTERED INDEX [<Name of Missing Index, sysname,>] ON [dbo].[TABLE_1] ([COLUMN_A],[COLUMN_B]) INCLUDE ([C4ARTX],[C4ASTX],[C4ADNB],[C4CZST]) GO */
/*
The Query Processor estimates that implementing the following index could improve the query cost by 99.5418%.
*/ /* USE [DB] GO CREATE NONCLUSTERED INDEX [<Name of Missing Index, sysname,>] ON [dbo].[TABLE_1] ([COLUMN_A],[COLUMN_B]) INCLUDE ([C4ABCD],[C4ARTX],[C4ASTX],[C4ADNB],[C4AFNB],[C4BKVA]) GO */
Hi, I've downloaded the SP3a for sql server from the microsoft website. I unpackaged the files into a folder and ran the file setup.bat. I recieved an error that setupsql.exe file was not found. I think theres a folder missing called Setup under the X86 folder. Is there a way I can fix this????????
I got this message from Backup Exec for Windows Servers Environment Check 1.0 for SBS1 QUOTE
An instance of Microsoft SQL 2000 was found that is not updated to Service Pack 3a.
An instance of Microsoft SQL Server was found that is not updated to the correct service pack. Using this SQL Server instance with Backup Exec may cause errors. To install Backup Exec successfully, upgrade this SQL Server to Service Pack 3a, or select another instance.
Server : w2k server sp4 Tablet : w2k sp4 Sql Server 2000
A few days ago we upgraded our servers and tablet pc's with sql server sp3a. At the same time we also changed the publisher and it now uses a static filter to reduce the number of records in the tablet database. Since we have done this we have been getting an error message when we try to synchronize via the windows synchronization manager on our tablet pc's. The error message is "mobsync.exe has generated errors and will be closed by windows. You will need to restart the program. I looked up the error message and it is a stack overflow. This does not happen every time but about 50% of the time. We can always sync from within enterprise manager and everything works fine every time.
If any one has any ideas I would greatly appreciate it and thank you in advance.
Can anyone give me some sugguestions here.Connection is declared at the start of the applicationSet rsFZReport.ActiveConnection = connThen a function repeatly opens recordsets like this -rsFZReport.Open sRS, , adOpenStatic, adLockReadOnly, adCmdText....reporting code etc...If rsFZReport.State > 0 ThenrsFZReport.CloseSet rsFZReport= NothingendifUsing VB Watch debugger, the close and set nothing do NOT release anymemory. It just keeps increasing. The application connects to multipledatabases and many tables, so its uses about 10Mb/minute.(I don't think its of relavance but Form.show uses memory but form.unloaddoesn't release any back)I've installed SP3a and MDAC 2.8 to no avail. Anyone have any ideas or knowwhat I can do?msado27.tlb 2.80.1022.0MSSQL Server SP3a (3 has a memory leak apparently)VB6, SP6Sqlsrv32.dll caused a leak on NT4 SP6, but upgrading it on my win2k SP4server/workstation (they are both and the same) to thisSqlsrv32.dll 2000.85.1022.0doesn't help.Provider=SQLOLEDB;Data Source=theServer;Initial Catalog=theDB;UserID=uid;Password=thepwd;OPTION=3;connect timeout=240;
We just upgraded 2 sql servers from sql server 2000 sp3a to sql server 2005 build 2153, and merge rep exists between these 2 servers.
However, after sql server upgrade, we had to reinitialize merge replication and now the merge agent is reporting 2 errors
1)
Error messages:
The Merge Agent failed to upgrade triggers, metadata and stored procedures on the Subscriber to versions compatible with SQL Server 2005. Restart synchronization, and if this failure continues to occur reinitialize the subscription. (Source: MSSQL_REPL, Error number: MSSQL_REPL-2147199403) Get help: http://help/MSSQL_REPL-2147199403
Invalid column name 'metadata_select_proc'. (Source: MSSQLServer, Error number: 207) Get help: http://help/207
Invalid column name 'delete_proc'. (Source: MSSQLServer, Error number: 207) Get help: http://help/207
The merge process could not connect to the Publisher 'Server:database'. Check to ensure that the server is running. (Source: MSSQL_REPL, Error number: MSSQL_REPL-2147199368) Get help: http://help/MSSQL_REPL-2147199368
Another merge agent for the subscription(s) is running or the server is working on a previous request by the same agent. (Source: MSSQLServer, Error number: 21036) Get help: http://help/21036
looking inside the sp_MSensure_single_instance stored procedure, it's trying to obtain an exclusive lock on resource 'Merge Agent Name' but fails and returns -1
this is the stored procedure executed by sp_MSensure_single_instance and @retcode = -1 and thus cannot obtain exclusive lock on resource
so i think it's failing because for some reason MSmerge_PAL_role database role does not have enough rights to obtain exclusive lock on necessary resources
i've tried many things to fix this, including scripting out and dropping all merge rep, turning off replication db option and reapplying replication create scripts on the server, and even manually deleting the MSmerge_PAL_role database role but still doesn't work
Hi,I'm trying to install MSDE Desktop SP3a SQL server on my Windows 2000 Professional Laptop.Every time I try it nearly finishes the installation and then just rollsback.Can anyone help? Is this a known issue?Thanks in advance,Jocky
I had a question thrown at me today that I am unable to find an answer to.
Can MSMQ 2.0 and SQL Server 2000 co-habitate on the same server? That is, can they both be installed on the same server and run along side each other with a big smile on their face?
My instinct is that, "yes", they can because they are both Microsoft products, but I have not been able to find anything that confirms this. Lots of information on the web about the two being used together, but nothing about them being on the same box.
Would someone be able to confirm whether they can/cannot run side by side on the same server? If you know of a website I can look at please let me know that also.
Thanks for the help! I greatly appreciate it! -- Shava
Here's my situation:I have an ODBC DSN setup for Timberline Data (An accounting packagethat uses pervasive.sql) on my sql box. I set up a linked server usingthe supplied timberline odbc driver. I have two sql instances setup,the default instance and a named instance. On the default instance,the linked server works great no matter who is logged in using it (allauthentication is NT integrated). However on the named instance onlythe NT account that the MSSqlserver$NAMED service is logged in undercan utilize the linked server. All others get a ODBC error:"error 7399: OLE DB Provider 'MSDASQL' reported an error. AccessDenied. OLE DB error trace [OLE/DB Provider 'MSDASQL'IUnknown::QueryInterface returned 0x80070005: Access Denied.]."Far as i can tell, both instances are setup the same, except that oneis the default and one is a named instance.Why the different results for the default instance vs a named instance.Any ideas?Thanks
I've an asp.net application that generates tons of messages (a few hundreds/Second, depending on load of requests). Basically, I need to consume those messages (doesn't have to call "message" even) into sql server table. Each message being a new row in a table.
I tried a few things but failed to satisfy the performance. I tried to put the messages in MSMQ and then have either the MSMQ trigger to process the message or Windows C# service to process message. The performance is horrible.
I'm thinking to send messages directly to SSBQueue and have sql server process it. Is there any async way of doing this so the performance would not be a problem? Anyway I can use WCF + SSB to solve this problem?
Can anybody suggest better approach? Buying a decent hardware or SQL Server 2005 is not a problem.
Rushi, if you are reading this post, sorry for the repeatation, I know I emailed you a few hours ago...
I am DBA and have worked for 3 years on sql Server 7 production servers. I briefly looked at SQL 2000 on desktop edition.
Should I be worried about applying for dba positions which have SQL 2000 production server environment. Companies will ask how much SQL 2000 experiance I have. I feel a little nervous as I have not the exposure to a live SQL 2000 production server environment. From what I have seen the bread and butter DBA tasks have not changed between sql 7 and sql 2000. I am hoping to get the opinion of fellow DBA's on this subject. Thanks Pargat
I am DBA and have worked for 3 years on sql Server 7 production servers. I briefly looked at SQL 2000 on desktop edition.
Should I be worried about applying for dba positions which have SQL 2000 production server environment. Companies will ask how much SQL 2000 experiance I have. I feel a little nervous as I have not the exposure to a live SQL 2000 production server environment. From what I have seen the bread and butter DBA tasks have not changed between sql 7 and sql 2000. I am hoping to get the opinion of fellow DBA's on this subject. Thanks Pargat
Here i am back with an inquiry about the last process of Merge Replication between SQL Mobile 2005 and SQL Server 2005.
Well i have performed all the steps found in the tutorial of the SQL Mobile Edition Books Online, and everything went on so fine and smooth. Now when i deploy my application on the Pocket PC emulator all possible SQL queries work and modify the data only in the emulator, i had installed ActiveSync 4.0 and it synchronizes the SQL Mobile data with the SQL Mobile server only when i copy the database from the emulator to my desktop, and this way is not efficient to my project, i need a better way to synchronize the data between the database on the emulator and that on the SQL Mobile Server.
I'll be looking forward to getting your help as soon as possible.
I have an interesting situation here. I have a SQL Server 2000 database which is attached to SQL Server 2005. The database (SQL 2000) however only has service pack 3 applied. I need to apply SP4 before I can move forward with SQL 2000->2005 upgrade. The question: can I somehow apply this support package with my current configuration or should I install full SQL 2000 front-end anew (which I don't have at the moment)? Whenever I try to run SQL 2000 SP4 it complains "SQL Server 2000 is not installed on this machine."
The server I try to install the update is Windows Server 2003 R2 Standard Edition x64. SQL Server 2000 database itself is x86 (obviously).
I'm in the process of doing the initial research for the architecture of a large scale, transactional messages routing platform.
My initial design called for a series of MSMQ queues and Windows Services, written in C#, to process the messages in these queues. There will be incoming and outgoing queues, queues to store unroutable messages, etc.
My application will be routing many hundreds of thousands (and eventually millions) of messages per day. These message are very small (< 200 bytes each) and must be routed very quickly. (<1 second processing overhead per message for high priority messages.)
Using the term "routing" may be a bit misleading. The messages arrive via TCP socket connections. I will just need to take in a message, examine its intended destination, and send it to one of several outgoing socket connections, likely on different machines. Some messages require higher priority routing than others, but I don't need multi-hop routing or anything like that.
Of great concern to me is that there are absolutely no single points of failure in the system. Because of this I was considering using a combination of MSMQ and Windows Services in a Clustered environment.
Can the Service Broker provide me with this kind of functionality? If so, how well does it perform and scale? Is it a better choice for messaging applications that require high transactional throughput than MSMQ?
I'm just trying to get an idea of what products/services I should look into further.
I've been trying different things to "READ" the recordset from the "Message Queue". I can read it but with some weird characters. I've tried
ActiveXMessageFormatter
BinaryMessageFormatter
XMLMessageFormatter
So, far I have no luck.
MessageQueue msgQ3 = new MessageQueue("SERVERNM\" + msg.Label, false);
Message msg3 = new Message();
msg3.Formatter = new ActiveXMessageFormatter(); msg3 = msgQ3.Receive(new TimeSpan(0, 0, 30));
byte[] b = new byte[msg3.BodyStream.Length]; msg3.BodyStream.Read(b, 0, (int)msg3.BodyStream.Length); System.Text.ASCIIEncoding enc = new System.Text.ASCIIEncoding();
returnVal = enc.GetString(b);
RESULTS: I try to convert it to String and then deserialize later but I'm getting some junk. <?xml version="1.0" encoding="utf-8" ?>
Can anyone direct me to a code that would automatically apply transaction logs to a DB in the standby server? We have a process that dumps the transaction log backup from the primary server into the backup server every hour on the hour, but I need to apply that transaction log as soon as it is in the standby server.
I am sure someone will ask, why not do transactional replication or log shipping? My answer to that is I have yet to learn how to setup replication between servers. I need to get our backup server up and running in the next few days.
I have a SQL Server at version Microsoft SQL Server 2012 - 11.0.5343.0 (X64)
May 4 2015 19:11:32 Copyright (c) Microsoft Corporation Enterprise Edition (64-bit) on Windows NT 6.3 <X64> (Build 9600: ) (Hypervisor).
I need to apply a cumulative update, either CU2 or CU3 for a third party tool used for monitoring to work. When I try to apply CU2 or any subsequent CU there is nothing that need the update so I cannot advance the version to 11.00.5548.
I want to Export a List of the Public Queues using the command prompt. Can this be done. I don't want to always click the Export List button. I eventually want to automate the Export List so I can create an Environment HealthCheck.
Firstly the disclaimer: im a total SSIS bunny. Ok, with that out of the way... What i want to do is have data read from an MSMQ message (which will be a simple XML message) and have it transformed and inserted into a simple table. Sound simple, but i cant work out how to do it.
I started a new Integration Services Project and dropped a Data Flow task on the Control flow tab, then double-click it to go to the data flow tab. I have added a MSMQ connection manager which points at my private queue and i can hit the test button and it sees it tests ok. How do i wire that into my data flow?
On my dataflow tab i have a SQL Server Destination (set to the local SQl server instance) and ive been trying a DataReader source and try setting the Advanced Editor "Connection Manager" column on IDBConnection row to point to my MSMQ connection manager. I get some error "cannot aquire a managed connection from the run-time connection manager". How do i get a Data Flow source which reads the XML message off my MSMQ connection manager??
On the Control Flow tab you can add a Message Queue Task and i can bind it to my MSMQ connection manager and set it up as a recieve task etc, but how do i use that?
I'm working on the design of a system that will have two queues. The first queue will take requests to do something. The second queue will hold the results of having done something. Both queues won't necessarily be on the same physical server. Clients reading/writing to the queue will not be using SQL server. Web service/WCF interfaces will be developed to interrogate/manipulate queues.
My question is: which is better - MSMQ or Service Broker; if I want reliable queues. I can affort to loose items off the first queue, but cannot afford to loose any result data on the second queue.
I have an SSIS package which writes to a configured public MSMQ. When run directly through Visual Studio, file system or msdb it works fine (I guess these use me as the user). However, when I schedule it through an SQL Agent Job it fails with the following:
Failed to write message ... to the queue.
This seems like a permissions thing to me, but I've tried giving full control to everyone in the MSMQ permissions, also explicitly giving full control to the user that the agent job executes as. Neither made a difference.
Any help would be greatly appreciated. Cheers, Tony Vaughan.