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

Encrypted File System ?

Any comments on SQL 2000 Encrypted File System ?Hi
Microsoft supports it, but it does affect performance, and if you reset the
SQL Server Service Account's profile, the certificate gets invalidated, and
you loose access to the encrypted parts of the disk.
Really do a lot of reading on how the OS implements it before you use it in
production. technet has a lot of information.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Bob Castleman" <nomail@.here> wrote in message
news:#Q7EHr#LFHA.1144@.TK2MSFTNGP09.phx.gbl...
> Any comments on SQL 2000 Encrypted File System ?
>

Encrypted File System

Anyone have any cautions using Encrypted File System (EFS) to encrypt DB
files as a security practice? Thanks to eveyone for being there to help."coenzyme" <coenzyme@.discussions.microsoft.com> wrote in message
news:4BE42E6E-CE78-4A2B-B270-F5AEA3292D49@.microsoft.com...
> Anyone have any cautions using Encrypted File System (EFS) to encrypt DB
> files as a security practice? Thanks to eveyone for being there to help.
Don't do it!!! It cripples performance.
The biggest slow down in the database is File I/O. By encrypting the data
files, you are in essence significantly increasing the File I/O.
Follow some networking best practices to secure your server and it's data
files. Use encryption over the network (if you must).
Any of these are far better than encrypting the data files.
Rick Sawtell
MCT, MCSD, MCDBA|||Thanks for the response Rick.
"Rick Sawtell" wrote:
> "coenzyme" <coenzyme@.discussions.microsoft.com> wrote in message
> news:4BE42E6E-CE78-4A2B-B270-F5AEA3292D49@.microsoft.com...
> > Anyone have any cautions using Encrypted File System (EFS) to encrypt DB
> > files as a security practice? Thanks to eveyone for being there to help.
>
> Don't do it!!! It cripples performance.
> The biggest slow down in the database is File I/O. By encrypting the data
> files, you are in essence significantly increasing the File I/O.
>
> Follow some networking best practices to secure your server and it's data
> files. Use encryption over the network (if you must).
> Any of these are far better than encrypting the data files.
> Rick Sawtell
> MCT, MCSD, MCDBA
>
>|||Hi
And once you change the password of the service account, in the AD user
manager, your certificate gets cancelled and your data is no longer
accessible.
If a hacker is on your server, EFS does not help much anymore as he owns
your network already.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Rick Sawtell" <r_sawtell@.hotmail.com> wrote in message
news:%23eKmvwkhFHA.572@.TK2MSFTNGP15.phx.gbl...
> "coenzyme" <coenzyme@.discussions.microsoft.com> wrote in message
> news:4BE42E6E-CE78-4A2B-B270-F5AEA3292D49@.microsoft.com...
>> Anyone have any cautions using Encrypted File System (EFS) to encrypt DB
>> files as a security practice? Thanks to eveyone for being there to help.
>
> Don't do it!!! It cripples performance.
> The biggest slow down in the database is File I/O. By encrypting the
> data
> files, you are in essence significantly increasing the File I/O.
>
> Follow some networking best practices to secure your server and it's data
> files. Use encryption over the network (if you must).
> Any of these are far better than encrypting the data files.
> Rick Sawtell
> MCT, MCSD, MCDBA
>
>|||What do you think about column level encryption? I'm considering using
column-level encryption on a few columns of particularly sensitive data in a
database.
"Rick Sawtell" <r_sawtell@.hotmail.com> wrote in message
news:%23eKmvwkhFHA.572@.TK2MSFTNGP15.phx.gbl...
> "coenzyme" <coenzyme@.discussions.microsoft.com> wrote in message
> news:4BE42E6E-CE78-4A2B-B270-F5AEA3292D49@.microsoft.com...
>> Anyone have any cautions using Encrypted File System (EFS) to encrypt DB
>> files as a security practice? Thanks to eveyone for being there to help.
>
> Don't do it!!! It cripples performance.
> The biggest slow down in the database is File I/O. By encrypting the
> data
> files, you are in essence significantly increasing the File I/O.
>
> Follow some networking best practices to secure your server and it's data
> files. Use encryption over the network (if you must).
> Any of these are far better than encrypting the data files.
> Rick Sawtell
> MCT, MCSD, MCDBA
>
>

Encrypted File System

Anyone have any cautions using Encrypted File System (EFS) to encrypt DB
files as a security practice? Thanks to eveyone for being there to help."coenzyme" <coenzyme@.discussions.microsoft.com> wrote in message
news:4BE42E6E-CE78-4A2B-B270-F5AEA3292D49@.microsoft.com...
> Anyone have any cautions using Encrypted File System (EFS) to encrypt DB
> files as a security practice? Thanks to eveyone for being there to help.
Don't do it!!! It cripples performance.
The biggest slow down in the database is File I/O. By encrypting the data
files, you are in essence significantly increasing the File I/O.
Follow some networking best practices to secure your server and it's data
files. Use encryption over the network (if you must).
Any of these are far better than encrypting the data files.
Rick Sawtell
MCT, MCSD, MCDBA|||Thanks for the response Rick.
"Rick Sawtell" wrote:

> "coenzyme" <coenzyme@.discussions.microsoft.com> wrote in message
> news:4BE42E6E-CE78-4A2B-B270-F5AEA3292D49@.microsoft.com...
>
> Don't do it!!! It cripples performance.
> The biggest slow down in the database is File I/O. By encrypting the dat
a
> files, you are in essence significantly increasing the File I/O.
>
> Follow some networking best practices to secure your server and it's data
> files. Use encryption over the network (if you must).
> Any of these are far better than encrypting the data files.
> Rick Sawtell
> MCT, MCSD, MCDBA
>
>|||Hi
And once you change the password of the service account, in the AD user
manager, your certificate gets cancelled and your data is no longer
accessible.
If a hacker is on your server, EFS does not help much anymore as he owns
your network already.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Rick Sawtell" <r_sawtell@.hotmail.com> wrote in message
news:%23eKmvwkhFHA.572@.TK2MSFTNGP15.phx.gbl...
> "coenzyme" <coenzyme@.discussions.microsoft.com> wrote in message
> news:4BE42E6E-CE78-4A2B-B270-F5AEA3292D49@.microsoft.com...
>
> Don't do it!!! It cripples performance.
> The biggest slow down in the database is File I/O. By encrypting the
> data
> files, you are in essence significantly increasing the File I/O.
>
> Follow some networking best practices to secure your server and it's data
> files. Use encryption over the network (if you must).
> Any of these are far better than encrypting the data files.
> Rick Sawtell
> MCT, MCSD, MCDBA
>
>|||What do you think about column level encryption? I'm considering using
column-level encryption on a few columns of particularly sensitive data in a
database.
"Rick Sawtell" <r_sawtell@.hotmail.com> wrote in message
news:%23eKmvwkhFHA.572@.TK2MSFTNGP15.phx.gbl...
> "coenzyme" <coenzyme@.discussions.microsoft.com> wrote in message
> news:4BE42E6E-CE78-4A2B-B270-F5AEA3292D49@.microsoft.com...
>
> Don't do it!!! It cripples performance.
> The biggest slow down in the database is File I/O. By encrypting the
> data
> files, you are in essence significantly increasing the File I/O.
>
> Follow some networking best practices to secure your server and it's data
> files. Use encryption over the network (if you must).
> Any of these are far better than encrypting the data files.
> Rick Sawtell
> MCT, MCSD, MCDBA
>
>

Encrypted File System

Anyone have any cautions using Encrypted File System (EFS) to encrypt DB
files as a security practice? Thanks to eveyone for being there to help.
"coenzyme" <coenzyme@.discussions.microsoft.com> wrote in message
news:4BE42E6E-CE78-4A2B-B270-F5AEA3292D49@.microsoft.com...
> Anyone have any cautions using Encrypted File System (EFS) to encrypt DB
> files as a security practice? Thanks to eveyone for being there to help.
Don't do it!!! It cripples performance.
The biggest slow down in the database is File I/O. By encrypting the data
files, you are in essence significantly increasing the File I/O.
Follow some networking best practices to secure your server and it's data
files. Use encryption over the network (if you must).
Any of these are far better than encrypting the data files.
Rick Sawtell
MCT, MCSD, MCDBA
|||Thanks for the response Rick.
"Rick Sawtell" wrote:

> "coenzyme" <coenzyme@.discussions.microsoft.com> wrote in message
> news:4BE42E6E-CE78-4A2B-B270-F5AEA3292D49@.microsoft.com...
>
> Don't do it!!! It cripples performance.
> The biggest slow down in the database is File I/O. By encrypting the data
> files, you are in essence significantly increasing the File I/O.
>
> Follow some networking best practices to secure your server and it's data
> files. Use encryption over the network (if you must).
> Any of these are far better than encrypting the data files.
> Rick Sawtell
> MCT, MCSD, MCDBA
>
>
|||Hi
And once you change the password of the service account, in the AD user
manager, your certificate gets cancelled and your data is no longer
accessible.
If a hacker is on your server, EFS does not help much anymore as he owns
your network already.
Regards
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Rick Sawtell" <r_sawtell@.hotmail.com> wrote in message
news:%23eKmvwkhFHA.572@.TK2MSFTNGP15.phx.gbl...
> "coenzyme" <coenzyme@.discussions.microsoft.com> wrote in message
> news:4BE42E6E-CE78-4A2B-B270-F5AEA3292D49@.microsoft.com...
>
> Don't do it!!! It cripples performance.
> The biggest slow down in the database is File I/O. By encrypting the
> data
> files, you are in essence significantly increasing the File I/O.
>
> Follow some networking best practices to secure your server and it's data
> files. Use encryption over the network (if you must).
> Any of these are far better than encrypting the data files.
> Rick Sawtell
> MCT, MCSD, MCDBA
>
>
|||What do you think about column level encryption? I'm considering using
column-level encryption on a few columns of particularly sensitive data in a
database.
"Rick Sawtell" <r_sawtell@.hotmail.com> wrote in message
news:%23eKmvwkhFHA.572@.TK2MSFTNGP15.phx.gbl...
> "coenzyme" <coenzyme@.discussions.microsoft.com> wrote in message
> news:4BE42E6E-CE78-4A2B-B270-F5AEA3292D49@.microsoft.com...
>
> Don't do it!!! It cripples performance.
> The biggest slow down in the database is File I/O. By encrypting the
> data
> files, you are in essence significantly increasing the File I/O.
>
> Follow some networking best practices to secure your server and it's data
> files. Use encryption over the network (if you must).
> Any of these are far better than encrypting the data files.
> Rick Sawtell
> MCT, MCSD, MCDBA
>
>

Enable/Disable Job with SMO

I am trying to enable/disable a job using a using smo in a script task with the following code

Imports System.Object
Imports Microsoft.SqlServer.Management.Common
Imports Microsoft.SqlServer.Management.Smo
Imports Microsoft.SqlServer.Management.Smo.Agent
Imports Microsoft.SqlServer.Management.Smo.SqlSmoObject
Imports Microsoft.SqlServer.Management.Smo.SmoObjectBase
Imports Microsoft.SqlServer.Management.Smo.NamedSmoObject
Imports Microsoft.SqlServer.Management.Smo.Agent.Job
Imports Microsoft.SqlServer.Management.Smo.Agent.Jobserver
Imports Microsoft.SqlServer.Management.Smo.Agent.AgentObjectBase

Public Class ScriptMain

Public Sub Main()

Dim strJobserver As JobServer

Dim strJob As Job
Dim srv As Server
srv = New Server("conchango-vpc")

strJob = New Job(srv.JobServer, "test")

strJob.IsEnabled = True

strJob.Alter()

Dts.TaskResult = Dts.Results.Success
End Sub

End Class
and i get an error at the alter point.

when code is changed to using the following line
srv.Jobserver.Alter()
it runs successfully but no change is made to the job.
What am I missing?
Ifaka Enefe
Conchango

Your code is trying to create a new job called "test" instead of using the existing job.

Instead of:
strJob = New Job(srv.JobServer, "test")

Use:
strJob = srv.JobServer.Jobs("test")|||It worked. Thanks!|||Hi,
I've got the followinng inside a class library:

Server serv;

Job jb;

serv = new Server(this.strServerName);

jb = serv.JobServer.Jobs(this.strJobName);

if (strJobEnabledStatus=="True")

{

jb.IsEnabled = true;

}

else

{

jb.IsEnabled=false;

}

jb.Alter();

And it complains about

serv.JobServer.Jobs(this.strJobName)

saying "'Microsoft.SqlServer.Management.Smo.Agent.JobServer.Jobs' is a property but is used like a 'method'"

Any ideas? I aint much a developer I'm afraid so I'm at a bit of a loss here.

Thanks
Jamie|||s'all right. I've found it.

jb = serv.JobServer.Jobs[this.strJobName];

Must get my head out of VB mode

-Jamie

Enable/Disable Job with SMO

I am trying to enable/disable a job using a using smo in a script task with the following code

Imports System.Object
Imports Microsoft.SqlServer.Management.Common
Imports Microsoft.SqlServer.Management.Smo
Imports Microsoft.SqlServer.Management.Smo.Agent
Imports Microsoft.SqlServer.Management.Smo.SqlSmoObject
Imports Microsoft.SqlServer.Management.Smo.SmoObjectBase
Imports Microsoft.SqlServer.Management.Smo.NamedSmoObject
Imports Microsoft.SqlServer.Management.Smo.Agent.Job
Imports Microsoft.SqlServer.Management.Smo.Agent.Jobserver
Imports Microsoft.SqlServer.Management.Smo.Agent.AgentObjectBase

Public Class ScriptMain

Public Sub Main()

Dim strJobserver As JobServer

Dim strJob As Job
Dim srv As Server
srv = New Server("conchango-vpc")

strJob = New Job(srv.JobServer, "test")

strJob.IsEnabled = True

strJob.Alter()

Dts.TaskResult = Dts.Results.Success
End Sub

End Class
and i get an error at the alter point.

when code is changed to using the following line
srv.Jobserver.Alter()
it runs successfully but no change is made to the job.
What am I missing?
Ifaka Enefe
Conchango

Your code is trying to create a new job called "test" instead of using the existing job.

Instead of:
strJob = New Job(srv.JobServer, "test")

Use:
strJob = srv.JobServer.Jobs("test")|||It worked. Thanks!|||Hi,
I've got the followinng inside a class library:

Server serv;

Job jb;

serv = new Server(this.strServerName);

jb = serv.JobServer.Jobs(this.strJobName);

if (strJobEnabledStatus=="True")

{

jb.IsEnabled = true;

}

else

{

jb.IsEnabled=false;

}

jb.Alter();

And it complains about

serv.JobServer.Jobs(this.strJobName)

saying "'Microsoft.SqlServer.Management.Smo.Agent.JobServer.Jobs' is a property but is used like a 'method'"

Any ideas? I aint much a developer I'm afraid so I'm at a bit of a loss here.

Thanks
Jamie|||s'all right. I've found it.

jb = serv.JobServer.Jobs[this.strJobName];

Must get my head out of VB mode

-Jamie