| Author |
Topic |
|
sagitariusmzi
Posting Yak Master
113 Posts |
 Posted - 2010-01-10 : 08:02:23
|
| Hi, I have inserted few records in DB, and then Deleted/Truncated those records, but the size of the data file is not reduced after removing of the recordsWhy the Data file's size is not reduced ?any help regarding this will be appreciated . |
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2010-01-10 : 08:33:11
|
The SQL Server will reserve the unused space in the data file dbname.mdf for later use.
No, you're never too old to Yak'n'Roll if you're too young to die. |
 |
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2010-01-10 : 10:06:22
|
| It will take into effect after you shrink it. |
|
|
|
sagitariusmzi
Posting Yak Master
113 Posts |
Posted - 2010-01-11 : 07:21:24
|
| Why we need to shrink it manually ?why it doesn't do it automatically ? |
|
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2010-01-11 : 07:56:15
|
You don't need to shrink it. Not manually and not automatically.There was a reason for the database to grow and it will/can use the unused space the next time there is a reason.Shrinking goes hand in hand with fragmentation so don't do it.Please see also this thread:http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=107085
No, you're never too old to Yak'n'Roll if you're too young to die. |
|
|
|
jackv
Master Smack Fu Yak Hacker
2179 Posts |
Posted - 2010-01-11 : 08:29:38
|
| webfred , based on what you've outlined - what would be your recommended way of reclaiming unused space - for example ,after records have been deleted , and you know that no more records will be added?Jack Vamvas--------------------http://www.ITjobfeed.com |
|
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2010-01-11 : 11:15:55
|
If you really know that no more additional data will be stored in your database and you have real big unused space in your file then IMHO you can shrink the file manually.Maybe after that you will notice a slow down in performance because fragmentation has occured then you can defrag indexes on that database.I have seen a stored procedure written by Tara and maybe this stored procedure is usful then.Here is the link to Tara's stored procedure:http://weblogs.sqlteam.com/tarad/archive/2007/04/17/60176.aspx
No, you're never too old to Yak'n'Roll if you're too young to die. |
|
|
|
jackv
Master Smack Fu Yak Hacker
2179 Posts |
Posted - 2010-01-12 : 01:36:20
|
| Thanks webfred , I just needed some clarification , as there was a suggestion earlier that manual shrinking should never occur , but it is something I do , particuarly if I've dropped summary tables from datawarehouses , and large amount of space is left unusedJack Vamvas--------------------http://www.ITjobfeed.com |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
|
Size of Data File is not Reduced