Maintenance Plan and Ghost SPIDs

Tech-Archive recommends: Fix windows errors by optimizing your registry

From: Bill Lucas (Homebrew42_at_hotmail.ccom)
Date: 05/24/04 

Date: Mon, 24 May 2004 12:44:07 -0400

Came in to work this morning and noticed one of our servers maintenance
plans had failed... Checked the log noticed that the integrity checks could
not be performed because a database could not be placed in single user
mode...

went and tried to put that database in single user mode manually and told I
wasn't allowed while there were still connections to that database.. Ok
Fair enough, but confusing.

I did an SP_Who and could not find one connection into the Troublesome
database. Which makes since because we have a job that fires at 8:30 in the
evening which kills any connections to the server that may have been left
open accidentally.

Went back and tried to put the database into single user mode and it still
failed complaining of open connections. Finally opened up Enterprise
Manager and tried to right click detach the database... It complained of 3
users connected. Went and checked the process info in EM and still could
not find any open connections.

Went back and detached the database via EM and told it to kill the
connections and it detached the database. Re ran the jobs that contained
the maintenance plans and everything went fine.

Can anyone tell me what happened? I couldn't find these connections EM was
complaining about anywhere...

Any info would be appreciated so I can try to prevent this in the future...

Regards,
Bill

Relevant Pages

  • Re: How can I open an SQL database and be the only one who has access to it?
    ... you can determine how SS permits access to your server. ... SINGLE_USER allows one user at a time to connect to the database. ... controlled by the termination clause of the ALTER DATABASE statement. ... To allow multiple connections, the database must be changed to ...
    (microsoft.public.data.ado)
  • Re: Drop all the connections to the DataBase
    ... If you have an active users in your database you will not be able use 'SET ... Cannot change the 'single user' option of a database while another user is ... Why would you KILL connections instead of using the "ALTER ... > limitation by killing all the active connections. ...
    (microsoft.public.sqlserver.server)
  • Re: ORA-12518, 04030 errors after 9i-10G upgrade.
    ... All went ok until users started connecting in 100+ numbers, database is ... set up for dedicated server connections, this was never a problem in 9i. ... This did not solve problems so bounced database and decreased SGA to ... out of process memory errors. ...
    (comp.databases.oracle.server)
  • Re: ORA-12518, 04030 errors after 9i-10G upgrade.
    ... All went ok until users started connecting in 100+ numbers, database is ... set up for dedicated server connections, this was never a problem in 9i. ... out of process memory errors. ... that Oracle is having issues with OS resources. ...
    (comp.databases.oracle.server)
  • Re: ORA-12518, 04030 errors after 9i-10G upgrade.
    ... New server has 6GB ram. ... All went ok until users started connecting in 100+ numbers, database is ... set up for dedicated server connections, this was never a problem in 9i. ... This did not solve problems so bounced database and decreased SGA to ...
    (comp.databases.oracle.server)