Kill SPs then sqlserveragent stop working

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 sy
stem 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). '
>
|||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...
> killed them with the status='sleeping', I realized that i might also
killed
> some connections are not supposed to be killed.
> (reason: The data area passed to a system call is too small). '
> 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 '
> to connect to SQLServer as SysAdmin, but '(Unknown)' is not a member of
the
> SysAdmin role). '
>