Post reply

How to reduce MDF file size ?

  • May 1, 2015 at 7:58 am

    #1794988

    Grant Fritchey (5/1/2015)

    WhiteLotus (5/1/2015)

    Grant Fritchey (5/1/2015)

    3gb is teeny tiny as databases go. Since you're at 99% usage on the data, I'd suggest moving this to a larger drive so you can have it be a whopping 4gb in size.

    Just to repeat what the others have said, you're fragmenting the files by constantly shrinking the database. There's no easy way to recover from that either. Please stop.

    Honestly my drive doesnt run out of space .. there are still many free space there. But the problem is i hv a monitoring tool that keep giving me the alert saying data file usage is almost 100% .

    So i tried so hard to solve this problem .

    If i move this data file in other bigger drive .i dont think it will solve problem because data file will be still the same size ( just different location ) .. Am i right ???

    '

    Yes. You are. But, you can expand the size of the file. Add one more gb and your alert should go away. You can't shrink the internal data. You can remove it. But other than that, there's little you can do. You're approaching your alert from the monitoring software incorrectly. Either set an exception for this database, or grow the file so that you have available space.

    Seems greatt .. can i do the same thing to system database such as master, model, tempdb ..they all hv the same problems..

  • May 1, 2015 at 8:00 am

    #1794990

    WhiteLotus (5/1/2015)

    Eirikur Eiriksson (5/1/2015)

    WhiteLotus (5/1/2015)

    Ohh nicee thx ..now i am thinking to expand it into 5GB . What do u think ?

    That is probably fine, that would be 5120MB or 5242880KB

    😎

    Greatt ... what about if i set autogrow by 256 MB to unlimited ?

    Autogrowth is fine but set it to a real limit, or in other words don't over provision the storage.

    😎

  • May 1, 2015 at 8:07 am

    #1794993

    WhiteLotus (5/1/2015)

    Grant Fritchey (5/1/2015)

    WhiteLotus (5/1/2015)

    Grant Fritchey (5/1/2015)

    3gb is teeny tiny as databases go. Since you're at 99% usage on the data, I'd suggest moving this to a larger drive so you can have it be a whopping 4gb in size.

    Just to repeat what the others have said, you're fragmenting the files by constantly shrinking the database. There's no easy way to recover from that either. Please stop.

    Honestly my drive doesnt run out of space .. there are still many free space there. But the problem is i hv a monitoring tool that keep giving me the alert saying data file usage is almost 100% .

    So i tried so hard to solve this problem .

    If i move this data file in other bigger drive .i dont think it will solve problem because data file will be still the same size ( just different location ) .. Am i right ???

    '

    Yes. You are. But, you can expand the size of the file. Add one more gb and your alert should go away. You can't shrink the internal data. You can remove it. But other than that, there's little you can do. You're approaching your alert from the monitoring software incorrectly. Either set an exception for this database, or grow the file so that you have available space.

    Seems greatt .. can i do the same thing to system database such as master, model, tempdb ..they all hv the same problems..

    Yes you need to let all those databases grow as much as they need to, they shouldn't take much space and bad things happen if they run out of space, unless you're putting user tables in them for some reason.....

  • May 1, 2015 at 8:31 am

    #1795004

    ZZartin (5/1/2015)

    WhiteLotus (5/1/2015)

    Grant Fritchey (5/1/2015)

    WhiteLotus (5/1/2015)

    Grant Fritchey (5/1/2015)

    3gb is teeny tiny as databases go. Since you're at 99% usage on the data, I'd suggest moving this to a larger drive so you can have it be a whopping 4gb in size.

    Just to repeat what the others have said, you're fragmenting the files by constantly shrinking the database. There's no easy way to recover from that either. Please stop.

    Honestly my drive doesnt run out of space .. there are still many free space there. But the problem is i hv a monitoring tool that keep giving me the alert saying data file usage is almost 100% .

    So i tried so hard to solve this problem .

    If i move this data file in other bigger drive .i dont think it will solve problem because data file will be still the same size ( just different location ) .. Am i right ???

    '

    Yes. You are. But, you can expand the size of the file. Add one more gb and your alert should go away. You can't shrink the internal data. You can remove it. But other than that, there's little you can do. You're approaching your alert from the monitoring software incorrectly. Either set an exception for this database, or grow the file so that you have available space.

    Seems greatt .. can i do the same thing to system database such as master, model, tempdb ..they all hv the same problems..

    Yes you need to let all those databases grow as much as they need to, they shouldn't take much space and bad things happen if they run out of space, unless you're putting user tables in them for some reason.....

    Agreed, but set limits so you don't fill the drives.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • May 1, 2015 at 4:46 pm

    #1795147

    Grant Fritchey (5/1/2015)

    ZZartin (5/1/2015)

    WhiteLotus (5/1/2015)

    Grant Fritchey (5/1/2015)

    WhiteLotus (5/1/2015)

    Grant Fritchey (5/1/2015)

    3gb is teeny tiny as databases go. Since you're at 99% usage on the data, I'd suggest moving this to a larger drive so you can have it be a whopping 4gb in size.

    Just to repeat what the others have said, you're fragmenting the files by constantly shrinking the database. There's no easy way to recover from that either. Please stop.

    Honestly my drive doesnt run out of space .. there are still many free space there. But the problem is i hv a monitoring tool that keep giving me the alert saying data file usage is almost 100% .

    So i tried so hard to solve this problem .

    If i move this data file in other bigger drive .i dont think it will solve problem because data file will be still the same size ( just different location ) .. Am i right ???

    '

    To be honest i wonder why that data file can't grow anymore . The previous setting for autogrow is by 10% to unlimited . I know it is not good if i let it grow by % So i change it by fixed amount which is 256 MB . But my question is Why they Dont Grow ??

    Yes. You are. But, you can expand the size of the file. Add one more gb and your alert should go away. You can't shrink the internal data. You can remove it. But other than that, there's little you can do. You're approaching your alert from the monitoring software incorrectly. Either set an exception for this database, or grow the file so that you have available space.

    Seems greatt .. can i do the same thing to system database such as master, model, tempdb ..they all hv the same problems..

    Yes you need to let all those databases grow as much as they need to, they shouldn't take much space and bad things happen if they run out of space, unless you're putting user tables in them for some reason.....

    Agreed, but set limits so you don't fill the drives.

  • May 3, 2015 at 5:56 pm

    #1795273

    WhiteLotus (5/1/2015)

    Grant Fritchey (5/1/2015)

    ZZartin (5/1/2015)

    WhiteLotus (5/1/2015)

    Grant Fritchey (5/1/2015)

    WhiteLotus (5/1/2015)

    Grant Fritchey (5/1/2015)

    3gb is teeny tiny as databases go. Since you're at 99% usage on the data, I'd suggest moving this to a larger drive so you can have it be a whopping 4gb in size.

    Just to repeat what the others have said, you're fragmenting the files by constantly shrinking the database. There's no easy way to recover from that either. Please stop.

    Honestly my drive doesnt run out of space .. there are still many free space there. But the problem is i hv a monitoring tool that keep giving me the alert saying data file usage is almost 100% .

    So i tried so hard to solve this problem .

    If i move this data file in other bigger drive .i dont think it will solve problem because data file will be still the same size ( just different location ) .. Am i right ???

    '

    To be honest i wonder why that data file can't grow anymore . The previous setting for autogrow is by 10% to unlimited . I know it is not good if i let it grow by % So i change it by fixed amount which is 256 MB . But my question is Why they Dont Grow ??

    Yes. You are. But, you can expand the size of the file. Add one more gb and your alert should go away. You can't shrink the internal data. You can remove it. But other than that, there's little you can do. You're approaching your alert from the monitoring software incorrectly. Either set an exception for this database, or grow the file so that you have available space.

    Seems greatt .. can i do the same thing to system database such as master, model, tempdb ..they all hv the same problems..

    Yes you need to let all those databases grow as much as they need to, they shouldn't take much space and bad things happen if they run out of space, unless you're putting user tables in them for some reason.....

    Agreed, but set limits so you don't fill the drives.

    Actually I wonder why that data file didn’t grow ?

    ( I checked the previous setting is auto growth by 10% ) . I know it is not a good setting ( by %) so I changed it using fixed amount ( by 256 MB) .

    If it did grow I would not have issue with data file ..

  • May 3, 2015 at 10:01 pm

    #1795282

    WhiteLotus (5/3/2015)

    WhiteLotus (5/1/2015)

    Grant Fritchey (5/1/2015)

    ZZartin (5/1/2015)

    WhiteLotus (5/1/2015)

    Grant Fritchey (5/1/2015)

    WhiteLotus (5/1/2015)

    Grant Fritchey (5/1/2015)

    3gb is teeny tiny as databases go. Since you're at 99% usage on the data, I'd suggest moving this to a larger drive so you can have it be a whopping 4gb in size.

    Just to repeat what the others have said, you're fragmenting the files by constantly shrinking the database. There's no easy way to recover from that either. Please stop.

    Honestly my drive doesnt run out of space .. there are still many free space there. But the problem is i hv a monitoring tool that keep giving me the alert saying data file usage is almost 100% .

    So i tried so hard to solve this problem .

    If i move this data file in other bigger drive .i dont think it will solve problem because data file will be still the same size ( just different location ) .. Am i right ???

    '

    To be honest i wonder why that data file can't grow anymore . The previous setting for autogrow is by 10% to unlimited . I know it is not good if i let it grow by % So i change it by fixed amount which is 256 MB . But my question is Why they Dont Grow ??

    Yes. You are. But, you can expand the size of the file. Add one more gb and your alert should go away. You can't shrink the internal data. You can remove it. But other than that, there's little you can do. You're approaching your alert from the monitoring software incorrectly. Either set an exception for this database, or grow the file so that you have available space.

    Seems greatt .. can i do the same thing to system database such as master, model, tempdb ..they all hv the same problems..

    Yes you need to let all those databases grow as much as they need to, they shouldn't take much space and bad things happen if they run out of space, unless you're putting user tables in them for some reason.....

    Agreed, but set limits so you don't fill the drives.

    Actually I wonder why that data file didn’t grow ?

    ( I checked the previous setting is auto growth by 10% ) . I know it is not a good setting ( by %) so I changed it using fixed amount ( by 256 MB) .

    If it did grow I would not have issue with data file ..

    It won't grow until it is full. Since you had free space, it would not have triggered a growth.

    I'd bet that if the server had been up long enough, you could track several growths in the default trace showing that the autogrow setting was indeed working.

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • May 3, 2015 at 10:06 pm

    #1795283

    I see..Hmm how do I trace it then ?

    cheers

  • May 4, 2015 at 8:02 am

    #1795355

    Like Jason said, this is recorded in the default trace, so you can just query it to look for growth events.

    Something like this would do the trick:

    DECLARE @filename NVARCHAR(4000);

    -- Current default trace

    SELECT @filename = CAST(value AS NVARCHAR(4000))

    FROM ::

    FN_TRACE_GETINFO(DEFAULT)

    WHERE traceid = 1

    AND property = 2

    -- Preserve the path and replace the current default trace with log.trc

    SET @filename = LEFT(@filename,

    LEN(@filename) - CHARINDEX('\',REVERSE(@filename)))

    + '\log.trc'

    -- Auto growth events in the current trace file

    SELECT

    TE.name AS [EventName],

    T.DatabaseName,

    t.DatabaseID,

    t.NTDomainName,

    t.ApplicationName,

    t.LoginName,

    t.SPID,

    t.IntegerData/128 AS MBGrown,

    t.Duration,

    t.StartTime,

    t.EndTime,

    t.textdata

    FROM sys.fn_trace_gettable ( @filename, DEFAULT) T

    JOIN sys.trace_events TE ON T.EventClass = TE.trace_event_id

    WHERE TE.name like '%grow%'

    ORDER BY t.StartTime;

    Cheers!

  • May 4, 2015 at 8:09 am

    #1795358

    WhiteLotus (5/3/2015)

    I see..Hmm how do I trace it then ?

    cheers

    Here is an article with links to other articles.

    http://bit.ly/FileSizeChange

    I am trying to find the pertinent info link/info for the default trace.

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • May 5, 2015 at 1:07 am

    #1795520

    SQLRNNR (5/4/2015)

    WhiteLotus (5/3/2015)

    I see..Hmm how do I trace it then ?

    cheers

    Here is an article with links to other articles.

    http://bit.ly/FileSizeChange

    I am trying to find the pertinent info link/info for the default trace.

    Many thanks Mate ..

    cheers:)

  • May 5, 2015 at 1:07 am

    #1795521

    Jacob Wilkins (5/4/2015)

    Like Jason said, this is recorded in the default trace, so you can just query it to look for growth events.

    Something like this would do the trick:

    DECLARE @filename NVARCHAR(4000);

    -- Current default trace

    SELECT @filename = CAST(value AS NVARCHAR(4000))

    FROM ::

    FN_TRACE_GETINFO(DEFAULT)

    WHERE traceid = 1

    AND property = 2

    -- Preserve the path and replace the current default trace with log.trc

    SET @filename = LEFT(@filename,

    LEN(@filename) - CHARINDEX('\',REVERSE(@filename)))

    + '\log.trc'

    -- Auto growth events in the current trace file

    SELECT

    TE.name AS [EventName],

    T.DatabaseName,

    t.DatabaseID,

    t.NTDomainName,

    t.ApplicationName,

    t.LoginName,

    t.SPID,

    t.IntegerData/128 AS MBGrown,

    t.Duration,

    t.StartTime,

    t.EndTime,

    t.textdata

    FROM sys.fn_trace_gettable ( @filename, DEFAULT) T

    JOIN sys.trace_events TE ON T.EventClass = TE.trace_event_id

    WHERE TE.name like '%grow%'

    ORDER BY t.StartTime;

    Cheers!

    many thanks mate 🙂

Viewing 12 posts - 16 through 27 (of 27 total)

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

Cookies on SQLServerCentral

This website stores cookies on your computer.

These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.

To find out more about the cookies we use, see our Privacy Policy