In running some performance tests on a Queue using a message size of ~5KB, we found that we can process (SEND and RECEIVE) on the order of 600 - 800 messages / second. However, we have found that INSERTs of new messages to the Queue appear to take great precedence over DELETEs of received messages from the queue. In particular, we found that during heavy use the total size of the Queue (as determined using the sp_spaceused procedure) equals about the number of total messages processed, not the number of messages on the queue.
When we stop sending messages, the overall size of the Queue table appears to decrease slowly, so there is a background process that is obviously doing some work there to clean up the received messages from the Queue. What I would like to know is if we can affect that background process in any way so that the messages are cleared out more quickly. The performance has been determined to suffer appreciably once the Queue size grows to greater than about 3GB in size. We also notice timeouts on the RECEIVE statements when the Queue size is that large.
I need to determine the actual date/time that a message was placed on the queue. In my "activated" procedure I want to log this information and pass it along to further processing routines. From what I can tell, the Queue table itself does not have this information captured.
Hello, This is info that I am still not certain about and I just need to make sure, my gut feeling is correct:
A. When a procedure is triggered upon reception of a message in a queue, what happens when the procedure fails and rolls back? 1. Message is left on the Queue. 2. is the worker procedure triggered again for the same message by the queue? 3. I am hoping the Queue keeps on triggering workers until it is empty.
My scenario is that my queue reader procedure only reads one message at a time, thus I do not loop to receive many messages.
B. For my scenario messages are independent and ordering does not matter. Thus I want to ensure my Queue reader procedures execute simultaneously. Is reading the Top message in one reader somehow blocking the queue for any other reader procedures? I.e. if I have BEGIN TRANSACTION when reading messages of the Queue, is that effectively going prevent many reader procedures working simultaneously. Again, I want to ensure that Service broker is effectively spawning procedures that work simultaneously.
I had a very recent problem where an excess number of mail records were queued. I ran sysmail_delete_mailitems_sp and deleted them from the InternalMailQueue, but it looks like they still exist in the ExternalMailQueue as I keep getting messages in the log similar to "Mail Id 2277459 has been deleted from sysmail_mailitems table. This mail will not be sent". The MailItem_Id keeps incrementing so it looks like it's working its way through. The problem is we can't use it to sent current mail until it gets through about another 3 million records. If there is some way to delete those records from the External Mail Queue or fast-forward the system to the current records that would be very helpful. Any ideas?
what i want is that everytime i add something to my sell table the stocks table deletes here is the file i want that everytime i buy something the stocks would lessen..
Is there any possible way to make a row delete itself at a certain date and time? I am tring to make an "On_Sale" table and perticular items(rows) must expire at a certain Date and time. I have an idea to delete a row when it expires but it will cause an extra burden on my application. So is it possible to have SQL responsible for deleting a row at a certain time and date?
Also, I have another question: when I asign a PK Identity to a certain column is there a way I can enforce consecutive order of PK values? For example if I delete a row and the PK value was 5 it will reoder the whole table so all PK values are in consecutive order:
Hi: I have 3 tables namely: 1 Category(CategoryID(int), CategoryName(varchar), 2 SubCategory( CategoryID(int),SubcategoryID(int),SubcategoryName) 3 Productlist (ProductID(int),ProductName(varchar),CategoryID(int), CategoryName(varchar),SubcategoryID(int),SubcategoryName(varchar)) how to delete correspoding subcategories of category from SubCategory,Productlist tables using triggers Ex: Category :TV Subcategory:ColorTV,Plasma,LCD...Plz Send me the query.... Thanks
I want to delete my record after ten days of the Posted date(field)..how do I do it? I'm able to insert date(short date , long time) to the database. I'm using sql server and C#. this is what I have so far.I would appreciate your help..
When running the following SQL statements, I get the same results.Though I need to count only -30 days. Both statements below alsoconsider the time of the day as well, which is not desiredDELETE FROM MNT_RWHERE MNT_R.TIMESTAMP < GETDATE()- 30DELETE FROM MNT_RWHERE MNT_R.TIMESTAMP < DATEADD(d, -30, GETDATE())Here is the format of the values in columnMNT_R.TIMESTAMP2005-08-09 06:06:44.5772005-08-09 06:06:46.8102005-08-09 06:06:49.060So, since data are inserted into the MNT_R table every few seconds, mydelete statement will delete different number of rows, according to thetime of the day it runs.Can you please post a SQL query that will not give me this headache?thanx a lot all
I have the following scenario : CustomerDetail customerid customername status app_no
[status = 0 means customer virtually deleted]
CustomerArchive archiveno [autoincrement] customerid customername status
At the end of the month, I have to physically delete customers. I have written two stored procs:
proc1 create proc spoc_startdeletion as declare @app_no int select @app_no = (select app_no from customerdetail where status=0) EXEC spoc_insertcustomerarchive @app_no -- After transferrin, physically delete delete from customerdetail where status=0
proc2 create proc spoc_insertcustomerarchive @app_no int as insert into customerarchive(customerid,customername,status) select customerid,customername,status from customerdetail where app_no = @app_no
It works fine if there is only one row with status=0, however the problem is that when there are multiple rows in customerdetail with status=0, it returns 'Subquery returned more than one value'
How can i transfer multiple rows one by one from the customerdetail to customerarchive and then delete the rows once they are transferred.
i've read the transact-sql command, i known that the select command use to retrieve many fields from many tables with one command select * from table1,table2 yes, but i ' ve not seen the way to add,delete or update those fields from those tables with one command...
Is it possible? why? I don't have any idea , can u help me I want to know the sql commands , if it's possible
I am struggling figuring out the token from a CMDEXEC job (as opposed to TSQL Job). It is not an option to execute the command by enabling the executing CMDs via TSQL, which is why I am using the agent. I have seen the Microsoft Site on tokens but all examples seem to be oriented to TSQL Job Type.
I am trying to delete a particular trace file and at same time keeping the SQL Directory dynamic.Taking it a step further is adding in "deleting if file exist".
I have some simple files but they are failing because the delete history task is failing as it is looking for files in a non existent directory.
It is looking for files in C:Program FilesMicrosoft SQL ServerMSSQL10_50.INSTANCEMSSQLLog whereas it should be looking in C:Program FilesMicrosoft SQL ServerMSSQL10_50.MSSQLSERVERMSSQLLog
how I can get this corrected so I can get the Maintenance Plans to run correctly.
I have tried deleting and recreating the Plan but to no avail
I am using Master Data Service for couple of months now. I can load, update, merge and soft delete data in MDS. Occasionally we even have to hard delete data from MDS. If we keep on soft deleting records in a MDS table eventually there will be huge number of soft deleted records. Is there an easy way to hard delete all the soft deleted records from all MDS tables in a specific Model.
Background: Am working on completing an ORM that can not only handles CRUD actions -- but that can also updates the structure of a table transparently when the class defs change. Reason for this is that I can't get the SQL scripts that would work for updating a software on SqlServer to be portable to other DBMS systems. Doing it by code, rather than SQL batch has a chance of making cross-platform, updateable, software...
Anyway, because it needs to be cross-DBMS capable, the constraints are that the system used must work for the lowest common denominator....ie, a 'recipe' of steps that will work on all DBMS's.
The Problem: There might be simpler ways to do this with SqlServer (all ears :-) - just in case I can't make it cross platform right now) but, with simplistic DBMS's (SqlLite, etc) there is no way to ALTER table once formed: one has to COPY the Table to a new TMP name, adding a Column in the process, then delete the original, then rename the TMP to the original name.
This appears possible in SqlServer too --...as long as there are no CASCADE operations. Truncate table doesn't seem to be the solution, nor drop, as they all seem to trigger a Cascade delete in the Foreign Table.
So -- please correct me if I am wrong here -- it appears that the operations would be along the lines of: a) Remove the Foreign Key references b) Copy the table structure, and make a new temp table, adding the column c) Copy the data over d) Add the FK relations, that used to be in the first table, to the new table e) Delete the original f) Done?
The questions are: a) How does one alter a table to REMOVE the Foreign Key References part, if it has no 'name'. b) Anyone know of a good clean way to get, and save these constraints to reapply them to the new table. Hopefully with some cross platform ADO.NET solution? GetSchema etc appears to me to be very dbms dependant? c) ANY and all tips on things I might run into later that I have not mentioned, are also greatly appreciated.
I am having great difficulty with cascading deletes, delete triggers and referential integrity.
The database is in First Normal Form.
I have some tables that are child tables with two foreign keyes to two different parent tables, for example:
Table A / Table B Table C / Table D
So if I try to turn on cascading deletes for A/B, A/C, B/D and C/D relationships, I get an error that I cannot have cascading delete because it would create multiple cascade paths. I do understand why this is happening. If I delete a row in Table A, I want it to delete child rows in Table B and table C, and then child rows in table D as well. But if I delete a row in Table C, I want it to delete child rows in Table D, and if I delete a row in Table B, I want it to also delete child rows in Table D.
SQL sees this as cyclical, because if I delete a row in table A, both table B and table C would try to delete their child rows in table D.
Ok, so I thought, no biggie, I'll just use delete triggers. So I created delete triggers that will delete child rows in table B and table C when deleting a row in table A. Then I created triggers in both Table B and Table C that would delete child rows in Table D.
When I try to delete a row in table A, B or C, I get the error "Delete Statement Conflicted with COLUMN REFERENCE". This does not make sense to me, can anyone explain? I have a trigger in place that should be deleting the child rows before it attempts to delete the parent row...isn't that the whole point of delete triggers?????
This is an example of my delete trigger:
CREATE TRIGGER [DeleteA] ON A FOR DELETE AS Delete from B where MeetingID = ID; Delete from C where MeetingID = ID;
And then Table B and C both have delete triggers to delete child rows in table D. But it never gets to that point, none of the triggers execute because the above error happens first.
So if I then go into the relationships, and deselect the option for "Enforce relationship for INSERTs and UPDATEs" these triggers all work just fine. Only problem is that now I have no referential integrity and I can simply create unrestrained child rows that do not reference actual foreign keys in the parent table.
So the question is, how do I maintain referential integrity and also have the database delete child rows, keeping in mind that the cascading deletes will not work because of the multiple cascade paths (which are certainly required).
I'm trying to clean up a database design and I'm in a situation to where two tables need a FK but since it didn't exist before there are orphaned records.
Tables are:
Brokers and it's PK is BID
The 2nd table is Broker_Rates which also has a BID table.
I'm trying to figure out a t-sql statement that will parse through all the recrods in the Broker_Rates table and delete the record if there isn't a match for the BID record in the brokers table.
I know this isn't correct syntax but should hopefully clear up what I'm asking
I have a requirement where once we create a new record in a table, we submit a query to fetch some data and save it in one of the columns of the newly created record. The main requirement is that the server where we fetch the data from can be down for sometime for regular maintenance and we do not want to loose the fetch query in that process. Is there a way we can implement this? Thanks.
I was testing my error handling and purposefully failed some messages. Automatic posion message detection kicked in and disabled my queue. I tried the following, one at a time to enable it again but it doesn't work:
ALTER QUEUE MigrationQueue WITH STATUS = ON;
ALTER QUEUE MigrationQueue WITH STATUS = ON, ACTIVATION (STATUS = ON);
I would have thought the first line would've worked but I get the following when trying to receive...
The service queue "MigrationQueue" is currently disabled.
I am running a basic SSB queue setup (more or less the Hello World example)and running into the following error message:
Transaction (Process ID 120) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
At first, I thought it was because I had the Initiator and Target Services on the same Queue, but I get this error even when I separate the two Services onto two Queues. This happens when I run more than one Target application receiving messages from the Target Queue.
Does anybody have any idea what could be happening here? Am I not allowed to set up more than one receiver?
I have a queue that, after running fine for several days will mysteriously turn off. It doesn't seem to be related to a poison message because I can restart the queue and processing resumes just fine. What are all the scenarios that would cause a queue to turn itself off, so I can 1) take preemptive action to prevent it from happening in the first place and 2) respond appropriately when it occurs.
Also, how to properly setup and verify that the BROKER_QUEUE_DISABLED is working properly. This is the SQL that I have so far, but is there a more direct way to raise the event other than writing an activated stored procedure that rolls back 5 times?
I have sent messages and they are all sitting in the transmission queue with a blank status, why is service broker not trying to send them ? They are no errors in the sql log. BOL says this is blank when it has not tried to send the message ? Service broker is definately activated in the database.
How do i force sql server to send anything in the transmission que ?
Running transactional replication, dedicated server for distributor. While performance in terms of latency is excellent (usually 1 sec, almost never higher than 4) the disk queue length on the distributor is extremely high (over 6 usually). Is this typical? On any other server I would be very concerned, but cpu and memory usage are excellent and as said, latency is good. what is recommended config for distributor? others see high queue length?
I have created Queue with the following syntax. But it is not getting activated itself. What I have to do to get it activated itself, and what could be the frequency by default.
CREATE QUEUE NewCustomerQueue WITH ACTIVATION (PROCEDURE_NAME = prProcessNewCustomers, STATUS = ON, MAX_QUEUE_READERS = 1, EXECUTE AS SELF) GO
If I execute the prProcessNewCustomers procedure manually it is showing that the Queue has been activated. What change I have to make in the syntax to get it activated itself.
Actually I have two scenarios in my requirement,
1. One Queue processing immediately when it receives data (Order Processing)
2. Another Queue, Process when the server is idle i.e., off-peak time (for mailing)
(1) When I was monitoring the SystemProcessor Queue locally ( Via a term server login onto the box ) I would see a queue of 3-4. If I monitor the same parameter from a remote PC, I see a Processor Queue of 1 - why? The box had 1 GB RAM ( SQL used 500 MB and had 250 MB free according to Task manager ).
(2) I have another almost identical box that has same CPU but twice ammount of RAM ( 2 GB ) but has SystemProcessor Queue of almost 0 - why?
