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

 > 

  • Question

  • Question
    Sign in to vote
    0
    Sign in to vote
    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

  • Question
    Sign in to vote
    0
    Sign in to vote
    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