As a DBA, there are occasions where we are required to collect details from a database which can be used for further analysis. For example, getting the status of the database (i.e. File Size, Free Space, Status of the database, and who is the current owner of the database).
This kind of information is required, and very useful, for auditing purposes in addition to tracking the database/database file's size for various reasons. I had a script which does this job for me, exactly the way I want it; however, I have to run it for each database separately.
Evolution of the Script: From Single to Multi-Database
One fine day, while answering some questions on a forum, I found a script by Dan Guzman which retrieved most of the information I needed, and it does this for all the databases. I have adopted Dan G.'s script for my use and modified it by adding some more details to it.
Please review the script below. Let me know if you like it or dislike it. I will try to make further improvements on this script.
T-SQL Script: Comprehensive Database Space and Status Analysis
-- Script Originally Written By: Dan Guzman | https://www.dbdelta.com/ -- Modified by: Hemantgiri S. Goswami -- Reference: -- https://social.msdn.microsoft.com/Forums/en/transactsql/thread/226bbffc-2cfa-4fa8-8873-48dec6b5f17f --================================================================================================================== DECLARE @SqlStatement NVARCHAR(MAX) ,@DatabaseName SYSNAME; IF OBJECT_ID(N'tempdb..#DatabaseSpace') IS NOT NULL DROP TABLE #DatabaseSpace; CREATE TABLE #DatabaseSpace ( SERVERNAME SYSNAME, DBID INT, DATABASE_NAME SYSNAME, Recovery_Model VARCHAR(15), DBOWNER VARCHAR(25), LOGICAL_NAME SYSNAME, FILE_PATH SYSNAME, FILE_SIZE_GB DECIMAL(12, 2), SPACE_USED_GB DECIMAL(12, 2), FREE_SPACE_GB DECIMAL(12, 2), GROWTH_OPTION VARCHAR(15), MAXIMUM_SIZE INT, AUTOGROWTH INT, DB_STATUS VARCHAR(100) ); DECLARE DatabaseList CURSOR LOCAL FAST_FORWARD FOR SELECT name FROM sys.databases WHERE STATE = 0; OPEN DatabaseList; WHILE 1 = 1 BEGIN FETCH NEXT FROM DatabaseList INTO @DatabaseName; IF @@FETCH_STATUS = -1 BREAK; SET @SqlStatement = N'USE ' + QUOTENAME(@DatabaseName) + CHAR(13)+ CHAR(10) + N'INSERT INTO #DatabaseSpace SELECT [ServerName] = @@ServerName ,[DBID] = SD.DBID ,[DATABASE_NAME] = DB_NAME() ,[Recovery_Model] = d.recovery_model_desc ,[DBOwner] = SUSER_SNAME(sd.sid) ,[LOGICAL_NAME] = f.name ,[File_Path] = sf.filename ,[FILE_SIZE_GB] = (CONVERT(decimal(12,2),round(f.size/128.000,2))/1024) ,[SPACE_USED_GB] = (CONVERT(decimal(12,2),round(fileproperty(f.name,''SpaceUsed'')/128.000,2))/1024) ,[FREE_SPACE_GB] = (CONVERT(decimal(12,2),round((f.size-fileproperty(f.name,''SpaceUsed''))/128.000,2))/1024) ,[Growth_Option] = case sf.status & 0x100000 WHEN 1048576 THEN ''Percentage'' WHEN 0 THEN ''MB'' END ,[Maximum_Size] = SF.MaxSize ,[AutoGrowth(MB)] = (SF.Growth*8/1024) ,[DB_Status] = CASE SD.STATUS WHEN 0 THEN ''Normal'' WHEN 1 THEN ''autoclose'' WHEN 2 THEN ''2 not sure'' WHEN 4 THEN ''select into/bulkcopy'' WHEN 8 THEN ''trunc. log on chkpt'' WHEN 16 THEN ''torn page detection'' WHEN 20 THEN ''Normal'' WHEN 24 THEN ''Normal'' WHEN 32 THEN ''loading'' WHEN 64 THEN ''pre recovery'' WHEN 128 THEN ''recovering'' WHEN 256 THEN ''not recovered'' WHEN 512 THEN ''offline'' WHEN 1024 THEN ''read only'' WHEN 2048 THEN ''dbo use only'' WHEN 4096 THEN ''single user'' WHEN 8192 THEN ''8192 not sure'' WHEN 16384 THEN ''16384 not sure'' WHEN 32768 THEN ''emergency mode'' WHEN 65536 THEN ''online'' WHEN 131072 THEN ''131072 not sure'' WHEN 262144 THEN ''262144 not sure'' WHEN 524288 THEN ''524288 not sure'' WHEN 1048576 THEN ''1048576 not sure'' WHEN 2097152 THEN ''2097152 not sure'' WHEN 4194304 THEN ''autoshrink'' WHEN 1073741824 THEN ''cleanly shutdown'' END FROM SYS.DATABASE_FILES F JOIN MASTER.DBO.SYSALTFILES SF ON F.NAME COLLATE database_default = SF.NAME COLLATE database_default JOIN MASTER.SYS.SYSDATABASES SD ON SD.DBID = SF.DBID JOIN MASTER.SYS.DATABASES D ON D.DATABASE_ID = SD.DBID AND DATABASEPROPERTYEX(SD.NAME,''Updateability'') <> ''OFFLINE'' ORDER BY [File_Size_GB] DESC'; EXECUTE(@SqlStatement); END CLOSE DatabaseList; DEALLOCATE DatabaseList; SELECT * FROM #DatabaseSpace; DROP TABLE #DatabaseSpace; GO
Update: Resolving Collation Mismatches
The code has been updated to display the output even if the collation is different for some databases, using the COLLATE database_default clause within the join logic.
Database Managed Services
Ready for better database management—for less?
.svg){kind=small}
.svg){kind=small}