-1

I want to modify this sql query from this post: List databases and tablespace in SQL Server 2014.

IF OBJECT_ID('tempdb.dbo.#space') IS NOT NULL
    DROP TABLE #space

CREATE TABLE #space (
      database_id INT PRIMARY KEY
    , data_used_size DECIMAL(18,2)
    , log_used_size DECIMAL(18,2)
)

DECLARE @SQL NVARCHAR(MAX)

SELECT @SQL = STUFF((
    SELECT '
    USE [' + d.name + ']
    INSERT INTO #space (database_id, data_used_size, log_used_size)
    SELECT
          DB_ID()
        , SUM(CASE WHEN [type] = 0 THEN space_used END)
        , SUM(CASE WHEN [type] = 1 THEN space_used END)
    FROM (
        SELECT s.[type], space_used = SUM(FILEPROPERTY(s.name, ''SpaceUsed'') * 8. / 1024)
        FROM sys.database_files s
        GROUP BY s.[type]
    ) t;'
    FROM sys.databases d
    WHERE d.[state] = 0
    FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 2, '')

EXEC sys.sp_executesql @SQL

IF OBJECT_ID('tempdb.dbo.#table') IS NOT NULL
    DROP TABLE #table

CREATE TABLE #table (
      database_id INT
    , obj_name NVARCHAR(1000)
    , total_rows INT
    , total_space DECIMAL(18,2)
    , used_space DECIMAL(18,2)
    , unused_space DECIMAL(18,2)
    , index_space DECIMAL(18,2)
    , data_space DECIMAL(18,2)
)

SELECT @SQL = STUFF((
    SELECT '
    USE [' + d.name + ']
    INSERT INTO #table
    SELECT 
          DB_ID()
        , s.name + ''.'' + o.name
        , i.total_rows
        , total_space = i.total_pages * 8. / 1024
        , used_space = i.used_pages * 8. / 1024
        , unused_space = (i.total_pages - i.used_pages) * 8. / 1024
        , index_space = i.index_pages * 8. / 1024
        , data_space = data_pages * 8. / 1024
    FROM sys.objects o
    JOIN sys.schemas s ON o.[schema_id] = s.[schema_id]
    JOIN (
        SELECT
              i.[object_id]
            , total_pages = SUM(a.total_pages)
            , used_pages = SUM(a.used_pages)
            , index_pages = SUM(a.used_pages - CASE WHEN a.[type] != 1 THEN a.used_pages WHEN p.index_id IN (0, 1) THEN a.data_pages ELSE 0 END) 
            , data_pages = SUM(CASE WHEN a.[type] != 1 THEN a.used_pages WHEN p.index_id IN (0, 1) THEN a.data_pages END)
            , total_rows = SUM(CASE WHEN i.index_id IN (0, 1) AND a.[type] = 1 THEN p.[rows] END)
        FROM sys.indexes i
        JOIN sys.partitions p ON i.[object_id] = p.[object_id] AND i.index_id = p.index_id
        JOIN sys.allocation_units a ON p.[partition_id] = a.container_id
        WHERE i.is_disabled = 0
            AND i.is_hypothetical = 0
        GROUP BY i.[object_id]
    ) i ON o.[object_id] = i.[object_id]
    WHERE o.[type] = ''U''
        AND o.is_ms_shipped = 0;'
    FROM sys.databases d
    WHERE d.[state] = 0
    FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 2, '')

EXEC sys.sp_executesql @SQL

SELECT
      d.name
    , t.total_size
    , t.data_size
    , s.data_used_size
    , t.log_size
    , s.log_used_size
    , tt.*
FROM (
    SELECT
          database_id
        , log_size = CAST(SUM(CASE WHEN [type] = 1 THEN size END) * 8. / 1024 AS DECIMAL(18,2))
        , data_size = CAST(SUM(CASE WHEN [type] = 0 THEN size END) * 8. / 1024 AS DECIMAL(18,2))
        , total_size = CAST(SUM(size) * 8. / 1024 AS DECIMAL(18,2))
    FROM sys.master_files
    GROUP BY database_id
) t
JOIN sys.databases d ON d.database_id = t.database_id
LEFT JOIN #space s ON d.database_id = s.database_id
LEFT JOIN #table tt ON t.database_id = tt.database_id
ORDER BY t.total_size DESC, tt.total_space DESC

I would like to implement it without temporary table and optimize it for performance because I will execute it every minute. Can you propose some solution?

Also I get error when I execute this query:

(1 row(s) affected)
Warning: Null value is eliminated by an aggregate or other SET operation

4

I would like to implement it without temporary table and optimize it for performance because I will execute it every minute.

The temporary table is just a convenient place to accumulate the results from each database. This is a pretty standard method to combine the results of a dynamic SQL query executed in the context of multiple databases. Executing the query once per minute should not cause you any performance problems.

To see the results, simply select out the contents of the temporary table:

SELECT 
    S.database_id,
    S.data_used_size,
    S.log_used_size 
FROM #space AS S;

Once you have finished with it, you can drop the table using:

DROP TABLE #space;

Also I get error when I execute this query:

(1 row(s) affected)
Warning: Null value is eliminated by an aggregate or other SET operation

Neither of these are errors.

The (row(s affected) message is informational. You can turn this off using SET NOCOUNT ON; before executing the script if you like. To reverse this, use SET NOCOUNT OFF;.

The warning about nulls being eliminated is caused by the SUM expression. It is harmless, but if it bothers you, change the lines to replace null with zero using ISNULL or COALESCE:

SUM(ISNULL(CASE WHEN t.[type] = 0 THEN t.space_used END, 0)),
SUM(ISNULL(CASE WHEN t.[type] = 1 THEN t.space_used END, 0))

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.