Shrinking Databse

Question

Shrinking Databse

Juan Ricardo Tomasino

SSC-Addicted

Points: 444
More actions
May 6, 2004 at 10:13 am

#101997

Hello everyone.
I have a general knowledge question and I have been trying to read this but I can't seem to find enough relevant information. When you add and delete records in the database, the database does not go back to its original size. Is there a reason why? It seems that when you go in ENTERPRISE MANAGER, you can SHRINK the database, what exactly does this do?
From what I gather, shrinking the database will make the database only as big as it needs to be. So, if I wanted to shrink the database on command, what would the syntax be? I found the help on the DBCC SHRINKDATABASE command but I am not sure how to use it. Can anyone give me some assistance.
Thank you.

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

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

Robert Chin SSC Rookie Points: 27 More actions · Answer 1

Look at DBCC SHRINKFILE instead.

When you add records to the database, the database will grow according to how its growth pattern had been specified (for this option, right-click on your database, choose properties, and under Data Files and Transaction Log tabs see the File Properties options).

The size of the database file and the actual amount of data contained in that file are two different things. The size of the database file tells you how big the file has grown to, while data size tells you the amount of data the file holds. For example, you may have a 10 GB data file (.mdf file) with possibly 9 GB worth of data. At some time, you decide to do a massive delete and this brings the actual amount of data to, say 100 MB. Now, while the data size is reflected to how much data is stored in your database, the database file size will still be sized at 10 GB.

With DBCC SHRINKFILE command, you can then shrink the database file size to a smaller size.

Hope this helps.

MKC_Sipos SSC Enthusiast Points: 143 More actions · Answer 2

Consider allowing the database to grow and maintain a "sweet-spot" for the size. There is no real point in shrinking a db (or a log) that will only grow back out the the same size in days/weeks. Be predictive and proactive when sizing the files, making sure that you grow them to the size you think they will be for some time. This will reduce the fragrementation of the files on disk. If you expect a new DB to be100gb in 6 months, grow to that point today.

AUTO_SHRINK is an available option (Ref BOL: Setting Database Options) and can be set through ALTER DATABASE

csherwood SSC Eights! Points: 823 More actions · Answer 3

You might want to refer to the following article before setting AutoShrink options

http://www.sqlservercentral.com/columnists/mpearson/autocloseandautoshrinkjustdont.asp

hoo-t SSChampion Points: 12713 More actions · Answer 4

From BOL, the syntax for DBCC SHRINKFILE is as follows:

USE UserDB

GO

DBCC SHRINKFILE (DataFil1, 7)

GO

where

UserDB is the name of the database

DataFil1 is the logical name of the file you wish to shrink

7 is the final size of the file in megabytes.

Steve

MKC_Sipos SSC Enthusiast Points: 143 More actions · Answer 5

The question remains if shrinking of the database is necessary at all. Too often DBAs and SEs attempt to minimize the footprint of the database on disk, without considering the REAL operating size may exceed the running size. Fragmented incremental growth can also bring down performance.

If Juan is going through the trouble of shrinking the database this time, then it is reasonable that space is of a greater consideration than performance. From the words of Mike Pearson (the reference above):

The auto_shrink option has it’s uses in scenarios such as development servers and the like where disk space resources are usually limited and hotly contested, but (there’s always a ‘but’) there is a performance cost. Shrinking a database hogs the CPU and takes a long time. Plus, any indexes on the heaps (a table without a clustered index) affected by the shrink must be adjusted because the row locators will have changed. More work for the CPU. Like Auto_Close, this option is set to ON/TRUE for all databases when using SQL Server Desktop Edition, and OFF for all other editions, regardless of operating system.
When this option is set to ON/TRUE, all of a database's files are marked for shrinking, and will be automatically shrunk by SQL Server. This option causes files to be shrunk automatically when more than 25 percent of the file contains unused space. Not a wise option for your production systems which would suddenly suffer a performance hit when SQL decides it’s shrink-time. So, again – just don’t.

If it is necessary to shrink a database, also consider writing a routine to make incremental reductions in the size 2-5% at a time. This will perform better than a single reduction.

As with any recommendation provided by a board ... your miles may vary.

nermina28 Grasshopper Points: 24 More actions · Answer 6

it is recommended NOT to check "Auto shrink" on .. because a lot of probelms will appear

and so .. the best thing is to:

1- shrink it manually by using DBCC

2- make Full Backup daily and Incremental Backup every hour at maximum .. so you log and Data files will not graw in a big way

I hope this help u

Juan Ricardo Tomasino SSC-Addicted Points: 444 More actions · Answer 7

Thanks .... everyone's suggestions and comments have been noted. I will use the DBCC SHRINKFILE because I want to allow the operator of the software to determine when they want to "repair" the database.

This way, the system won't be operating when SQL decides to autoshrink and yes, it will be a serious CPU hog!!!

Thanks again!!!