Post reply

Shrinking Databse

  • 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.

  • May 6, 2004 at 12:38 pm

    #505535

    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.

  • May 6, 2004 at 9:37 pm

    #505584

    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

     

  • May 7, 2004 at 7:51 am

    #505662

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

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

  • May 7, 2004 at 10:28 am

    #505686

    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

  • May 7, 2004 at 10:52 pm

    #505723

    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.

     

  • May 11, 2004 at 6:05 am

    #505946

    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

  • May 17, 2004 at 2:07 pm

    #506701

    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!!!

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

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