Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
dbaadm
Starting Member
10 Posts |
 Posted - 2009-08-21 : 07:52:37
|
I have a database hosted on an external SQL Server underlying storage is SAN. The HW is very powerful 4x (Xeon MP E7450 2.40GHz 12MB 1066MHz) with 64GB memory setup in a cluster.The database consists of n numbers of physical files in the same filegroup.Presently each file has allocated physical around 120GB.However using "Disk usage" report in SSMS I have found out that we only use around 25% of each file.As we don't own the SAN that we use but rent it in chunk of 100GB I would like to shrink the files.As we are running a 24x7 hour service I would like to limit the "downtime" for the cleanup.As I see it I have one of these options - Shrink each file in the file group to a size that would acceptable
- Use the SHRINKFILE with the option EMPTYFILE, moving the data to the other files in the filegroup following by a removal of the "obsolete" datafile.
As both operation are resource demanding my question are - which of the option are the fastest and should have the least impact on the overall operation of the system as we are running 24x7?Does someone have another idea to reduce the allocated storage usage without deleting data? |
|
|
ahmad.osama
Posting Yak Master
183 Posts |
Posted - 2009-08-21 : 08:10:13
|
quote:
Originally posted by dbaadm
I have a database hosted on an external SQL Server underlying storage is SAN. The HW is very powerful 4x (Xeon MP E7450 2.40GHz 12MB 1066MHz) with 64GB memory setup in a cluster.The database consists of n numbers of physical files in the same filegroup.Presently each file has allocated physical around 120GB.However using "Disk usage" report in SSMS I have found out that we only use around 25% of each file.As we don't own the SAN that we use but rent it in chunk of 100GB I would like to shrink the files.As we are running a 24x7 hour service I would like to limit the "downtime" for the cleanup.As I see it I have one of these options - Shrink each file in the file group to a size that would acceptable
- Use the SHRINKFILE with the option EMPTYFILE, moving the data to the other files in the filegroup following by a removal of the "obsolete" datafile.
As both operation are resource demanding my question are - which of the option are the fastest and should have the least impact on the overall operation of the system as we are running 24x7?Does someone have another idea to reduce the allocated storage usage without deleting data?
I think first one is better as it will only shrink the specified file where as the 2nd one will delete data from the specified file and will then write to another.... |
 |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2009-08-21 : 09:59:09
|
| You don't have to take the database offline to shrink files.What was the reason for using multiple files? Balancing I/O threads to match CPUs? If so, you wouldn't want to use EMPTYFILE unless you don't need that feature anymore. Better to just shrink each file. |
|
|
|
|
|
|
SHRINKFILE