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" 😉