8

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?

11

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.

6

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.

1

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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