Mdf File Size is getting increased...

  • Hi Experts....

    Am using 2008R2 in my location..

    I have a Problem like the Following ..

    On 24th my Mdf size was 10GB,when i checked now the Mdf size was increased suddenly to 30GB.

    Please give me some solution to decrease the Size and as well as where can i check the reasons behind that..

    Awaiting for your valuable Response...

    Regards

    Chowdary...

    Regards
    Chowdary...

  • Has data been added to the databases? (and maybe deleted again?)

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • Thank you For Replying Mr.Koen....

    How can i check the recently added Data,Is there any chances to check whether the data added or not?

    Pls help me out...

    Regards
    Chowdary...

  • Chowdary's (7/28/2014)


    On 24th my Mdf size was 10GB,when i checked now the Mdf size was increased suddenly to 30GB.

    First ascertain whether the file has free space or filled with data.

    If the data file is near to full no amount of shrinking will reduce it in size!!

    Chowdary's (7/28/2014)


    Please give me some solution to decrease the Size

    Run this query against the database in question

    SET NOCOUNT ON

    SELECTdf.name AS LogicalFileName

    , ISNULL(fg.name, 'Log') AS FilegroupName

    , physical_name AS PhysicalOSName

    , (df.size / 128) AS SizeMBs

    , (FILEPROPERTY(df.name, 'SpaceUsed') / 128) AS SpaceUsedMBs

    , CASE df.growth

    WHEN 0 THEN 'No growth'

    ELSE 'Growth allowed'

    END AS GrowthAllowed

    , CASE

    WHEN df.max_size / 128 = 0 THEN CAST(df.size / 128 as varchar(50)) + ' MBs'--'Unlimited'

    WHEN df.max_size / 128 = 2097152 and df.growth = 0 THEN 'No growth'

    WHEN df.max_size = 268435456 THEN '2TB'

    ELSE CAST(df.max_size / 128 AS VARCHAR(10)) + ' MBs'

    END AS MaxGrowthSize

    , CASE df.is_percent_growth

    WHEN 0 THEN CAST(df.growth / 128 AS VARCHAR(10)) + ' MBs'

    ELSE CAST(df.growth AS VARCHAR(10)) + ' %'

    END AS Growth

    , (df.size / 128) - (FILEPROPERTY(df.name, 'SpaceUsed') / 128) AS FreeMBs

    FROM sys.database_files df LEFT OUTER JOIN sys.filegroups fg ON df.data_space_id = fg.data_space_id

    ORDER BY df.type

    -----------------------------------------------------------------------------------------------------------

    "Ya can't make an omelette without breaking just a few eggs" 😉

  • Dear Mr.Perry Whittle Thank you for replying,

    I executed the Script against the DB , i got the following

    SpaceUsedMbs, SizeMBs, GrowthAllowed, MaxGrowthSize,Growth and FreeMBs result.But from this result how can i reduce the Size of MDF.

    >Here SpaceUsedMbs, SizeMBs showing as same size (Ex:198Mb)

    >MaxGrowthSize is showing as 198MB (Ex:)

    >Growth is 16Mb (Ex)

    >FreeMBs is showing 0

    Can u pls Explain this as am not able to get the resolution to reduce the MDF size.

    Regards
    Chowdary...

  • Chowdary's (7/28/2014)


    Dear Mr.Perry Whittle Thank you for replying,

    I executed the Script against the DB , i got the following

    SpaceUsedMbs, SizeMBs, GrowthAllowed, MaxGrowthSize,Growth and FreeMBs result.But from this result how can i reduce the Size of MDF.

    >Here SpaceUsedMbs, SizeMBs showing as same size (Ex:198Mb)

    >MaxGrowthSize is showing as 198MB (Ex:)

    >Growth is 16Mb (Ex)

    >FreeMBs is showing 0

    Can u pls Explain this as am not able to get the resolution to reduce the MDF size.

    Are you sure you have executed the query against the correct database?

    It's likely you won't be able to shrink the database, it depends on whether its full of data or not.

    -----------------------------------------------------------------------------------------------------------

    "Ya can't make an omelette without breaking just a few eggs" 😉

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply