Sql-server – SQL query output on one line

sql serversql-server-2017

Sorry, I'm not a expert in scripting,

I need to create a query in MS SQL Server to get db and db log size on one row. I am using PRTG to monitor 4 SQL servers and i need to know if there's any growth in the database and logs, eater in percentage or in bites,

I have found this new script that is perfect, it gives me all the info i need then i can set thresholds on the outputs, the problem is PRTG can only read the info form one row, so the only info that shows up in PRTG is the database info, i also need to get the log size.

I have found this script that gives me all the info i need and i would like to send the output to one row if possible.

USE [maccadk]
GO
SELECT
[TYPE] = A.TYPE_DESC
,[FILE_Name] = A.name
,[FILEGROUP_NAME] = fg.name
,[File_Location] = A.PHYSICAL_NAME
,[FILESIZE_MB] = CONVERT(DECIMAL(10,2),A.SIZE/128.0)
,[USEDSPACE_MB] = CONVERT(DECIMAL(10,2),A.SIZE/128.0 - ((SIZE/128.0) - CAST(FILEPROPERTY(A.NAME, 'SPACEUSED') AS INT)/128.0))
,[FREESPACE_MB] = CONVERT(DECIMAL(10,2),A.SIZE/128.0 - CAST(FILEPROPERTY(A.NAME, 'SPACEUSED') AS INT)/128.0)
,[FREESPACE_%] = CONVERT(DECIMAL(10,2),((A.SIZE/128.0 - CAST(FILEPROPERTY(A.NAME, 'SPACEUSED') AS INT)/128.0)/(A.SIZE/128.0))*100)
,[AutoGrow] = 'By ' + CASE is_percent_growth WHEN 0 THEN CAST(growth/128 AS VARCHAR(10)) + ' MB -'
WHEN 1 THEN CAST(growth AS VARCHAR(10)) + '% -' ELSE '' END
+ CASE max_size WHEN 0 THEN 'DISABLED' WHEN -1 THEN ' Unrestricted'
ELSE ' Restricted to ' + CAST(max_size/(128*1024) AS VARCHAR(10)) + ' GB' END
+ CASE is_percent_growth WHEN 1 THEN ' [autogrowth by percent, BAD setting!]' ELSE '' END
FROM sys.database_files A LEFT JOIN sys.filegroups fg ON A.data_space_id = fg.data_space_id
order by A.TYPE desc, A.NAME;

tables

Thanks
Gilles

Best Answer

Turning rows into columns known as a PIVOT operation. There are several ways to implement this operation, including a built-in expression. The simplest to understand, and most flexible, I find to be the CASE construct.

I'll use an in-line CTE to wrap your query. There's no magic here, it's just a convenient way of using your existing code. I've added column type_desc to help distinguish the source of each amount. The built-in function DB_NAME() is used to give a "key" value to the output.

;with YourData as
(
    select
        DBName = DB_NAME(),
        type_desc,
        [Name],
        physical_name [Path],
        CAST(size AS BIGINT)*8192 [TotalBytes], 
        CAST(FILEPROPERTY(name,'SpaceUsed') AS BIGINT)*8192 [UsedBytes], 
        (case when max_size<0 
             then -1 
             else CAST(max_size AS BIGINT)*8192 end) [MaxBytes]
    from sys.database_files
)
select
    DBName,
    RowsTotalBytes = sum(case when type_desc = 'ROWS' then TotalBytes end),
    LogTotalBytes  = sum(case when type_desc = 'LOG' then TotalBytes end)
from YourData
    group by DBName;

The values we want occur in your query as two rows in a single column. The CASE expressions separate them into individual columns, with a NULL in the other column, still in two rows. By grouping I collapse the two rows into as single row. Hence the purpose for introducing DB_NAME().

Using SUM() serves two purposes: it returns a single value for all files of a type and ignores the NULL introduced for the other type_desc.

If you have multiple data or log files and want a separate output value for each use Name in the when instead of type_desc.

Related Question