Showing posts with label messages. Show all posts
Showing posts with label messages. Show all posts

Friday, March 9, 2012

How to compress text before storing in DB?

I was planning on running a service where thousands of text messages are stored. Obviously I'd want to make the most of my DB space, and was wondering if there's some way for SQL to compress text down to the smallest space possible. If not, is there some kind of ASP component I could download to do this? Failing that, I could always write a simple one, which takes the most common letter combinations, and shortens them down to a single character.

Any advice?Space is cheap. Use it. Otherwise, your data will be dependent on whatever compression algorithm you use, and everyone will have to consult with you to read the data. Of course, if you're a consultant focused on billable hours, writing some hair-brained compression may be just what you want.

Either don't worry about space (have you done the math on what this gives you vs. buying the adequate space?), or store your text outside the db on a compressed drive.|||I don't have to consult with anyone :) It's a one-man project, just something I'm building for consumers, there's no income involved. And space is cheap, yes, if I'm hosting this solution myself. But I'm not. And SQL Server 2000 DB storage does not come cheaply at a good, reliable host.|||Anyone else? Anyone?|||Maybe this will get you to drink the water: Video on Hotmail Architecture (http://channel9.msdn.com/ShowPost.aspx?PostID=39016)

Sunday, February 19, 2012

how to clean all messages of a queue ?

How to clean all messages of a queue ?

Online books says it can be done as below:
RECEIVE * FROM ExpenseQueue

But it only delete a row every time,and I found all the three statement only delete a row:

RECEIVE * FROM ExpenseQueue
RECEIVE TOP(1) * FROM ExpenseQueue
RECEIVE TOP(n) * FROM ExpenseQueue

The RECEIVE statement (as it exists today) only fetches messages belonging to a single conversation group at a time. So the only way to get rid of all messages is to call RECEIVE in a loop:

WHILE (1)
BEGIN
RECEIVE * FROM ExpenseQueue
IF (@.@.ROWCOUNT = 0)
BREAK
END

Are you just looking at means to clean up the queue, or do you really want to fetch multiple conversation groups in a single batch. If it is the latter, could you explain your requirements further to help us improve the product in future versions?

Thanks,
Rushi
--
Developer, Service Broker Team, SQL Server Engine
Microsoft Corporation

|||Rushi's answer assumes that you indeed just want to get rid of the messages and leave the conversations active. If you also want to clean up the conversations - say you have run a bunch of tests and found your application doesn't work correctly - you might want to try one of these scripts which work in ascending dgrees of severity. If you truely don't care at all about the messages or conversations, try the last one:

-- End conversations that are in an error state

declare @.handle uniqueidentifier

declare conv cursor for select conversation_handle from sys.conversation_endpoints where state = 'ER'

open conv

fetch NEXT FROM conv into @.handle

while @.@.FETCH_STATUS = 0

Begin

END Conversation @.handle with cleanup

fetch NEXT FROM conv into @.handle

End

close conv

deallocate conv

-- End conversations that are half closed

declare @.handle uniqueidentifier

declare conv cursor for select conversation_handle from sys.conversation_endpoints where state = 'DI'

open conv

fetch NEXT FROM conv into @.handle

while @.@.FETCH_STATUS = 0

Begin

END Conversation @.handle with cleanup

fetch NEXT FROM conv into @.handle

End

close conv

deallocate conv

-- End all conversations

declare @.handle uniqueidentifier

declare conv cursor for select conversation_handle from sys.conversation_endpoints

open conv

fetch NEXT FROM conv into @.handle

while @.@.FETCH_STATUS = 0

Begin

END Conversation @.handle with cleanup

fetch NEXT FROM conv into @.handle

End

close conv

deallocate conv

|||scripts of Roger_MS can solve my problem, but seems it clean all messages of all queues. If I want to clean a queue only, should I use my Queue_Name to replace sys.conversation_endpoints ? Just as

declare conv cursor for select conversation_handle from Queue_Name

And I think it is convenient to use a statement like "alter queue XXX with cleanup" .

Thank you.|||That would clean up one end of the conversations in the queue. Keep in mind that conversations always involve 2 queues so if you want to clean up the conversations you will have to clean up both queues. This is definitely not something you want to do regularly. Clearing out a queue is equivalent to truncating a table - there will often be valuable data destroyed if you do this so this would generally be something you do only during development.