ASP.NET Cafe
new tricks every week

MS SQL 2005 Performance tip: Turn off autoclose for your databases

Wednesday, 25 November 2009 08:40 by dmitriy
MS SQL 2005

For some reason by default you can have "AUTOCLOSE" property of your database set to ON. That's not bad, but if you check your server Event Log you can find a lot of entries like "Starting database 'xxxxxxx' ". And log is not a problem, but for sure this takes a time and slow down your asp.net application response.

You can turn it off in Managment Studio.
or you can run a little sql to update all databases on server:

EXECUTE sp_MSforeachdb
'
IF (''?'' NOT IN (''master'', ''tempdb'', ''msdb'', ''model''))
   EXECUTE (''ALTER DATABASE [?] SET AUTO_CLOSE OFF WITH NO_WAIT'')
'

All databases won't close automatically and response instantly.

Currently rated 5.0 by 1 people

  • Currently 5/5 Stars.
  • 1
  • 2
  • 3
  • 4
  • 5
Tags:   , ,
Categories:   SQL | SQL
Actions: E-mail | del.icio.us | Kick it! | DZone it! | Permalink | Comments (3) | Comment RSSRSS comment feed

Comments

Add comment


 

  Country flag

biuquote
  • Comment
  • Preview
Loading