none
Can you force a database to auto close?

    คำถาม

  • We have pieces of our own in house developed equipment which connect to our website regularly, the website connects to a SQL Database. Both the website and database are on Windows server 2008 running SQL Express 2008.

    Every 7-8 days we get an issue where the SQL database can't be written to for exactly 60 seconds each time, the time of day this happens is also random.

    I have auto close turned off now but it was enabled, is there a way to force a database to auto close for testing purposes? to see if this could be the cause, so I don't have to wait for another week to find out or is it possible to log when the database auto closes? 

    Any suggestions on what could cause this or troubleshooting tips would be appreciated

     
    • แก้ไขโดย MartynTVC 13 มีนาคม 2555 14:40
    13 มีนาคม 2555 14:31

คำตอบ

  • Hi,

    The Auto-close property kicks in when the last user connection disconnects.  So in that sense, you can 'force' auto-close by forcibly disconnecting all your user connections.

    The sort of problem you're describing usually occurs when something external to SQL Server gets a handle on the database files whilst they're closed.  For example, Anti Virus comes along and scans your database file .... in the meantime, a user tries to connect to the database and SQL Server tries to open it.  However, it's exclusively locked by the AV program.  In this instance, you should set AV to exclude the SQL Server binaries, data files, etc.

    Anyway, best general advice is to turn off auto-close anyway



    Thanks,

    Andrew Bainbridge
    SQL Server DBA

    Please click "Propose As Answer" if a post solves your problem, or "Vote As Helpful" if a post has been useful to you


    • แก้ไขโดย Andrew Bainbridge 13 มีนาคม 2555 14:42
    • ทำเครื่องหมายเป็นคำตอบโดย Iric WenModerator 28 มีนาคม 2555 8:45
    13 มีนาคม 2555 14:41

ตอบทั้งหมด

  • Hi,

    The Auto-close property kicks in when the last user connection disconnects.  So in that sense, you can 'force' auto-close by forcibly disconnecting all your user connections.

    The sort of problem you're describing usually occurs when something external to SQL Server gets a handle on the database files whilst they're closed.  For example, Anti Virus comes along and scans your database file .... in the meantime, a user tries to connect to the database and SQL Server tries to open it.  However, it's exclusively locked by the AV program.  In this instance, you should set AV to exclude the SQL Server binaries, data files, etc.

    Anyway, best general advice is to turn off auto-close anyway



    Thanks,

    Andrew Bainbridge
    SQL Server DBA

    Please click "Propose As Answer" if a post solves your problem, or "Vote As Helpful" if a post has been useful to you


    • แก้ไขโดย Andrew Bainbridge 13 มีนาคม 2555 14:42
    • ทำเครื่องหมายเป็นคำตอบโดย Iric WenModerator 28 มีนาคม 2555 8:45
    13 มีนาคม 2555 14:41
  • Thanks, I will leave auto close turned off.

    Are there any error logs which would indicate when SQL auto closes the database? so I can track down if this happened at the same time as we had the problem.

    13 มีนาคม 2555 15:20
  • I think the only indication is when you see messages like "Starting up database...." in the SQL Server Error Log, which indicates that it was previously closed.



    Thanks,

    Andrew Bainbridge
    SQL Server DBA

    Please click "Propose As Answer" if a post solves your problem, or "Vote As Helpful" if a post has been useful to you

    14 มีนาคม 2555 9:16