The SQL script below will show you the summarized database file size and database log file sizes on the current database. This can be helpful to monitor the actual file sizes in a single row / line.
This script was also posted in the Paessler PRTG KB, where the initial request for this came up. Posting it here cause I think it can become useful for some. The initial code used and modified is from Tri Effendi SS of stack exchange.
You will need to adjust the USE statement in the first line to the database you want to gather the information from.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 | USE [master] SELECT MYROWS.DATABASENAME, SUM(MYROWS.FILESIZE) AS DATA_FILESIZE, SUM(MYROWS.USEDSPACE) AS DATA_USEDSPACE, SUM(MYROWS.FREESPACE) AS DATA_FREESPACE, SUM(MYROWS.[FREESPACE_%]) AS DATA_FREESPACE_PERCENT, SUM(MYLOGS.FILESIZE) AS LOG_FILESIZE, SUM(MYLOGS.USEDSPACE) AS LOG_USEDSPACE, SUM(MYLOGS.FREESPACE) AS LOG_FREESPACE, SUM(MYLOGS.[FREESPACE_%]) AS LOG_FREESPACE_PERCENT FROM ( SELECT DB_NAME() AS DATABASENAME ,[FILESIZE] = CONVERT(DECIMAL(10,2),A.SIZE/128.0) * 1024 * 1024 ,[USEDSPACE] = CONVERT(DECIMAL(10,2),A.SIZE/128.0 - ((SIZE/128.0) - CAST(FILEPROPERTY(A.NAME, 'SPACEUSED') AS INT)/128.0)) * 1024 * 1024 ,[FREESPACE] = CONVERT(DECIMAL(10,2),A.SIZE/128.0 - CAST(FILEPROPERTY(A.NAME, 'SPACEUSED') AS INT)/128.0) * 1024 * 1024 ,[FREESPACE_%] = CONVERT(DECIMAL(10,2),((A.SIZE/128.0 - CAST(FILEPROPERTY(A.NAME, 'SPACEUSED') AS INT)/128.0)/(A.SIZE/128.0))*100) FROM sys.database_files A LEFT JOIN sys.filegroups fg ON A.data_space_id = fg.data_space_id WHERE A.type_desc LIKE 'ROWS' ) MYROWS LEFT JOIN ( SELECT DB_NAME() AS DATABASENAME ,[FILESIZE] = CONVERT(DECIMAL(10,2),A.SIZE/128.0) * 1024 * 1024 ,[USEDSPACE] = CONVERT(DECIMAL(10,2),A.SIZE/128.0 - ((SIZE/128.0) - CAST(FILEPROPERTY(A.NAME, 'SPACEUSED') AS INT)/128.0)) * 1024 * 1024 ,[FREESPACE] = CONVERT(DECIMAL(10,2),A.SIZE/128.0 - CAST(FILEPROPERTY(A.NAME, 'SPACEUSED') AS INT)/128.0) * 1024 * 1024 ,[FREESPACE_%] = CONVERT(DECIMAL(10,2),((A.SIZE/128.0 - CAST(FILEPROPERTY(A.NAME, 'SPACEUSED') AS INT)/128.0)/(A.SIZE/128.0))*100) FROM sys.database_files A LEFT JOIN sys.filegroups fg ON A.data_space_id = fg.data_space_id WHERE A.type_desc LIKE 'LOG' ) MYLOGS ON MYROWS.DATABASENAME = MYROWS.DATABASENAME GROUP BY MYROWS.DATABASENAME |
