DBCC SHRINK FILE

Question

DBCC SHRINK FILE

sqlquestions15

SSC Journeyman

Points: 84
More actions
June 14, 2009 at 11:05 am

#138604

What does DBCC SHRINK FILE does Actually????

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

ps. One Orange Chip Points: 29422 More actions · Answer 1

DBCC SHRHINKFILE shrinks a data or a log file.

Shrinking here pertains to reclaiming unused spaces within a file. There are operations that happen in the database that lead to growth of files, SQL Server doesnt release the space back to the OS.

For example, if your transaction log has grown heavily, backup log will mark all spaces occupied by committed transactions as free(which can be reused) but doesnt give free space back to the underlying operating system.

This link may provide more help.

http://msdn.microsoft.com/en-us/library/aa258824(SQL.80).aspx"> http://msdn.microsoft.com/en-us/library/aa258824(SQL.80).aspx

Pradeep Singh

Johan Bijnens SSC Guru Points: 135376 More actions · Answer 2

Also check the Books Online ref provided in the previous post !

e.g. shrinking a log may not occur when you expect it to, .... BOL explains why...

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me

Tushar_IDFC SSC Veteran Points: 287 More actions · Answer 3

Check BOL it gives detailed usage and advantages of using DBCC Shrik Files.

Tushar

kishore_gdk 24670 Mr or Mrs. 500 Points: 565 More actions · Answer 4

Hi ,

dont know , may be this is small question..

1.i have senorio like DBallocated size 10 GB , free available size :7GB , used space is :3GB.. for datafile file id 1

i start shrink operation on Datafile ..

dbcc shrinkfile(1,9950)

dbcc shrinkfiel(1,9900)

-----

-------

dbcc shrinkfile(1,3400)

like this i have created job , but after some time ...the used space is 4 GB .., freespace 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 .

george sibbald SSC Guru Points: 104210 More actions · Answer 5

sqlquestions15 (6/14/2009)
What does DBCC SHRINK FILE does Actually????

in a nutshell for a datafile it shuffles all the extents to the front of the file. It does not care how it does it or about maintaining the order of the data, this is why it introduces fragmentation and is generally a bad idea and only to be performed if strictly necessary. Depending on exact command you have issued will then return free space to the OS

For log files it truncates the log down to the last active vlf (virtual log file) it comes to if this is at a point greater than target size specified. This is why log files sometimes will not shrink if the vlf is at the end of the file.

---------------------------------------------------------------------

Steve Jones - SSC Editor SSC Guru Points: 736478 More actions · Answer 6

You don't want to do this often, for reasons George mentioned. However if you do shrink data flies, leave enough space to run a reindex on the database.

If you have trouble with the log files, you can use a script like this one to help: http://www.sqlservercentral.com/scripts/Maintenance+and+Management/30026/

Johan Bijnens SSC Guru Points: 135376 More actions · Answer 7

file 1 is a data file.

Shrinking it 500MB at a time will be to much overhead for your operation, especially if you know it only contains 20% worth of data.

I would shrink it directly to the size you target for.

Keep in mind to also reserve space for the reindex operation that MUST follow directly after your shrink operation.

Must because a shrink only moves your pages to fit into the space you aim for. It will take more space if that target size doesn't fit.

So you should always perform full database maintenance after a shrink operation of a data file.

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 8

Steve Jones - Editor (5/7/2010)
You don't want to do this often, for reasons George mentioned. However if you do shrink data flies, leave enough space to run a reindex on the database.
If you have trouble with the log files, you can use a script like this one to help: http://www.sqlservercentral.com/scripts/Maintenance+and+Management/30026/

Heh... which will also cause the log files to grow again. No "Sort In Temp DB" option in 2k either IIRC.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)