I've been handed a SQL Server that is used as an MIS source. There are 4 databases that carry out the task of importing data from various sources, then manipulting that data, and offering the data for reporting purposes.
The vendor has also created several other databases (of which there are also 4), but no-one in my company seems to know the purpose of these dbs.
In the logs, there are approximately 8/9 messages per second - not every second, but numberous seconds per minute - stating....
Starting up database 'db_name'.
... each time, all 4 of the mysterious dbs appear.
I've checked the spid that is running this job this morning, and it seems to be NT AUTHORITY\SYSTEM connected to one of the original 4 report databases.
Does this have any affect on the performance of the server, or the specific db attached to the user?
And yes, I'll contact the vendor to find out why they've done this.
Before I do, could there be any benefit to this since the databases are used at least once a second (or maybe two seconds at most) throughout the day???
What this option does is closes the database connection after the last user disconnects from the database. when a user tries to acces the databases again and requests for the same, then the database has to be reopened, which takes time and overhead.
Since your database is accessed frequentl, it has to be closed and reopened frequently which puts a large performance on the server and the users or the application which is using it.