DBCC shrinkfile
Hi ,
Dont know, maybe this is a small question..
I have scenario like DBallocated size 10GB, free available size 7GB, used space is 3GB, for datafile file id 1
I start shrink operation on Datafile ..
dbcc shrinkfile(1,9950)
dbcc shrinkfile(1,9900)
-----
-------
dbcc shrinkfile(1,3400)
like this I have created job, but after some time, the used space is 4GB, free space is 5.100GB
If it continues, at end of the shrink command, what will happen?
please any one can understand my problem give me correct input
Apart from not understanding why you are doing this in multiples (like Blackhawk has said), DBCC SHRINKFILE will not shrink a file smaller than what it needs.
From BOL:
DBCC SHRINKFILE does not shrink a file past the size needed to store the data in the file. For example, if 7 MB of a 10-MB data file is used, a DBCC SHRINKFILE statement with a target_size of 6 shrinks the file to only 7 MB, not 6 MB.
So in your case setting the target size to 3400MB, when the data is 4GB, will have no affect.
Also, you mention running this in a job, which implies you may be doing this regularly? Why are you shrinking the files on a regular basis?
Using DBCC SHRINKFILE or any other SHRINK command is not recommended practice, it is disk intensive work and in general gets undone as the database gets used in the normal run of your system. Care should ideally be taken to set the database size and leave it alone for as long as possible. If you shrink a database and then it grows again it is wholly possible that the data wont be in a contiguous area on the disk and performance will suffer.
You should investigate why a database has grown so big (may be you have deleted a lot of data recently?) as it is possible it will re-grow to the same size again.
