SQL Server Performance

SQL Server Error

Discussion in 'SQL Server Clustering' started by ahmed32, May 27, 2007.

  1. ahmed32 New Member

    hi everybody<br /><br />I Have SQL2000 installed on Cluster i found a strange error in the SQL Server Error Reapet every 2 minutes the messege is"Starting up Database'Database Name'" Source<img src='/community/emoticons/emotion-7.gif' alt=':S' />PID55<br />there is no Service Pack Installed nothing new happen the users are working but the system becomming slow<br /><br />Thanks alot for your Help<br /><br />Ahmed
  2. MohammedU New Member

    It is nothing to with Service Pack...
    I believe your database option "Auto close' is enabled...
    When there is no activity sql server will close the db files and when some one try to access then it will write Starting up database....

    exec sp_dboption 'pubs', 'autoclose', 'false'


    MohammedU.
    Moderator
    SQL-Server-Performance.com

    All postings are provided “AS IS” with no warranties for accuracy.
  3. ahmed32 New Member

    but it's not mentioned in the log that the file is close it's just open every 2 minutes plus there is also open for another Databases which is not used by any users but this happen every 30 minutes(open for non used Database)
  4. MohammedU New Member

    I don't know exactly after how many minutes of no activity it closes but it is not advisable to enabled this option...


    Auto Close (autoclose) When TRUE, the database is closed and resources are freed up when the last user connection ends and all database processes are completed. The database reopens automatically when a user tries to use the database again. In the SQL Server Desktop Edition, this option is set to TRUE by default. All other editions set this option to FALSE by default. When FALSE, the database remains open even if no users are currently using it.

    When the database is set to AUTOCLOSE = ON, an operation that initiates an automatic database shutdown clears the plan cache for the instance of SQL Server. Clearing the plan cache causes a recompilation of all subsequent execution plans and can cause a sudden, temporary decrease in query performance.

    MohammedU.
    Moderator
    SQL-Server-Performance.com

    All postings are provided “AS IS” with no warranties for accuracy.
  5. satya Moderator

    AUTOCLOSE option will close the database if there are no users connected and whenever a process requesting connection then you would see such messages, as explained it is not advisable if this is a production server.

    Satya SKJ
    Microsoft SQL Server MVP
    Writer, Contributing Editor & Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.
  6. ahmed32 New Member

    thanks mohamed and satya it has disappear after modifiying this option
  7. satya Moderator

    Also I would like to suggest about testing and applying latest service pack on SQL Server.

    Satya SKJ
    Microsoft SQL Server MVP
    Writer, Contributing Editor & Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.
  8. MohammedU New Member

    Satya,

    Do you know after how many minutes/seconds of users closed their connections database will be close when autoclose is configured?


    MohammedU.
    Moderator
    SQL-Server-Performance.com

    All postings are provided “AS IS” with no warranties for accuracy.
  9. satya Moderator

    I haven't looked into that but I guess it will be instant as soon as a process completes on that database, may be with trace flags you can find it.

    Satya SKJ
    Microsoft SQL Server MVP
    Writer, Contributing Editor & Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.

Share This Page