The attempt I tried was looking up an existing dialog in the conversation_endpoints.
However on doing a scale test I would that the non blocking I was hoping wasn't happening. Even through I was giving each spid a new dialog by using a conversation_group_id related to the spid. I found that the following SQL was blocked by a transaction that contains a begin dialog. This suggests the locking on conversation_endpoints is too excessive.
select top 1 conversation_handle
from sys.conversation_endpoints ce
join sys.services s on s.service_id = ce.service_id
join sys.service_contracts c on c.service_contract_id = ce.service_contract_id
where s.name = 'jobStats'
and ce.far_service = 'jobStats'
and (ce.far_broker_instance = @targetBroker OR @targetBroker = 'CURRENT DATABASE')
and ce.state IN ('SO','CO')
and ce.is_initiator = 1
and (ce.conversation_group_id = @conversation_group_id )--or @conversation_group_id is null)
After lots of testing I have found that re-using dialogs improves performance significantly, however I cannot fathom out what is the cost of re-using them. Performance does not come for free hene why I feel there is a cost. Can anyone shed any light on the cost of re-using dialogs? Also why should you re-cycle them? What benefits does recycling dialogs have?
SInce day one i have configuring service broker to use transport and dialog security with certificates.
However i am not 100% sure how to setup service broker between 2 instances without security.
Would the following work, or what steps am i missing?
1.Setup the endpoint with WINDOWS authentication instaed fo certificates, the instances are in the same domain, grant necessary permission to the service account for each instance.
2.Create dialogs with the create remote service bindings with the anonymous = ON and grant send to public for the services.
Would this work? Also am i correct in thinking that if encryption is required the use of certificates is mandatory?
Hi all, I am accessing one database a bunch of different times all throughout my code...in various functions and different web pages. Is there a a way to create an sqlconnection that I can access all the time, instead of constanting hardcoding which database to go to? I've tried putting the info in another file and just including it where I want the database to open, but I can't use <!-- #INCLUDE --> inside of the server scripts. Can anyone help
Every time my asp.net app needs to open a connection, it tries to establish a new connection with the mssql server. I´ve already set the max pool size property in the connection string. After that, my app raises an "time out"error saying it couldn´t obtain a connection from the pool. The problem is that I have a lot of iddle connections. With the Enterprise Manager I can see the status of the connections. They´re all the same "awaiting command". How can I reuse this connections? I know that the connection string must be the same for all connections and it is. I´ve set it in the web.config file. If I remove the max pool size property from the connection string I get a lot, I mean A LOT of connections with the sql server. Any ideas?
I want to open and close sql connection only once and want to use in every function without open or close this connection in class file in asp.net 2003 . how can it possible .
I have this SP below, and I am trying to reuse the value returned by the Dateofplanningdate column so that I don't have to enter the code for each additional column I create. I have tried temp tables and derived tables with no luck.
In the Package configurations wizard, I am trying to edit an existing configuration using the edit button. In the Configuration Filter, I get the list of several filters (the filters which were used for other packages). Whe I try to reuse an used filter, it is forcing me to set a new value and when I go back to SQL Server tables , I see the old value has got erased.
Can I not use an existing filter?. Do I need to use new filters for every new package?.
I'm trying to test some queries in SQL analyser without reusing the query plan (already cached). I know that there is a way to avoid that but I don't remember right now. Another option would be to restart MS SQL service but I don't want to do that. Any thoughts...?
I have a replicated table that has a trigger attached to the it. The trigger fires off a service broker message for inserts. Originally for every insert, I would begin a conversation, send, and end the conversation when target send an end conversation. Since replication process is only using a single spid, I would like to reuse 1 conversation. the following is what I have for the send procedure in the initiator. I check the conversation_endpoints for any open conversation, if it's null, I start a new conversation and send else just send with the existing conversation. Is there anything wrong with this code? What could cause the conversation on the initiator to be null if I never end the conversation on the initiator side? thanks
DECLARE @dialog_handle uniqueidentifier
select @dialog_handle = conversation_handle from sys.conversation_endpoints where state = 'CO'
Is it possible to reuse a Lookup component which is configured with Full chaching?
My requirement is as follows....
A input file have 2 columns called CurrentLocation and PreviousLocation. In the dataflow, values of these two columns needs to be replaced with values from a look up table called "Location".
In my package i have added two LookUp components which replaces values of CurrentLocation and PreviousLocation with the values available in the table "Location". Is there any way to reuse the cache of first lookup component for second column also?
Hi,I'm constructing a query that will performs a lot o datetimecalculumns to generate columns.All that operations are dependent of a base calculum that is performedon the query and its result is stored in a columna returned.I wanna find a way of reusing this generated column, to avoidreprocessing that calculumn to perform the other operations, causethat query will be used in a critical application, and all saving isfew.Thanks a lot.
Hi! I'm wondering why is my sys.conversation_endpoints table inserting a new row for each message i send even when i reuse conversations? when i send the first message i get the first row in the sys.conversation_endpoints with a uniqueidentifier for the conversation_handle. this uniqueidentifier is then saved in the table which i query the next time i send a message to reuse the dialog conversation. But even though it looks like the uniqueidentifier is reused i still get a new row for every message i send with a different conversation_handle? this happens in both target and initator db.
I've tried to understand this by i don't.
Also for the moment i don't end conversations. But as i understand it this shouldn't matter.
Also the message successfully arives to the target and sys.transmission_queue is empty in both databases. Neither queues have any error messages in them.
I currently have multiple (parent and child) packages using the same config file. The config file has entries for connections to a number of systems. All of them are not used from the child packages. Hence, my child package throws an error when it tries to configure using the same config file because it can't find the extra connections in my connection collection.
Does anyone have any ideas on the best way to go about resolving this? Is multiple config files (one for each connection) the only way?
I have a real heartache with runtime parameter interogation on my DB. Sure I get the latest and greatest and sure I don't have to type in all those lovely parameter types..but...the hit I take on performance for making no less then 3 DB hits for each SqlAdapter is unreasonable!
So ...I like the idea of maybe calling it once for all my stored procs on application startup...and then maybe saving this in CacheObject.
My problem is that I can't see where you can even serialize a SqlParametersCollection or even for that matter assign it to a Command object. Can you cache a command object ?
LOL
I think I may just have to write some generic routine for creating and populating my command objects based on a key (type) and then use that to fetch my command.Update, command.Insert and command.
I would like to use the new AsynchBlock to do the fetching of the stored proc parameters and then just pull them from the Cache object....put a file watch so that if the DB's change my params it re-pulls them again.
*nice*.....
Then I get the best of both worlds...caching...and no parameter writing...
One sql transaction is in rollback state and optimization job is trying to restructure index on the same table that this transaction is using. As a result, transaction is in 'blocking' state and optimization job is in 'blocked' state. 'Kill process' doesn't do anything. How can this situation be resolved?
Is it possible that blocking can occur between a select and update statements running at the same time on the same object??thanks for the reply! Sheila.
Anyone out there having "self blocking" issues since upgrading to SP4 on SQL 2000? Enabing trace flag 9059 has not helped, is this a parallelism issue ?
INSERT INTO #XLAT_BCP_Results(Col1) Exec(@Command_String)
MOVE DATA FILE TO ARCHIVE COMMIT TRAN ANOTHER FETCH
A spid is launched to do the bcp...I have 4 files...on the last load The connection Pooling lauched spid gets blocked by the sa spid that launched the sproc...it doesn't happen all the time, just most of the time....
I've put a trace on in Profiler, but I don't see anything...I've picked the event class of deadlocks...but I never see it...
Hi just read an article on the internet that states
"A quick way to resolve a blocking problem is to disconnect the connection blocking the rest of the users. All you have to do is ask your database administrator to execute the KILL statement. Obviously, this is not a permanent solution, and won't really help anything except take care of an immediate need."
Now when i run sp_who ... i see that the spid that is blocking is the same as the spid itself....
Now the above article states that killing the one that is blocking the rest of the other spids is ok ...
But what if its the same SPID that is blocking ...what does it signify & whats the solution
I am running a simple SELECT statement on my TempDB
SELECT * FROM TempDB..SysObjects
When I run this , it's getting blocked by some other Process which is not at all relevant to the above Qry. First it gets blocked by one Stored Proc say Proc1 ( The Proc1 is just a SELECT statement on a table which just has four rows ). So I just kill this PROC1 and again its gets blocked by some other stored Proc PROC2 which again is a simple stored Proc and it runs in not time.
hi,I am using SQL SERVER 2000.Problem that i am facing is when ever I check locks in EnterpriseManager I findfollowing blocking - :1) sp_cursoropen;12) sp_cursorclose;13) sp_cursorfetch;1We dont have any control over application(source code).Whateverwe have to do, has to done from database side.can anyone guide me to solve this problem.Thanks
I have one batch jobs running 4 time a day and web application. We have hosted the site long time already but this month we are facing the store procedure blocked problem. The problem is that when user submit the form when during the execution of batch job, two storeprocedure are blocked. One store procedure that is using from batch job read the table "A" and another store procedure that is using from web app update the table "A". Then deadlock is occure. but i don't understand it is hosted long time already. Please let me know if you know how to solve this issue. Thanks, Aung
I have a powerbuilder application that is connecting to SQL Server. We get very frequent DeadLock situations. Is there a way that I can check to see as what SQL is causing the Lock?
I have upgraded a MS SQL database from 6.5 to 7.0. The database functioned fine in 6.5, now I have a table that is locking due to a blocking process. If I kill the process all is fine, but am trying to determine what is causing the process to hang. Has anyone experience any similar situations.
In the morning at 9:32 one of the spid was blocked and got time out msg for select statement. the statement was sp_execute wht is sp_execute? can anyone guide me?
I've a problems with blocking simple select * from table1 is blocked how to know which statement is blocking select * from table1 is spid 30 blocked by spid 26 and in current activity spid 26 has no select or any statement how could i know the spid 26 statement ? And can just select statement be blocked?
I know that spid 22 is blocking 2 other users. How do I tell who that user is? Is this just matching the same spid# in the process info in EM and if it doesn't give me an NT name it's probably because they're coming through the application and I can't figure it out?
I've a encountering a problem with "Blocking" and would highly appreciatd, if anyone out there please advice how to get to the bottom of this. the problem in as follows:
Every now and then, my SQL Server Freezes and when i look at the process info, i see some user connections in blocked mode and some in blocking. i dont know, where to start looking for. my first question would be, what should I do here? Should i kill the blocked user or blocking user? Also, Whats the difference between this two modes (blocked and blocking) and why does this happening? can i trace the user id which is causing this problem?
Experts please advice, what to do in the near future to aviod this.
I search at microsoft support site and came up nothing good about this problem.
When an SA attempts to change permission on any object they are shown as blocked as expected. However as new users access the object the SA's process is continuely blocked until the object is free. Can anyone explain this??
