How to reduce MDF file size ?

  • HI ALL,

    I have issue with my DATA file ( MDF) .. The usage is 99.87% for database DB1 . File size is 3 GB . How do I reduce it ?

    I have tried to shrink it by changing the recovery from FULL to SIMPLE and set it back to FULL . but it only help a bit .

    I notice the index Defragmentation is high ..

    Can I change the Initial Size into 1 GB for example ?

    Thanks..

    Cheers

  • Well, a couple things bear mentioning.

    First, switching the recovery model from Full to Simple, shrinking, and switching back to full is a terrible trick for shrinking the log file, not the data file (the .mdf is what you're trying to shrink).

    It's a bad way to reduce the size of the transaction log because any of the active log before you switched recovery model and shrank is just gone, so you've lost recoverability to those points in time (and point-in-time recovery is precisely why a DB would be in full recovery in the first place).

    Second, you're not going to be able to shrink the data file by much at all. If it's 99.87% used, then you would only be able to recover .0013*3000MB or 3.9 MB.

    Also, even if you could shrink the data file, that comes with its own list of problems. It's resource-intensive and inefficient, it fragments indexes terribly (as you noticed), and you'll probably use that space again eventually anyway.

    If you eventually use the space anyway, then not only did you use IO and CPU to perform the shrink and use more to correct the fragmentation (which, by the way, if you rebuild the indexes online, will end up creating a bunch of free space again), you'll end up just growing it back out, which means a lot of unnecessary work.

    The really short version of this is that you shouldn't consider shrinking your databases unless the circumstances absolutely, 100% dictate you need that space back, which is rarely the case.

    http://www.brentozar.com/archive/2009/08/stop-shrinking-your-database-files-seriously-now/ is a nice bit with a link to a bunch of other articles going into more detail about what I said above. Read those, and you'll definitely be convinced 🙂

    Having said all that, is the file's being 3 GB actually causing any problem?

    Cheers!

  • WhiteLotus (4/30/2015)


    HI ALL,

    Can I change the Initial Size into 1 GB for example ?

    No, you cannot reduce the size of this file without either purging data or compacting/compressing the content.

    😎

    My question is why?

  • Eirikur Eiriksson (4/30/2015)


    WhiteLotus (4/30/2015)


    HI ALL,

    Can I change the Initial Size into 1 GB for example ?

    No, you cannot reduce the size of this file without either purging data or compacting/compressing the content.

    😎

    My question is why?

    because the data file grows very big ( database usage is almost 100 %) now .. I just tried to index defragmentation and shrink data file ( it helps a bit ) but the index fragmentation become big now.

    If I only did Index defragmentation without shrinking . The data file will not be reduced

    I am still not satisfied with what i have done 🙁

  • I know you say it's grown very big, but is the size of the file actually causing any problems?

    If it is causing actual problems (for example, it's running the drive out of space, and space can't be added to the drive), then you need to figure out what's taking up the space, what's putting it there, and whether you need that data.

    If it's not causing actual problems (for example, the drive it's on has plenty of free space), then I wouldn't worry about it.

    If the file is completely full now, though, it'll start autogrowing when new data is added. It's best not to rely on autogrowth, so once you've figured out the things mentioned above, you'll want to size the file appropriately so that it has room to operate normally without needing to grow for a while.

    Alternatively, if the space is taken up by data you don't need and don't want to store, then you'll need to stop generating that data.

    I can't stress enough, though, that the crucial question here is whether the file's size is causing any actual issues (and no, being bigger than we thought it might be isn't an actual issue :-D)

    Cheers!

  • WhiteLotus (4/30/2015)


    Eirikur Eiriksson (4/30/2015)


    WhiteLotus (4/30/2015)


    HI ALL,

    Can I change the Initial Size into 1 GB for example ?

    No, you cannot reduce the size of this file without either purging data or compacting/compressing the content.

    😎

    My question is why?

    because the data file grows very big ( database usage is almost 100 %) now .. I just tried to index defragmentation and shrink data file ( it helps a bit ) but the index fragmentation become big now.

    If I only did Index defragmentation without shrinking . The data file will not be reduced

    I am still not satisfied with what i have done 🙁

    No matter what the problem actually is, in this case shrinking the file is absolutely the very worst thing to do, not only will you not gain any space as the file is full but you will completely fragment the content of the file.

    😎

  • 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.

    "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

  • Grant Fritchey (5/1/2015)


    ... I'd suggest moving this to a larger drive so you can have it be a whopping 4gb in size.

    That's a giant leap, maybe 3328 Mb would be enough for now:-D

    😎

  • 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 ???

  • I want to make clear here .. it is not disk space problem but the problem is data file is too big for some user databases . One of databases has 99% full of its data file (MDF) so i need to reduce it .

  • WhiteLotus (5/1/2015)


    I want to make clear here .. it is not disk space problem but the problem is data file is too big for some user databases . One of databases has 99% full of its data file (MDF) so i need to reduce it .

    Wrong again, you need to expand your data file! Here is a hint

    😎

    USE [master]

    GO

    ALTER DATABASE [DATABASE_NAME] MODIFY FILE ( NAME = N'[LOGICAL_FILE_NAME]', SIZE = 3407872KB ) /*3328Mb*/

    GO

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

  • 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.

    "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

  • 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

    😎

  • 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 ?

Viewing 15 posts - 1 through 15 (of 26 total)

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