Connections & Single-User

Hello all,
As an sql novice I have created some Maintenance Plans which seemed to work
ok except for integrity checks. As I can read from the logs they could'n be
executed because the db had to be in Single-User. Ok got that.
But the problem is that other servers are running some foreground &
background programs that connect to the sql svr. When I closed the
connections on our sql, the other server keeps connecting back resulting in
the fact I couldn't close the connections. This weekend when I rebooted the
other servers they lost connection to sql resulting in 0 connections to the
db.
How can this be done then, if remote servers keep connecting ? Is there a
safe way to get my db's in single user ?
I presume my test by closing the active connections to the sql via Ent. Mgr
was also not a good thing to do?
Rgds,
T.
The way to handle it is to remove the check for the option
to repair errors on your integrity checks in the maintenance
plan. You don't really want automatic repairs happening if
your dbcc checkdbs fail. You would want to intervene
yourself, determine what caused the consistency error, fix
the underlying problem and then take whatever course of
action based on what the underlying problem is.
-Sue
On Mon, 20 Sep 2004 19:21:26 GMT, "TieTo" <nobody@.home.net>
wrote:

>Hello all,
>As an sql novice I have created some Maintenance Plans which seemed to work
>ok except for integrity checks. As I can read from the logs they could'n be
>executed because the db had to be in Single-User. Ok got that.
>But the problem is that other servers are running some foreground &
>background programs that connect to the sql svr. When I closed the
>connections on our sql, the other server keeps connecting back resulting in
>the fact I couldn't close the connections. This weekend when I rebooted the
>other servers they lost connection to sql resulting in 0 connections to the
>db.
>How can this be done then, if remote servers keep connecting ? Is there a
>safe way to get my db's in single user ?
>I presume my test by closing the active connections to the sql via Ent. Mgr
>was also not a good thing to do?
>Rgds,
>T.
>
>
|||"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
news:4h5vk0l1mvrer6h775b6dgb2slkg5441r0@.4ax.com...
> The way to handle it is to remove the check for the option
> to repair errors on your integrity checks in the maintenance
> plan. You don't really want automatic repairs happening if
> your dbcc checkdbs fail. You would want to intervene
> yourself, determine what caused the consistency error, fix
> the underlying problem and then take whatever course of
> action based on what the underlying problem is.
> -Sue
>
Ok Sue, I will do so. Thank you for your help!!
Rgds,
T.

Connection Timeout

Is there any configuration option that controls how long
a user stays connected to mssql server? I have an
application that logs into mssql server and then uses
that one login for all work never logging out, after
about 7 days(fairly consistently) the connection to the
mssql gets killed and the application has to be
restarted. I was just curious if there is some kind of
parameter within mssql server that kills logins after x
number of days?
Thanks,
CarlHi,
There is no parameter in SQL server which will kill the user after x number
of days.
Rather you can write ur own code based on the login_time in
master..sysprocesses table and compare with getdate() for each process id
available in the list.
If any of the SPID is older then x days you can use
KILL SPID to kill the user. SPID also you can take it from
master..sysprocesses tables
Thanks
Hari
MCDBA
"z-man" <anonymous@.discussions.microsoft.com> wrote in message
news:817d01c4076d$e655aa20$a601280a@.phx.gbl...
> Is there any configuration option that controls how long
> a user stays connected to mssql server? I have an
> application that logs into mssql server and then uses
> that one login for all work never logging out, after
> about 7 days(fairly consistently) the connection to the
> mssql gets killed and the application has to be
> restarted. I was just curious if there is some kind of
> parameter within mssql server that kills logins after x
> number of days?
> Thanks,
> Carl
>|||There isn't anything like that built into SQL Server. You
could write your own procedure to do something like that but
nothing built into SQL Server.
If the application is on PC, server other than SQL Server,
you may want to look into network connectivity issues. You
may want to check the event logs where the application is
and see if you can find any clues in the logs.
-Sue
On Thu, 11 Mar 2004 05:36:47 -0800, "z-man"
<anonymous@.discussions.microsoft.com> wrote:

>Is there any configuration option that controls how long
>a user stays connected to mssql server? I have an
>application that logs into mssql server and then uses
>that one login for all work never logging out, after
>about 7 days(fairly consistently) the connection to the
>mssql gets killed and the application has to be
>restarted. I was just curious if there is some kind of
>parameter within mssql server that kills logins after x
>number of days?
>Thanks,
>Carl|||There is a 2 hour tcp session timeout and 1 hour for name pipe sessions.
But, this is controlled by the OS, not SQL.
Thanks,
Kevin McDonnell
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.|||That's a good point...and looking at the keepalive settings
could be an option. After thinking about it more and that if
this happens about every 7 days, it may more likely be
related to a weekly process or something similar that is
hosing the connection. I think I'd go through the logs and
look for activity around the time the connection gets hosed.
-Sue
On Thu, 11 Mar 2004 21:02:41 GMT, kevmc@.online.microsoft.com
(Kevin McDonnell [MSFT]) wrote:

>There is a 2 hour tcp session timeout and 1 hour for name pipe sessions.
>But, this is controlled by the OS, not SQL.
>Thanks,
>Kevin McDonnell
>Microsoft Corporation
>This posting is provided AS IS with no warranties, and confers no rights.
>