question

kiiiiiii avatar image
kiiiiiii asked

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

datafiles
1 comment
10 |1200 characters needed characters left characters exceeded
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Blackhawk-17 avatar image Blackhawk-17 ·
Why are you doing 50 MB changes? Why not just the one large shrink?
2 Likes 2 ·

2 Answers

· Write an Answer
Kev Riley avatar image
Kev Riley answered

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?

10 |1200 characters needed characters left characters exceeded
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Fatherjack avatar image
Fatherjack answered

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.

2 comments
10 |1200 characters needed characters left characters exceeded
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Blackhawk-17 avatar image Blackhawk-17 ·
Gee - determining root cause. What a concept!
0 Likes 0 ·
Fatherjack avatar image Fatherjack ♦♦ ·
Well, I'm trying to start a revolution ;)
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.