If you are wondering why I called this option as "Killer Option" then here is an answer:
If you turn on this option then what happens is as soon as the last connection to the database is closed, All the resources(CPU,memory,IO) will be freed up and database will be closed.
When new connection is made then SQL Server restarts the database, reassign those resources to the below databases and will also perform checkdb for consistency checks.
This can cause huge performance issue on your production database at day time.
So please check if you have not turned on this option by mistake.
I dont understand why Microsoft gives this option ???? :(
ReplyDeleteThis is because QA was not performed well enough before releasing the product ;)
ReplyDelete