Vision: SQL Homeostasis

Here is a DMV script to check whether a or any database has the auditing configured and running, along with few other useful attributes if it does. Few things to keep in mind: Auditing is available from SQL version 2008 and up.... Until very recently (SQL version 2016 and up), granual (object level) auditing at the database level is available only on Enterprise edition.  Although you could still use server level auditing to setup auditing at the database level So keeping that in mind, I wrote this in a way to be able to execute as a multi-server query either using CMS or just a bunch of locally registered servers. If you are running this as a multi-server script, you should turn on the "Merge Results" setting in the SSMS. Finally, if you feel that this script is unncessarily complicated, its because it is written in a way so that it can be executed against any version and editon of sql server, whether it supports auditing or not! Hope you find this use and please feel free to...

Do you ever wonder if there are any databases in your environment that may just be there but not being used? I needed to make a comprehensive list of such databases i n order to clean up old databases and prep old/existing environment and migrate and consolidate them into a new sql server infrastructure. So I wrote this DMV query to obtain when a database was last accessed for a user query as well as those that were left OFFLINE but never removed. I executed this against CMS / Multi Server query an whola... within few seconds I had the complete list or rather a preliminary list for my intended purpose. Couple things to note first: 1. I only tested and ran this against SQL versions 2008 and up. 2. It uses a global temp table and removes when done Hope you find this use and please feel free to leave comments/feedback/suggestions for further improvement. SET nocount ON SET TRANSACTION isolation level READ uncommitted USE master go IF Object_id ( 'tempdb..##t_dba_db_last_ac...