July 28, 2014 at 5:08 am
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...
July 28, 2014 at 5:14 am
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
July 28, 2014 at 5:22 am
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...
July 28, 2014 at 6:26 am
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" 😉
July 28, 2014 at 7:05 am
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...
July 28, 2014 at 7:45 am
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