July 21, 2010 at 3:41 am
Hi,
I have a database (SQL 2005). The initial size of the data files is set to 50Gb. When looking at the data files (.ndf) they are all obviously 50Gb. I now need additional space on the server. My questions are:
1/. How can I tell how full the data files are. They are using 50Gb of space each; however, I dont think that they actually contain this much data. This was the initial setting, so I'm assuming that they are created at 50Gb and then they fill up to this value, although the file size is always 50Gb.
2/. If the data files are not full, then what would be the best option to reduce their size down from 50Gb. I've heard that shrinking data files is not a good option.
3/. Does rebuilding indexes require more space during the rebuilding phase?
Thanks for any info.
July 21, 2010 at 5:26 am
Hi,
I will give my try.
1.) The easiest way to find the full details of data file usage is:
Right click database> reports> Standard reports> Disk Usage
OR use the below:
Current Database
Select name as FileName, size/128 as CurrentSize, (fileproperty(Name,'SpaceUsed'))/128 SpaceUsed from sysfiles
All databases:
sp_msforeachdb'use ?; Select ''?'' DBName, name as LogicalName, size/128 as PhysicalSize,(fileproperty(name,''spaceused''))/128 as UsedSpace,filename from sysfiles'
2.) If the data file tends to grow in future, then its not advisable to shrink the data file because it can cause fragmentation while growing afterwards.
John
July 21, 2010 at 6:09 pm
3) Yes, rebuilding indexes requires more space, but it depends on index size. I have database that takes more than 3 times space than actual data because of index rebuilds.
You can check Paul Randal's article Why you should not shrink your data files.
July 22, 2010 at 2:18 am
OK. Thanks for your help.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply