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 |
|
fruitbat04
Starting Member
6 Posts |
 Posted - 2007-07-21 : 11:02:26
|
| I have a database that was 500GB (mostly binaries) that after archiving has been reduced to 200GB. I would like to reduce the size of the file to relect the new size.Firstly, please can someone confirm this is the correct commanduse databasenamegodbcc shrinkfile (filename_data, 200, truncateonly)goSecondly, is anyone able to comment on roughly how long this will take and how much it will slow the performance for users. I also assume this is safe to cancel from query analyser incase is overruns into production hours?thanks |
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2007-07-21 : 12:29:20
|
| Should be:use databasenamegodbcc shrinkfile (filename_data, 200000, truncateonly)goIt may take long while to shrink depending on disk speed. |
 |
|
|
fruitbat04
Starting Member
6 Posts |
Posted - 2007-07-21 : 14:23:13
|
| D'oh! I knew it was in MB not GB!Disks are Raid:10 array 10k SCSI but clustered so with no cache. |
|
|
|
thierryVD
Starting Member
11 Posts |
Posted - 2007-07-23 : 07:33:12
|
| We did a shrink once of a 160Gb data file to 130Gb and it took 15 hours (test system with no user-activity. No details on disk but I'm pretty sure it was RAID5)Anyway, I wouldn't recommend doing this during day-time |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2007-07-23 : 11:14:31
|
| It is often better to shrink database files in small increments so that it can make continuous, incremental progress, instead of trying to shrink by a large amount in one command. This makes it easier to shrink a database that must be shrunk by a large amount, and makes it easier to interrupt without losing all progress.This script can be used to shrink a database file in small increments until it reaches a target free space size. It will loop to execute the DBCC SHRINKFILE command to shrink the database file by the desired increment until it reaches the target free space.Shrink DB File by Increment to Target Free Spacehttp://www.sqlteam.com/forums/topic.asp?TOPIC_ID=80355CODO ERGO SUM |
|
|
|
|
|
|
DBCC SHRINKFILE