I have an SQL Server 2012 and a 2008R2 running in my environment. I need to decommission the server if the server is not actually being used.
I did go through this and this where connection count is one of the parameters to check the same, and I did run that and the result was 0 but is that concrete enough for to me do away with this server? Thing is I do not have any ownership details of this server to even proceed further, so I will have to see in the server itself.
There are chances the database could have been restarted sometime back, which I'm not sure. Auditing and logon triggers have been disabled at present. Should I enable them? If yes, which auditing specifically I will have to enable? And what logon trigger?
There aren't many databases in the instance, about 3-4 only.
What can be done to come to a concrete conclusion to stop this instance from running?
Best Answer
SQL Server can log failed logins, successful logins, both or none to the SQL server log. Check your current setting and if it is not collecting both failed and successful logins change it so that it is. Once it's been running that way for a few weeks (preferably over a month end & quarter end) check to see if there has been any activity. If no one is logging in then you can run a final backup of your databases (you should probably include the master and msdb databases as well as the user databases). Move those backups to long term storage and shut down the instance. If no one complains after a while then you can de-com.
Under the server properties: