End 35 Million conversations quickly - production issue!

We have a system that has 35 million conversations piled up. We didn't know to explicitly end the conversation once the processing has completed. Oops. Now, our production box has 35 mm sitting in the table, and we have run into the problem where the amount in sys.conversation_endpoints has exceeded memory and they are being dumped into tempdb, which is killing our disk space, thus bringing the box down. We have fixed the code to end the conversations, but we now have to end the conversations in a hurry. If we select one by one out of the table and end the conversation via END CONVERSATION, it is slow. Very slow. It will finish in a few months. :(

Does anyone know how to get rid of these conversations in a hurry? All of the messages have been applied to our system, so killing the conversations will (should) have no affect on the processed data. Something like a TRUNCATE statement?

Thank you so much in advance,

John Hennesey

The best way is to END CONVERSATION in batches and let it run. Make sure you batch 100-200 ENDs in one transaction batch for better performance.

ALTER DATABSE ... SET NEW_BROKER has the risk of distrbing existing, active conversations. Also, in RTM and SP1 is just as slow as a batch of END CONVERSATION ... WITH CLEANUP, since that what it does internally.

If none of these work for you, then if you dare installing SP2 CTP, the internals of ALTER DATABASE ... SET NEW_BROKER have changed to truncate and will do what you need, fast. You will have to suspend the production system for a short while and make sure there are no active dialogs, to prevent active dialogs from being affected. also, if your routes use the broker_instance, you will have to update them after the NEW_BROKER operation.

HTH,
~ Remus

|||

Remus -

Thank you very much for your response. We did exactly what you said and it worked. It was just a bit slower than what we needed. It's good to know the CTP will have another option for handling this - unfortunately since this is a production system CTP isn't an option. We split it up by queue name and ran 10 queries in parallel to end the conversations a little quicker. It took about 24 hours, and with the code changes we are in good shape.

Thank you for your response,

John

Encryption question, sql 2000

Situation: We are required to encrypt procs in part of our production
environment. We have the
unencrypted procs in test environment. As a sanity check (when a problem
arises), is there a way
to verify that an encrypted proc (ctext) in production matches a specific
unencrypted version?
If the procs were first encrypted with Alter Proc ... With Encryption, then
the same script can be
run again, and before/after syscomments.ctext values compared. But this is
a destructive test
that potentially changes prod environment.
Is there some benign alternative?
Thanks,
Craig Hessel
WPS, Madison, WICraig,
Two possibilities:
1) Restore db to development machine. Unencrypt stored procs there and check them
( unencryption algo is straightforward but destructive )
2) Check the length of the procedures in syscomments - most edits will change this
Regards
AJ
"Craig Hessel" <craig_hessel@.hotmail.com> wrote in message news:10181dttf4ta204@.corp.supernews.com...
> Situation: We are required to encrypt procs in part of our production
> environment. We have the
> unencrypted procs in test environment. As a sanity check (when a problem
> arises), is there a way
> to verify that an encrypted proc (ctext) in production matches a specific
> unencrypted version?
> If the procs were first encrypted with Alter Proc ... With Encryption, then
> the same script can be
> run again, and before/after syscomments.ctext values compared. But this is
> a destructive test
> that potentially changes prod environment.
> Is there some benign alternative?
> Thanks,
> Craig Hessel
> WPS, Madison, WI
>sql

Encrypting the actual MDF file - not the data column

Hi there,
We have a website application that we needed to replicae in various laptops
for our team that does not have internet access to our production site
constantly. We wrote the application that keeps in sync the website, the
database and the relevant data between the laptop and our production server.
In order to do that, we had to replicate the production database and
production "compiled" website on the local laptop, and have the applicaion
downloading and uploading the relevant data in order to keep the whole thing
in sync. Success !!!
Now comes the problem:
At this point we have a laptop that holds a production compiled dotfuscated
website (we are ok with that) and the production database with a minimum
subset of data. This means that the Database schema and the data is open to
anybody that has access to that laptop. So what happen if the laptop gets
stolen ?
The only way we can allow that is if we find a way to encrypt somehow the
MDF file.
Constraint:
We cannot change the production website and SP code to ENCRYPT - DECRYPT
certain data columns.
So my question is ..can I secure the DB file in case the laptop get stolen?
Thanks,
FPConsider using Encrypting File System (EFS).
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
"Hey it's Filippo" <pandiani69@.hotmail.com> wrote in message
news:%23QK1780ZHHA.5020@.TK2MSFTNGP05.phx.gbl...
Hi there,
We have a website application that we needed to replicae in various laptops
for our team that does not have internet access to our production site
constantly. We wrote the application that keeps in sync the website, the
database and the relevant data between the laptop and our production server.
In order to do that, we had to replicate the production database and
production "compiled" website on the local laptop, and have the applicaion
downloading and uploading the relevant data in order to keep the whole thing
in sync. Success !!!
Now comes the problem:
At this point we have a laptop that holds a production compiled dotfuscated
website (we are ok with that) and the production database with a minimum
subset of data. This means that the Database schema and the data is open to
anybody that has access to that laptop. So what happen if the laptop gets
stolen ?
The only way we can allow that is if we find a way to encrypt somehow the
MDF file.
Constraint:
We cannot change the production website and SP code to ENCRYPT - DECRYPT
certain data columns.
So my question is ..can I secure the DB file in case the laptop get stolen?
Thanks,
FP|||Tom,
With EFS, would my local application be able to connect and run queries
normally?
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:eYxH5O1ZHHA.4000@.TK2MSFTNGP02.phx.gbl...
> Consider using Encrypting File System (EFS).
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Toronto, ON Canada
> "Hey it's Filippo" <pandiani69@.hotmail.com> wrote in message
> news:%23QK1780ZHHA.5020@.TK2MSFTNGP05.phx.gbl...
> Hi there,
> We have a website application that we needed to replicae in various
> laptops
> for our team that does not have internet access to our production site
> constantly. We wrote the application that keeps in sync the website, the
> database and the relevant data between the laptop and our production
> server.
> In order to do that, we had to replicate the production database and
> production "compiled" website on the local laptop, and have the applicaion
> downloading and uploading the relevant data in order to keep the whole
> thing
> in sync. Success !!!
> Now comes the problem:
> At this point we have a laptop that holds a production compiled
> dotfuscated
> website (we are ok with that) and the production database with a minimum
> subset of data. This means that the Database schema and the data is open
> to
> anybody that has access to that laptop. So what happen if the laptop gets
> stolen ?
> The only way we can allow that is if we find a way to encrypt somehow the
> MDF file.
> Constraint:
> We cannot change the production website and SP code to ENCRYPT - DECRYPT
> certain data columns.
> So my question is ..can I secure the DB file in case the laptop get
> stolen?
> Thanks,
> FP
>
>|||Also,
Since I do need to keep in sync the local DB and sometimes I need to "drop"
it and then recreate an empty one, would I be able to programmatically
encrypt the file using EFS?
If so, do you have a code sample (possibly in C#)?
Thanks again,
Filippo
"Hey it's Filippo" <pandiani69@.hotmail.com> wrote in message
news:uar7Yq1ZHHA.4872@.TK2MSFTNGP03.phx.gbl...
> Tom,
> With EFS, would my local application be able to connect and run queries
> normally?
>
> "Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
> news:eYxH5O1ZHHA.4000@.TK2MSFTNGP02.phx.gbl...
>|||You'd have SQL running under a domain account and that account would then be
writing files (data and log) on the disk. If you copy files to be attached
or restores, be sure to use that same domain account.
How you connect to SQL Server doesn't matter. The login that you use to
talk to SQL Server isn't the account that writes to those files. The SQL
Server service account is.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
.
"Hey it's Filippo" <pandiani69@.hotmail.com> wrote in message
news:uar7Yq1ZHHA.4872@.TK2MSFTNGP03.phx.gbl...
Tom,
With EFS, would my local application be able to connect and run queries
normally?
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:eYxH5O1ZHHA.4000@.TK2MSFTNGP02.phx.gbl...
> Consider using Encrypting File System (EFS).
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Toronto, ON Canada
> "Hey it's Filippo" <pandiani69@.hotmail.com> wrote in message
> news:%23QK1780ZHHA.5020@.TK2MSFTNGP05.phx.gbl...
> Hi there,
> We have a website application that we needed to replicae in various
> laptops
> for our team that does not have internet access to our production site
> constantly. We wrote the application that keeps in sync the website, the
> database and the relevant data between the laptop and our production
> server.
> In order to do that, we had to replicate the production database and
> production "compiled" website on the local laptop, and have the applicaion
> downloading and uploading the relevant data in order to keep the whole
> thing
> in sync. Success !!!
> Now comes the problem:
> At this point we have a laptop that holds a production compiled
> dotfuscated
> website (we are ok with that) and the production database with a minimum
> subset of data. This means that the Database schema and the data is open
> to
> anybody that has access to that laptop. So what happen if the laptop gets
> stolen ?
> The only way we can allow that is if we find a way to encrypt somehow the
> MDF file.
> Constraint:
> We cannot change the production website and SP code to ENCRYPT - DECRYPT
> certain data columns.
> So my question is ..can I secure the DB file in case the laptop get
> stolen?
> Thanks,
> FP
>
>|||You wouldn't encrypt it programmatically. It's done automatically.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
.
"Hey it's Filippo" <pandiani69@.hotmail.com> wrote in message
news:O5Il8p%23ZHHA.1300@.TK2MSFTNGP02.phx.gbl...
Also,
Since I do need to keep in sync the local DB and sometimes I need to "drop"
it and then recreate an empty one, would I be able to programmatically
encrypt the file using EFS?
If so, do you have a code sample (possibly in C#)?
Thanks again,
Filippo
"Hey it's Filippo" <pandiani69@.hotmail.com> wrote in message
news:uar7Yq1ZHHA.4872@.TK2MSFTNGP03.phx.gbl...
> Tom,
> With EFS, would my local application be able to connect and run queries
> normally?
>
> "Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
> news:eYxH5O1ZHHA.4000@.TK2MSFTNGP02.phx.gbl...
>|||Also have a look at:
http://technet.microsoft.com/en-us/.../aa906017.aspx, for a new
technology that would be helpful in this scenario.
Thanks
Laurentiu Cristofor [MSFT]
Software Development Engineer
SQL Server Engine
http://blogs.msdn.com/lcris/
This posting is provided "AS IS" with no warranties, and confers no rights.
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:%23DmRu$MaHHA.4552@.TK2MSFTNGP05.phx.gbl...
> You'd have SQL running under a domain account and that account would then
> be
> writing files (data and log) on the disk. If you copy files to be
> attached
> or restores, be sure to use that same domain account.
> How you connect to SQL Server doesn't matter. The login that you use to
> talk to SQL Server isn't the account that writes to those files. The SQL
> Server service account is.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canada
> .
> "Hey it's Filippo" <pandiani69@.hotmail.com> wrote in message
> news:uar7Yq1ZHHA.4872@.TK2MSFTNGP03.phx.gbl...
> Tom,
> With EFS, would my local application be able to connect and run queries
> normally?
>
> "Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
> news:eYxH5O1ZHHA.4000@.TK2MSFTNGP02.phx.gbl...
>

Encrypting SPs and maybe triggers, how?

Don't want my sa to be able to tinker with Stored procedures or triggers on
the production machine. I saw some software that had them encrypted. Could
not go in and see their text. How can this be done?
Thanks for any help.
BobUse the WITH ENCRYPTION clause. However, I believe there are tools
available on the Internet to unencrypt the text. Also, note that once
encrypted the object cannot be scripted out for other purposes. Be sure to
save the original DDL in a secure location if needed in the future.
HTH
Jerry
"RDufour" <rdufour@.sgiims.com> wrote in message
news:O9FtvxuuFHA.4040@.TK2MSFTNGP10.phx.gbl...
> Don't want my sa to be able to tinker with Stored procedures or triggers
> on
> the production machine. I saw some software that had them encrypted. Could
> not go in and see their text. How can this be done?
> Thanks for any help.
> Bob
>

Encrypting SPs and maybe triggers, how?

Don't want my sa to be able to tinker with Stored procedures or triggers on
the production machine. I saw some software that had them encrypted. Could
not go in and see their text. How can this be done?
Thanks for any help.
Bob
Use the WITH ENCRYPTION clause. However, I believe there are tools
available on the Internet to unencrypt the text. Also, note that once
encrypted the object cannot be scripted out for other purposes. Be sure to
save the original DDL in a secure location if needed in the future.
HTH
Jerry
"RDufour" <rdufour@.sgiims.com> wrote in message
news:O9FtvxuuFHA.4040@.TK2MSFTNGP10.phx.gbl...
> Don't want my sa to be able to tinker with Stored procedures or triggers
> on
> the production machine. I saw some software that had them encrypted. Could
> not go in and see their text. How can this be done?
> Thanks for any help.
> Bob
>

Encrypted Triggers.

Somehow, the encrypted triggers were missing in our production server.
Is there any way, without restoring old database, to recover them using
development server?
Thanks!
-Alan
Hi, you will have to restore the database or find the script which
actually created them with encryption. AFAIK there is no way to script
encrypted triggers out of the database. If you are using SQL Server
2000 you might search at Google for some (unsupported) decrypting
mechanisms to hack the procedures. But there is (fortunately) no
official tool for this.
HTH, Jens K. Suessmeyer.
http://www.sqlserver2005.de

Encrypted Triggers.

Somehow, the encrypted triggers were missing in our production server.
Is there any way, without restoring old database, to recover them using
development server?
Thanks!
-AlanHi, you will have to restore the database or find the script which
actually created them with encryption. AFAIK there is no way to script
encrypted triggers out of the database. If you are using SQL Server
2000 you might search at Google for some (unsupported) decrypting
mechanisms to hack the procedures. But there is (fortunately) no
official tool for this.
HTH, Jens K. Suessmeyer.
--
http://www.sqlserver2005.de
--

Encrypted Triggers.

Somehow, the encrypted triggers were missing in our production server.
Is there any way, without restoring old database, to recover them using
development server?
Thanks!
-AlanHi, you will have to restore the database or find the script which
actually created them with encryption. AFAIK there is no way to script
encrypted triggers out of the database. If you are using SQL Server
2000 you might search at Google for some (unsupported) decrypting
mechanisms to hack the procedures. But there is (fortunately) no
official tool for this.
HTH, Jens K. Suessmeyer.
http://www.sqlserver2005.de
--

Enable Production SQL Server Express for SQLCacheDependency?

Q1: Can I just open these protocols I need (see web links below) and then close them down again after SQLCacheDependency has been enabled? Note: The website (asp.net 2.0/SQL server Express) operates OK right now with asp.net - but it does not have SQLCacheDependency enabled.

Problem: When I try to do this I keep on getting this error message:

"An error has occurred when establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections."

None of these 3 commands have worked (all give the same message above):

aspnet_regsql.exe -U user1 -P pwd1 -S server1/SQLEXPRESS -d myDBName -ed
aspnet_regsql.exe -E -S server1/SQLEXPRESS -d myDBName -ed
aspnet_regsql.exe -E -S .\SqlExpress -d myDBName -ed

I am trying to run these commands via a MSTSC connection. Q2: Is that (terminal server) the problem (the "remote connection") complained of above ?

This is the command that worked on my local dev machine:

aspnet_regsql.exe -E -S .\SqlExpress -d myDBName -ed

Q3: Must I really go through the procedure "How to: Configure Express to accept remote connections" at:

http://support.microsoft.com/default.aspx?scid=kb;EN-US;914277
http://blogs.msdn.com/sqlexpress/archive/2005/05/05/415084.aspx

I don't really want to have SQL server accepting any connections as the last time it was listening on these ports we had so many attacks being made that it was filling up the logs!

What I can't understand is that every night the database is backed up with the following command line code (operating as a Scheduled Task):

sqlcmd -s server1/SQLEXPRESS -i MW4BAK.SQL -E

Q4: So if the server is happy receiving commands from 'sqlcmd.exe' why does it have such problems with 'aspnet_regsql.exe'

Q5: If I grouped those statements I need to setup SQLCacheDependency under a Scheduled Task would they work (as there would presumably be no remote connection then) or is it something in the nature of the connection established which allows sqlcmd.exe to work but stops aspnet_regsql.exe?

I was able to configure the database for cache dependency by just running the command with no parameters and letting the wizard take over:

aspnet_regsql.exe

But ICAN NOT enable the tables e.g.
aspnet_regsql.exe -E -S server1\SqlExpress -d myDBName -t myTable1 -et
continues to give the error message above (implying that it won't allow a remote connection).

What's the problem? Is it because I'm running the command remotely under terminal server?

|||

Apologies. I sorted it out.

The SQL instance is not called "server1/SQLEXPRESS". It is only
"server1" (on this server). So the following will work.

aspnet_regsql.exe -E -S server1 -d myDBName -ed
aspnet_regsql.exe -E -S server1 -d myDBName -t myTableName -et

etc. for each table

emtpy sysperfinfo table

Hello!
I am getting no records when querying sysperinfo table:
select * from master..sysperfinfo
This is production server running SQL2000 SP3 onWindows 2002 . I saw many
people asking the same question but didn't find a definite answer. As a
result, I do not see any of the SQL Server counters in the system monitor. I
am pretty much sure server was installed with performance counters.
Any advice would be greatly appreciated.
IgorCheck the following article as it very likely may apply to
the problems you are having - note the message that is
logged to the error log, that's one way to verify if it
applies in your case:
FIX: "Performance monitor shared memory setup failed: -1"
error message when you start SQL Server
http://support.microsoft.com/?id=812915
-Sue
On Tue, 23 Nov 2004 17:05:05 -0800, "Igor"
<imarchenko@.bowneglobal.com> wrote:
>Hello!
>I am getting no records when querying sysperinfo table:
>select * from master..sysperfinfo
> This is production server running SQL2000 SP3 onWindows 2002 . I saw many
>people asking the same question but didn't find a definite answer. As a
>result, I do not see any of the SQL Server counters in the system monitor. I
>am pretty much sure server was installed with performance counters.
> Any advice would be greatly appreciated.
>
>Igor
>|||Hello Sue,
I do not get error you mentioned in SQL Server error log. I also do not
think this case applies to my problem.
Thanks,
Igor
"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
news:6mp7q0pfkrjmp7mfgb8gdn3k4t7lka5ti1@.4ax.com...
> Check the following article as it very likely may apply to
> the problems you are having - note the message that is
> logged to the error log, that's one way to verify if it
> applies in your case:
> FIX: "Performance monitor shared memory setup failed: -1"
> error message when you start SQL Server
> http://support.microsoft.com/?id=812915
> -Sue
> On Tue, 23 Nov 2004 17:05:05 -0800, "Igor"
> <imarchenko@.bowneglobal.com> wrote:
> >Hello!
> >
> >I am getting no records when querying sysperinfo table:
> >
> >select * from master..sysperfinfo
> >
> > This is production server running SQL2000 SP3 onWindows 2002 . I saw
many
> >people asking the same question but didn't find a definite answer. As a
> >result, I do not see any of the SQL Server counters in the system
monitor. I
> >am pretty much sure server was installed with performance counters.
> > Any advice would be greatly appreciated.
> >
> >
> >Igor
> >
>|||Your symptoms do point to the issue in the KB Sue mentioned. Usually when
you don't get the counters (which come from syserfinfo) and your
lastwaittype column in sysprocesses is always Misc you blew away the
counters. The short term solution is to stop anything that is monitoring
the counters and restart SQL Server.
--
Andrew J. Kelly SQL MVP
"Igor" <imarchenko@.bowneglobal.com> wrote in message
news:elohshc0EHA.3708@.TK2MSFTNGP14.phx.gbl...
> Hello Sue,
> I do not get error you mentioned in SQL Server error log. I also do not
> think this case applies to my problem.
> Thanks,
> Igor
> "Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
> news:6mp7q0pfkrjmp7mfgb8gdn3k4t7lka5ti1@.4ax.com...
>> Check the following article as it very likely may apply to
>> the problems you are having - note the message that is
>> logged to the error log, that's one way to verify if it
>> applies in your case:
>> FIX: "Performance monitor shared memory setup failed: -1"
>> error message when you start SQL Server
>> http://support.microsoft.com/?id=812915
>> -Sue
>> On Tue, 23 Nov 2004 17:05:05 -0800, "Igor"
>> <imarchenko@.bowneglobal.com> wrote:
>> >Hello!
>> >
>> >I am getting no records when querying sysperinfo table:
>> >
>> >select * from master..sysperfinfo
>> >
>> > This is production server running SQL2000 SP3 onWindows 2002 . I saw
> many
>> >people asking the same question but didn't find a definite answer. As a
>> >result, I do not see any of the SQL Server counters in the system
> monitor. I
>> >am pretty much sure server was installed with performance counters.
>> > Any advice would be greatly appreciated.
>> >
>> >
>> >Igor
>> >
>