 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
SQL Server Forum / Other Technologies / Service Broker / August 2007Service Broker - tempdb filling up
Hi, My tempdb is filling up fast and it's coming from SPID 18 which has a command BRKR EVENT HNDLR. I believe it has something to do with service broker. Does anybody have any idea how to keep it from growing? I restart SQL Server and it just fills right back up. Many Thank! Could be a number of things but the most common is that you have an application that is not closing Service Broker dialogs correctly. See if there are a huge number of rows in sys.conversation_endpoints.  SignatureThis posting is provided "AS IS" with no warranties, and confers no rights. Use of included script samples are subject to the terms specified at http://www.microsoft.com/info/cpyright.htm > Hi, > [quoted text clipped - 4 lines] > > Many Thank! Hi Roger, Why would that fill up tempdb? How is tempdb used by SSB? SignatureAdam Machanic SQL Server MVP - http://sqlblog.com Author, "Expert SQL Server 2005 Development" http://www.apress.com/book/bookDisplay.html?bID=10220 > Could be a number of things but the most common is that you have an > application that is not closing Service Broker dialogs correctly. See if [quoted text clipped - 8 lines] >> >> Many Thank! The conversation data is cached in memory. When the memory gets too full, it overflows into tempdb. SignatureThis posting is provided "AS IS" with no warranties, and confers no rights. Use of included script samples are subject to the terms specified at http://www.microsoft.com/info/cpyright.htm > Hi Roger, > [quoted text clipped - 12 lines] >>> >>> Many Thank! > The conversation data is cached in memory. When the memory gets too full, > it overflows into tempdb. Thanks for the reply, Roger. But why spill the data to tempdb instead of simply expiring it from the cache? I assumed that SSB would simply use the buffer cache pool, like any other user data... SignatureAdam Machanic SQL Server MVP - http://sqlblog.com Author, "Expert SQL Server 2005 Development" http://www.apress.com/book/bookDisplay.html?bID=10220 Because the data records the current state of a dialog and has to be available to the system until the dialog is ended. If Service Broker needs memory, it looks for in-memory objects related to dialogs that still exist but have not been active for a while. Broker pages the objects for inactive-but-still-running dialogs to tempdb. Service Broker can't easily tell which dialogs are inactive for several days due to the design of the application and which inactive dialogs are orphans that were not properly ended by the application, so it caches all of them in tempdb. SignatureAlan Brewer [MSFT] SQL Server Documentation Team Download the latest Books Online update: http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx This posting is provided "AS IS" with no warranties, and confers no rights. Hi Alan, Thanks for the response, but I am still a bit confused. How is caching in tempdb any better than simply re-reading the data from the internal table in which it's stored? When you say "objects", are you saying that Broker creates internal objects for the dialogs, which go beyond simply reading the data? Are these objects, and SB's use of tempdb in general, documented or described somewhere? SignatureAdam Machanic SQL Server MVP - http://sqlblog.com Author, "Expert SQL Server 2005 Development" http://www.apress.com/book/bookDisplay.html?bID=10220 > Because the data records the current state of a dialog and has to be > available to the system until the dialog is ended. If Service Broker needs [quoted text clipped - 4 lines] > application and which inactive dialogs are orphans that were not properly > ended by the application, so it caches all of them in tempdb. Ah, sorry. Broker uses transmission objects (TO) to record the state of a conversation endpoint. These are in-memory objects about 1KB in size, stored in the DB Engine address space, but outside the buffer pool. Once a TO is assigned to a conversation, it remains assigned to the conversation until the conversation ends. When a conversation ends, it's TO is then available to be assigned to another conversation. So long as there is enough memory to hold all the TOs for all existing concurrent conversations, the TOs are all in memory and nothing is cached to tempdb. If, however, there are more concurrent conversations than there is available memory to store all of their TOs, then Broker starts caching the TOs for inactive conversations to tempdb. So if a system has an application that is never ending conversations, eventually there will be more TOs than can be stored in memory and Broker will start caching them in tempdb. The TOs will then stay in tempdb indefinately if the conversations are never ended. There is additional documentation on transmission objects in the Books Online for the next Katmai CTP. SignatureAlan Brewer [MSFT] SQL Server Documentation Team Download the latest Books Online update: http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx This posting is provided "AS IS" with no warranties, and confers no rights. Thanks! One final question, if you don't mind: If these things are worthwhile to cache, I assume they take some resources and/or time to create. Are there implications of a large number of active conversations for clustering and/or mirroring, in case of failover time? I assume all of the transmission objects need to be re-created on the passive/mirror node (respectively) in the case of a failover, before the system can go live again? And this might also have implications even for a simple reboot..? SignatureAdam Machanic SQL Server MVP - http://sqlblog.com Author, "Expert SQL Server 2005 Development" http://www.apress.com/book/bookDisplay.html?bID=10220 > Ah, sorry. Broker uses transmission objects (TO) to record the state of a > conversation endpoint. These are in-memory objects about 1KB in size, [quoted text clipped - 15 lines] > There is additional documentation on transmission objects in the Books > Online for the next Katmai CTP. Yes this can take a while if you have millions of active dialogs. This happens asynchronously so other than IO and processing time it doesn't have a significant impact on startup of the server but it does affect when Service Broker messages can be processed. As Alan says, you can think of this memory as the SQL OS equivalent of virtual memory where Tempdb is used like the pagefile for the Windows OS and just as with Windows, too much pagefile (tempdb) IO is a bad thing for performance. This isn't generally an issue if you have thousands to hundreds of thousands of dialogs open at a time but hundreds of millions of dialogs use a lot of resources. If you open a few hundred dialogs a second and never close them, a month down the road you have a whole lot of open dialogs. This is a pretty common issues - someone writes a Service Broker application and goes through some light testing OK then the database crawls to a halt after the application has been in production a couple months and there are hundreds of millions of dialogs open - opening 100 dialogs a second without ever ending them is half a billion dialogs after a couple months.. SignatureThis posting is provided "AS IS" with no warranties, and confers no rights. Use of included script samples are subject to the terms specified at http://www.microsoft.com/info/cpyright.htm > Thanks! One final question, if you don't mind: If these things are > worthwhile to cache, I assume they take some resources and/or time to [quoted text clipped - 24 lines] >> There is additional documentation on transmission objects in the Books >> Online for the next Katmai CTP. Thanks again for the insightful replies, Roger and Alan! SignatureAdam Machanic SQL Server MVP - http://sqlblog.com Author, "Expert SQL Server 2005 Development" http://www.apress.com/book/bookDisplay.html?bID=10220 > Yes this can take a while if you have millions of active dialogs. This > happens asynchronously so other than IO and processing time it doesn't [quoted text clipped - 41 lines] >>> There is additional documentation on transmission objects in the Books >>> Online for the next Katmai CTP. Wow! That is good to know. I guess this also means that for those of us administering SSB apps, we should jack up the size of tempdb. SignatureTom ---------------------------------------------------- Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS SQL Server MVP Toronto, ON Canada https://mvp.support.microsoft.com/profile/Tom.Moreau Yes this can take a while if you have millions of active dialogs. This happens asynchronously so other than IO and processing time it doesn't have a significant impact on startup of the server but it does affect when Service Broker messages can be processed. As Alan says, you can think of this memory as the SQL OS equivalent of virtual memory where Tempdb is used like the pagefile for the Windows OS and just as with Windows, too much pagefile (tempdb) IO is a bad thing for performance. This isn't generally an issue if you have thousands to hundreds of thousands of dialogs open at a time but hundreds of millions of dialogs use a lot of resources. If you open a few hundred dialogs a second and never close them, a month down the road you have a whole lot of open dialogs. This is a pretty common issues - someone writes a Service Broker application and goes through some light testing OK then the database crawls to a halt after the application has been in production a couple months and there are hundreds of millions of dialogs open - opening 100 dialogs a second without ever ending them is half a billion dialogs after a couple months.. SignatureThis posting is provided "AS IS" with no warranties, and confers no rights. Use of included script samples are subject to the terms specified at http://www.microsoft.com/info/cpyright.htm > Thanks! One final question, if you don't mind: If these things are > worthwhile to cache, I assume they take some resources and/or time to [quoted text clipped - 24 lines] >> There is additional documentation on transmission objects in the Books >> Online for the next Katmai CTP. Yes, even more for tempdb to do :( SignatureAdam Machanic SQL Server MVP - http://sqlblog.com Author, "Expert SQL Server 2005 Development" http://www.apress.com/book/bookDisplay.html?bID=10220 > Wow! That is good to know. I guess this also means that for those of us > administering SSB apps, we should jack up the size of tempdb. [quoted text clipped - 53 lines] >>> There is additional documentation on transmission objects in the Books >>> Online for the next Katmai CTP. It's already mentioned in the Service Broker section of the tempdb capacity planning topic: http://msdn2.microsoft.com/en-us/library/3065e567-dbeb-4770-9835-c6e1b44595b8.aspx The "1 KB" dialog objects that the topic mentions are the transmission objects. SignatureAlan Brewer [MSFT] SQL Server Documentation Team Download the latest Books Online update: http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx This posting is provided "AS IS" with no warranties, and confers no rights. Thanx, Alan! SignatureTom ---------------------------------------------------- Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS SQL Server MVP Toronto, ON Canada https://mvp.support.microsoft.com/profile/Tom.Moreau It's already mentioned in the Service Broker section of the tempdb capacity planning topic: http://msdn2.microsoft.com/en-us/library/3065e567-dbeb-4770-9835-c6e1b44595b8.aspx The "1 KB" dialog objects that the topic mentions are the transmission objects. SignatureAlan Brewer [MSFT] SQL Server Documentation Team Download the latest Books Online update: http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx This posting is provided "AS IS" with no warranties, and confers no rights. Yes, There are 30 million rows in sys.conversation endpoints, but not on this server, but on the other endpoint. Our SB application basically copies data from one database server to another. The rows are on the source database. How would I get rid of them? and what would I need to look for in my conversation to stop this from happening. Thanks! > Could be a number of things but the most common is that you have an > application that is not closing Service Broker dialogs correctly. See if [quoted text clipped - 8 lines] >> >> Many Thank! Look up END CONVERSATION in BOL. Also, you can find some information here: http://simple-talk.com/sql/learn-sql-server/service-broker-foundations-workbench /#twelvth SignatureAdam Machanic SQL Server MVP - http://sqlblog.com Author, "Expert SQL Server 2005 Development" http://www.apress.com/book/bookDisplay.html?bID=10220 > Yes, There are 30 million rows in sys.conversation endpoints, but not on > this server, but on the other endpoint. Our SB application basically [quoted text clipped - 16 lines] >>> >>> Many Thank! The Books Online also has a topic with a code example of testing for an EndDialog message and issuing END CONVERSATION: http://msdn2.microsoft.com/en-us/library/ms171587.aspx I've asked the Broker dev team how to determine which of the sys.conversation_endpoints are inactive and will let you know. From past blogs, to clean out the orphaned conversations, you'll basically code a script to query sys.conversation_endpoints for the conversation handles of the orphaned endpoints, then call END CONVERSATION WITH CLEANUP for those conversations, such as: http://www.nootz.net/index.php/notification-services-conversation-cleanup.html What I've asked the dev team is what should go into the WHERE clause of the SELECT from sys.conversation_endpoint so that you do not end active conversations. Also, for a large number of conversations, a past recommendation was to do these in batches of 50-100 conversations. SignatureAlan Brewer [MSFT] SQL Server Documentation Team Download the latest Books Online update: http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx This posting is provided "AS IS" with no warranties, and confers no rights. Thanks so much Alan! I really appreciate the help Steven > The Books Online also has a topic with a code example of testing for an > EndDialog message and issuing END CONVERSATION: [quoted text clipped - 14 lines] > Also, for a large number of conversations, a past recommendation was to do > these in batches of 50-100 conversations. |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2009 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.