SQL Server Performance

How to reduce MDF file

Discussion in 'Performance Tuning for DBAs' started by essam636, Aug 10, 2003.

  1. essam636 New Member

    Dear All;

    I have a huge primary MDF - ~8.3GB. I've tried all the actions I know of to reduce the physical size of it... Backup up the DB, Backed up the transaction log, used DBCC ShrinkDatabase & DBCC ShrinkFile...but the MDF size remain same..!

    The first time I ran the DBCC ShrinkFile the transaction log went down to 5 MB -- but MDF remains at ~8.3GB. I used the Shrink command but this did not affect the size.

    does someone have any suggestions?

    Many Thanks,
    Essam Andrew
  2. bambola New Member

    try to rebuild indexes on tables, it might help, and then run
    DBCC SRHINKDATABASE('db_name', 10) -- 10 percent free space or whatever suits you

    Bambola.
  3. essam636 New Member

    Thanks Bambola....

    Actually, I rebuilt indexes as you advise me and I ran SHRINKDATABASE, but it reduced by 200 MB only..!

    is there any alternate way to reduce my huge MDF?

    Many Thanks..
    Essam Andrew
  4. bambola New Member

    Why do you expect it to be much smaller? And how do you read the size of the mdf file? from EM? QA?

    Bambola.
  5. essam636 New Member

    Enterprise manager, right click on DB, choose properties, then on Data File tab, in space allocated (MB), I find 8.3 GB as MDF size..!

    Any suggestions to reduce this MDF..?

    Regards..

    Essam Andrew
  6. bambola New Member

    Again, why do you expect to reduce this size bu much?
    What makes you think that it can be much smaller?

    Bambola.
  7. bambola New Member

    BTW, did you srhink it from EM or run from QA the DBCC SRHINKDATABASE command?

    Bambola.
  8. Argyle New Member

    How much space is actually used in the 8.3 GB MDF file? If 8.3 GB is used in the file then that is how much space the database will take up. It won't help to try and shrink the MDF file if all the space in it is used.

    If that is the case and you think the data in the database should be less then look for archive or log tables in the database. If there is a batch job or something logging to the database look if some of the data can be deleted or archived to another database. In EM choose "view taskpad" when looking at the database and go trough all tables to see if anyone take up too much space.

    /Argyle
  9. gaurav_bindlish New Member

    Use sp_spaceused with update usage to see what is the actual space used. If u see the same figures, data can not be reduced. U'll have to archieve the database to move some data from the main database to the archived database.

    Gaurav
    Moderator
    Man thrives, oddly enough, only in the presence of a challenging environment- L. Ron Hubbard
  10. essam636 New Member

    I used sp_spaceused and I found the same figures..(data size ~8.3 GB)..
    today, I did a small experiment: i transfered All tables, views, proc., etc to new database in different server, and I after transfere I ran sp_spaceused , and I found the data size went down to 900 MB only..!

    any comments..

    Regards..
    Essam Andrew


  11. itbhushan New Member

    Did u drop the transfered tables, views, proc, etc from the old database where your size decreased to 900 mb, if yes then what is left in the database ? Or is it that after transferring the above mentioned objects, have u truncated the tables ?

    Bhushan
  12. satya Moderator

    You need to make periodic log backup to keep the log file within limits. Also,backup/restore
    does not allow/deal you to shrink file size.


    Before proceeding for shrink method, take full backup and truncate the log and then proceed for DBCC SHRINKFILE or DBCC SHRINKDATABASE.


    _________
    Satya SKJ
    Moderator
    SQL-Server-Performance.Com
  13. gaurav_bindlish New Member

    Wait a minute, I thought we are talking about data file and not log file.

    As far as reducing the data file size is conccerned, as I said before, if the data size in the database is 8.3 GB then there is no way you can decrease the data file beyond 8.3 GB. If you want to do that, consider partitioning the data between two databases keeping the active data in one database and old data in archieved database.

    Gaurav
    Moderator
    Man thrives, oddly enough, only in the presence of a challenging environment- L. Ron Hubbard
  14. bambola New Member

    Back to the DBCC SRHINKDATABASE. If you ran it from QA, with what parameters did you ran it?
    If you did from EM, did you check the option that says "move pages to beginning of file before shrinking"?
    If you didn't, try again to run this command from QA

    DBCC SRHINKDATABASE('database_name', 10)

    If it doesn't shrink it and the data (data file not log!) is really only 900 MB, than I really don't understand this...

    Bambola.
  15. Twan New Member

    another possibility is if varchar columns have been removed from tables, but dbcc cleantable has not been run. SQL won't reclaim space of dropped varchar column automatically 7GB seems excessive though, are you counting index space in that 900MB too? the mdf file includes indexes as well as data...
  16. vbkenya New Member

    Essam, please address these issues:

    1. What is contained in the 900 MB?
    2. What size reduction are you expecting?

    And

    1. What was the size of the new container database after moving ALL the objects?
    2. Did you try shrinking this new container database?

    Nathan H.O.
    Moderator
    SQL-Server-Performance.com
  17. satya Moderator

    Tlog is also a part when you consider database size.
    And truncating the transactions from Tlog will definetly help to reduce the database as a whole.

    _________
    Satya SKJ
    Moderator
    SQL-Server-Performance.Com
  18. Chappy New Member

    true, but Tlog isnt an MDF file <img src='/community/emoticons/emotion-1.gif' alt=':)' />
  19. Luis Martin Moderator

    Is critical to know how many is data in 8.3Gb and how many is space to grow in 8.3Gb.

    I can't find that information in previus post.

    Luis Martin
  20. essam636 New Member

    Dear Gaurav,

    OK if there is no way I can decrease the MDF data file beyond 8.3 GB, i want to know why after transfering the same dta( all tables, views, procedure..etc..) to new dtabase in different server, I check the size of the MDF file ( for this new database) and I found it ( 900 MB) only..althogh it contains same data from original DB?

    Regards..
    Essam Andrew


  21. satya Moderator

    Essam

    Confirm the following options :

    What is the size of full database backup?
    What is the size of Tlog?
    What is the result of SP_SPACEUSED for this database?

    _________
    Satya SKJ
    Moderator
    SQL-Server-Performance.Com
  22. essam636 New Member

    DB Backup size = 3.9 GB
    size of Tlog = 10 MB ( I truncate Tlog on daily basis )

    the result of SP_SPACEUSED :

    reserved : 8.3 GB
    data : 1.5 GB
    index_size : 90 MB
    unused : 6.8 GB

    Regards...
    Essam Andrew
  23. bambola New Member

    Now could you please run DBCC SRHINKFILE(data_file, 10) and then repeat the numbers?
    You can get the file name from sp_helpdb 'database_name'.

    Bambola.
  24. satya Moderator

    BTW, what is the size of MODEL database?

    _________
    Satya SKJ
    Moderator
    SQL-Server-Performance.Com
  25. vbkenya New Member

    Satya may have a point about the T-Log contributing to the perceived 8.3GB size of the database despite the fact that Essam is referring only to the MDF file. An irreducible 8.3GB could have resulted from a T-Log that cannot be truncated (and shrunk) any further.The movement of ALL database objects that gave Essam only 900 MB maybe be testomony of this although there is a possibility that not 'ALL' objects were moved e.g. indexes. Essam might also benefit from checking how much data the system tables of the old database contain - I have seen blotted system tables before.

    7.4 GB of data (or space for that matter) is hard to miss and only a rare bug would attempt to explain it.



    Nathan H.O.
    Moderator
    SQL-Server-Performance.com
  26. gaurav_bindlish New Member

    Nathan, as mentioned in one of the previous posts, the transaction log size is 10 MB.

    What was the size of the database when it was created?

    Gaurav
    Moderator
    Man thrives, oddly enough, only in the presence of a challenging environment- L. Ron Hubbard
  27. essam636 New Member

    Bambola: the figures after I ran DBCC SHRINKFILE(data_file, 10) are:

    CurrentSize: 831080
    MinimumSize: 128
    UsedPages: 831072
    EstimatedPages: 831072

    regards..
    Essam Andrew
  28. satya Moderator

    As a test run try to export database to another server, increase the size and try running DBCC SHRINKDB or SHRINKFILE to see the activity.

    _________
    Satya SKJ
    Moderator
    SQL-Server-Performance.Com

Share This Page