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?
Best Answer
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.