As a SQL Server DBA, you must know the detailed information about your databases, like file sizes, locations, etc. This information is easily extracted from your SQL Server instance with the correct script. In this article from Tim Ford, you will get the required information.
The other thing to consider is that all this information that’s returned through these various means don’t necessarily match in terms of units of measure: some of this information is returned as megabytes (mb) some as counts of data pages (which are 8 kilobytes (kb) each.) The disparity does not do us much good.
All this leads to the need to have a single option to go to in order to return information that provides data points that are valuable to the Database Administrator:
- Server Name
- Database Name
- File Name (both logical and physical)
- File Type
- File Size, Consumption, Free Space
- Growth Units (percent or a fixed mb size)
- Maximum File Size
Since there is no single option for aggregating this information together I had to build a stored procedure to collect it for me and it’s time to share it with all of you.
The underlying code taps into some of those sources I mentioned above: sys.database_files and the FILEPROPERTY() function. The remainder of it is pure math and converting 8kb pages (in some cases) into a megabyte unit. The stored procedure accepts two parameters:
@granularity: d | NULL – ‘d’ signifies a database level granularity and aggregates all size metrics for each data file (and separately the transaction log) into a single row.
@database_name: <database name> | NULL – if a database name is specified then only the results for that database are returned. Otherwise results for all databases on the instance are returned.
The following columns are returned when specifying a database-level of granularity:
The following columns are returned when specifying a file-level of granularity:
Read the entire article that includes the sample scripts.