Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Mdf File Size is getting increased... Expand / Collapse
Author
Message
Posted Monday, July 28, 2014 5:08 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, December 12, 2014 2:56 AM
Points: 168, Visits: 538
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...
Post #1596764
Posted Monday, July 28, 2014 5:14 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: 2 days ago @ 12:52 PM
Points: 13,636, Visits: 11,509
Has data been added to the databases? (and maybe deleted again?)



How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1596769
Posted Monday, July 28, 2014 5:22 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, December 12, 2014 2:56 AM
Points: 168, Visits: 538
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...
Post #1596774
Posted Monday, July 28, 2014 6:26 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 6:20 AM
Points: 6,752, Visits: 14,400
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

SELECT df.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"
Post #1596804
Posted Monday, July 28, 2014 7:05 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, December 12, 2014 2:56 AM
Points: 168, Visits: 538
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...
Post #1596829
Posted Monday, July 28, 2014 7:45 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 6:20 AM
Points: 6,752, Visits: 14,400
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"
Post #1596864
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse