locked
Why Database .mdf and .ldf showing huge size RRS feed

  • Question

  • User-807418713 posted

    Hello

    I have a database its showing .mdf and .ldf total size is 211MB

    I used this code to check size of table

    sELECT 
        t.NAME AS TableName,
        s.Name AS SchemaName,
        p.rows,
        SUM(a.total_pages) * 8 AS TotalSpaceKB, 
        CAST(ROUND(((SUM(a.total_pages) * 8) / 1024.00), 2) AS NUMERIC(36, 2)) AS TotalSpaceMB,
        SUM(a.used_pages) * 8 AS UsedSpaceKB, 
        CAST(ROUND(((SUM(a.used_pages) * 8) / 1024.00), 2) AS NUMERIC(36, 2)) AS UsedSpaceMB, 
        (SUM(a.total_pages) - SUM(a.used_pages)) * 8 AS UnusedSpaceKB,
        CAST(ROUND(((SUM(a.total_pages) - SUM(a.used_pages)) * 8) / 1024.00, 2) AS NUMERIC(36, 2)) AS UnusedSpaceMB
    FROM 
        sys.tables t
    INNER JOIN      
        sys.indexes i ON t.OBJECT_ID = i.object_id
    INNER JOIN 
        sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
    INNER JOIN 
        sys.allocation_units a ON p.partition_id = a.container_id
    LEFT OUTER JOIN 
        sys.schemas s ON t.schema_id = s.schema_id
    WHERE 
        t.NAME NOT LIKE 'dt%' 
        AND t.is_ms_shipped = 0
        AND i.OBJECT_ID > 255 
    GROUP BY 
        t.Name, s.Name, p.Rows
    ORDER BY 
        TotalSpaceMB DESC, t.Name

    it showing 72MB

    what was that balance how to see and remove that space

    Thanking You

    Wednesday, October 7, 2020 5:01 PM

Answers

  • User1535942433 posted

    Hi Gopi.MCA,

    As far as I think,database log file size will be populating continuously till the next transaction backup happens if the database recovery model is not simple and Transaction backup or Log shipping is not set up. However, upon creating a Log backup, there is definitely going to exist a free space. For example, consider taking the log backup of the file with size 1024MB. What happens after this is that all logs will be removed inside the file and as a result of this there will be a plethora of space.

    To shrink a file in SQL Server, we always use DBCC SHRINKFILE() command. This DBCC SHRINKFILE() command will release the free space for the input parameter.

    DBCC SHRINKFILE([FileName / FileID],[EMPTYFILE / [nMB(Amount for Shrink), NOTRUNCATE / TRUNCATEONLY]])

    When any transaction blocks or stuck any database transactions, database log file size can be rapidly increased and cross the expected output file size to handle this issue. Query  should be performed in each database to monitor the free space for all databases with the criteria and on the result set, space monitor criteria must be applied. Users can use sp_msforeachdb() procedure to execute monitor T-SQL queries in each database of the SQL Server.

    T-SQL Query to get total space and free space for database files:

    CREATE TABLE #FileSize
    (dbName NVARCHAR(128), 
        FileName NVARCHAR(128), 
        type_desc NVARCHAR(128),
        CurrentSizeMB DECIMAL(10,2), 
        FreeSpaceMB DECIMAL(10,2)
    );
        
    INSERT INTO #FileSize(dbName, FileName, type_desc, CurrentSizeMB, FreeSpaceMB)
    exec sp_msforeachdb 
    'use [?]; 
     SELECT DB_NAME() AS DbName, 
            name AS FileName, 
            type_desc,
            size/128.0 AS CurrentSizeMB,  
            size/128.0 - CAST(FILEPROPERTY(name, ''SpaceUsed'') AS INT)/128.0 AS FreeSpaceMB
    FROM sys.database_files
    WHERE type IN (0,1);';
        
    SELECT * 
    FROM #FileSize
    WHERE dbName NOT IN ('distribution', 'master', 'model', 'msdb')
    AND FreeSpaceMB > ?;
        
    DROP TABLE #FileSize;

    More details,you could refer to below article:

    https://www.sqlshack.com/how-to-determine-free-space-and-file-size-for-sql-server-databases/

    Best regards,

    Yijing Sun

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, October 8, 2020 5:07 AM