SQL Server Performance

Manually Add space to data file

Discussion in 'General DBA Questions' started by alimmia, Dec 17, 2005.

  1. alimmia New Member

    In my data file, there is no free space, I shrink the data file. My "select * into" is gettting hung. How can I add free space to the data file manually. My database is 230 GB. I do not like to wait for auto growth.

    Thanks,

    Alim
  2. Luis Martin Moderator

    Easy way: Open EM, yourdatabase, options, Data Files, and in Space Allocated fill the number you want.

    But, I don't think you select will improve performance with that.

    You need to see execution plan and find out if any indexes are neccesary.


    Luis Martin
    Moderator
    SQL-Server-Performance.com

    One of the symptoms of an approaching nervous breakdown is the belief that one's work is terribly important
    Bertrand Russell


    All postings are provided “AS IS” with no warranties for accuracy.



  3. alimmia New Member

    Thank you. I got it.

    Another problem! My DB is 228 GB. I shrink the database with DBCC SHRINKFILE (db_file,10000), expecting it will keep 10 GB free space. However, I saw the data file has zero space. The auto file growth is set to 10%. Could you tell me why the file did not get 10 GB free space?

    Thanks,

    Alim
  4. Luis Martin Moderator

    May be after the shrink database need to grow.

    Keep in mind this: If you have plenty space, live database to grow dinamically. If you have no plenty space, then change automatic grow 10% to a fix value, said 15GB (an example).
    In any case, any shrink after maitenance plan like reindex or update statistics will loose all maintenance plan.



    Luis Martin
    Moderator
    SQL-Server-Performance.com

    One of the symptoms of an approaching nervous breakdown is the belief that one's work is terribly important
    Bertrand Russell


    All postings are provided “AS IS” with no warranties for accuracy.



  5. Luis Martin Moderator

    Now is sunday 12:05am my time, I'll back tomorrow to see if you need more help.

    Regards,


    Luis Martin
    Moderator
    SQL-Server-Performance.com

    One of the symptoms of an approaching nervous breakdown is the belief that one's work is terribly important
    Bertrand Russell


    All postings are provided “AS IS” with no warranties for accuracy.



  6. alimmia New Member

    Thank you, Luis.

    Alim
  7. satya Moderator

    If the database size is 220gb then default value to auto-grow the size is 10%,so you need atleast 22gb free space on drive to accomodate the database size.

    quote:Originally posted by alimmia

    Thank you. I got it.

    Another problem! My DB is 228 GB. I shrink the database with DBCC SHRINKFILE (db_file,10000), expecting it will keep 10 GB free space. However, I saw the data file has zero space. The auto file growth is set to 10%. Could you tell me why the file did not get 10 GB free space?

    Thanks,

    Alim

    Satya SKJ
    Contributing Editor & Forums Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.

Share This Page