Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have several databases for legacy applications which run in VMs that are currently off until someone needs to look at some history. I have set each of the databases used by these applications to offline but I wanted to know:

Is there a performance benefit to having these unused databases in the offline state instead of sitting online but unused without any connections to them or queries run against them?

share|improve this question

3 Answers 3

up vote 9 down vote accepted

If the database has objects in memory, taking it offline will remove those objects from the buffer. This frees up memory for other database objects.

Also, taking it offline ensures that automated maintenance plans (backups, index maintenance, integrity checks...) don't waste any resources doing maintenance on it.

share|improve this answer

If there is no connection to the databases, I don’t think they are using SQL server resources. You can take full backup of these DBs and remove them from servers or take them offline and if needed,

You can bring them back online right away, However, this will cause errors for administrative jobs running on all DBs like backup all user DBs, reindex all user databases

share|improve this answer

You could instead turn on the AUTO_CLOSE on these databases:

When set to ON, the database is shut down cleanly and its resources are freed after the last user exits. The database automatically reopens when a user tries to use the database again. When set to OFF, the database remains open after the last user exits.

While generally shun, this setting is valid for databases that are truly seldom used. simply accessing the database will open it, there is no need for manual intervention.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.