SQL Server Performance Forum – Threads Archive

Manually Add space to data file

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

---

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.

---

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

---

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.

---

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.

---

Thank you, Luis. Alim

---

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

---

]]>