We had a lot user connections with 'sleeping' mode in sysprocesses. I killed them with the status='sleeping', I realized that i might also killed some connections are not supposed to be killed.
After that, I checked event viewer, I saw 'Unable to read local eventlog (reason: The data area passed to a system call is too small).
When I restarted the sqlserveragent service, I got'Could not start SQLserverAgent Service on local computer. The service did not return an error. This could be an internal Windows error or an internal service error. If this error persists, contact your system administrator. ' and also the following message appears in Application log
SQLServerAgent could not be started (reason: SQLServerAgent must be able to connect to SQLServer as SysAdmin, but '(Unknown)' is not a member of the SysAdmin role).1) Reboot your server.
2) Don't kill processes you don't know for sure are safe. NEVER kill a
process with a SPID under 50. Those are system processes and need to be
there. Sleeping processes do not harm SQL and typically take very little
memory.
--
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Shannon" <anonymous@.discussions.microsoft.com> wrote in message
news:128716F6-4D97-41E6-B8E6-EC001502D843@.microsoft.com...
> We had a lot user connections with 'sleeping' mode in sysprocesses. I
killed them with the status='sleeping', I realized that i might also killed
some connections are not supposed to be killed.
> After that, I checked event viewer, I saw 'Unable to read local eventlog
(reason: The data area passed to a system call is too small). '
> When I restarted the sqlserveragent service, I got'Could not start
SQLserverAgent Service on local computer. The service did not return an
error. This could be an internal Windows error or an internal service error.
If this error persists, contact your system administrator. ' and also the
following message appears in Application log '
> SQLServerAgent could not be started (reason: SQLServerAgent must be able
to connect to SQLServer as SysAdmin, but '(Unknown)' is not a member of the
SysAdmin role). '
>|||If you want to kill off all your user connections, you might try a query
like this:
select 'kill '+convert(varchar(10),spid) from sysprocesses where dbid not in
(select dbid from sysdatabases where name in ('master','msdb') union all
select 0 as dbid) order by sysprocesses.spid
"Shannon" <anonymous@.discussions.microsoft.com> wrote in message
news:128716F6-4D97-41E6-B8E6-EC001502D843@.microsoft.com...
> We had a lot user connections with 'sleeping' mode in sysprocesses. I
killed them with the status='sleeping', I realized that i might also killed
some connections are not supposed to be killed.
> After that, I checked event viewer, I saw 'Unable to read local eventlog
(reason: The data area passed to a system call is too small). '
> When I restarted the sqlserveragent service, I got'Could not start
SQLserverAgent Service on local computer. The service did not return an
error. This could be an internal Windows error or an internal service error.
If this error persists, contact your system administrator. ' and also the
following message appears in Application log '
> SQLServerAgent could not be started (reason: SQLServerAgent must be able
to connect to SQLServer as SysAdmin, but '(Unknown)' is not a member of the
SysAdmin role). '
>|||Sorry, I am not recommending you try that. I accidentally hit a
key-combination that posted that note while I was composing it and still
thinking about it.
I didn't test it and the thought process was incomplete. One of the
processes killed would be your own. Well... I take that back. If you were
in master at the time, your own process would not be listed and if you were
not in master, this query couldn't resolve sysprocesses - unless it was an
SP_ stored procedure in master. Still, adding an "and spid <> @.@.spid"
clause might be a good idea.
Of course, the select statement I listed is no threat by itself, you'd have
to execute the results.
"DHatheway" <dlhatheway@.mmm.com.nospam> wrote in message
news:c5k11b$pfh$1@.tuvok3.mmm.com...
> If you want to kill off all your user connections, you might try a query
> like this:
> select 'kill '+convert(varchar(10),spid) from sysprocesses where dbid not
in
> (select dbid from sysdatabases where name in ('master','msdb') union all
> select 0 as dbid) order by sysprocesses.spid
> "Shannon" <anonymous@.discussions.microsoft.com> wrote in message
> news:128716F6-4D97-41E6-B8E6-EC001502D843@.microsoft.com...
> > We had a lot user connections with 'sleeping' mode in sysprocesses. I
> killed them with the status='sleeping', I realized that i might also
killed
> some connections are not supposed to be killed.
> >
> > After that, I checked event viewer, I saw 'Unable to read local eventlog
> (reason: The data area passed to a system call is too small). '
> >
> > When I restarted the sqlserveragent service, I got'Could not start
> SQLserverAgent Service on local computer. The service did not return an
> error. This could be an internal Windows error or an internal service
error.
> If this error persists, contact your system administrator. ' and also the
> following message appears in Application log '
> >
> > SQLServerAgent could not be started (reason: SQLServerAgent must be able
> to connect to SQLServer as SysAdmin, but '(Unknown)' is not a member of
the
> SysAdmin role). '
> >
> >
>|||clever! <g>
"DHatheway" <dlhatheway@.mmm.com.nospam> wrote in message
news:c5k11b$pfh$1@.tuvok3.mmm.com...
> If you want to kill off all your user connections, you might try a query
> like this:
> select 'kill '+convert(varchar(10),spid) from sysprocesses where dbid not
in
> (select dbid from sysdatabases where name in ('master','msdb') union all
> select 0 as dbid) order by sysprocesses.spid
> "Shannon" <anonymous@.discussions.microsoft.com> wrote in message
> news:128716F6-4D97-41E6-B8E6-EC001502D843@.microsoft.com...
> > We had a lot user connections with 'sleeping' mode in sysprocesses. I
> killed them with the status='sleeping', I realized that i might also
killed
> some connections are not supposed to be killed.
> >
> > After that, I checked event viewer, I saw 'Unable to read local eventlog
> (reason: The data area passed to a system call is too small). '
> >
> > When I restarted the sqlserveragent service, I got'Could not start
> SQLserverAgent Service on local computer. The service did not return an
> error. This could be an internal Windows error or an internal service
error.
> If this error persists, contact your system administrator. ' and also the
> following message appears in Application log '
> >
> > SQLServerAgent could not be started (reason: SQLServerAgent must be able
> to connect to SQLServer as SysAdmin, but '(Unknown)' is not a member of
the
> SysAdmin role). '
> >
> >
>
sql
