If you work with a SQL Server instance or an Azure SQL Managed Instance, then you might have many databases in that instance. If you need to collect information about these databases, then it can be time consuming, even with just a few databases.
There are many pieces of information you might need to collect, such as the recovery model, the compatibility level, the database size, and the log reuse wait reason. Instead of using a tool like SSMS to check these things manually one database at a time, a script would be a much better option here. You can get most of the information you need from sys.databases, and some others views and functions.
Why would you need to collect information about the databases? There are many reasons. Here are just a few examples:
You want to apply some configuration best practices and make sure that all databases are aligned. For example, you want to make sure that none of your databases has the Auto-Shrink property set to True.
You want to plan a maintenance plan for the databases in the instance, so you need to know things like the recovery model of each databases and whether a database is configured for accelerated database recovery.
You want to design a HA/DR solution, such as Always On Availability Group, so you need to know the database sizes and whether any of the databases is already part of an existing availability group.
So to save you precious time, here is a script that collects important information about all the databases in your instance. Enjoy!
Comments