Shrink Database

  • Dear all,

    I need to know is this good to shrink the database after every backup.

    If not then what will be the effect if we continue shrinking the database after every Database Backup.

     

    Hope u undestand my question.

     

    From

    Killer

  • It's not considered good practice to shrink a production database.

    If the database has been shrunk and new data is added then it has to grow again, probably at an inconvenient time. Grows and shrinks are expensive IO operations

    Also shrinking a database reorders data and index pages to fit into a smaller volume, hence you are fragmenting your indexes every time you shrink.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • Dear ,

    Thanks for the reply.But still i need to know what will be the worst part if a DBA schedule a job to shrink database after every database backup in the production enviornment.

    from

    killer

  • As I said, it will force the database to grow again, when data is added. This grow may occur at a busy time, slowing the database down. It will fragment your indexes, possibly leading to degraded performance.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • Dear,

    I understand what u are trying to say.But i need some strong points a part from what u described in ur posts.

    So that i can explain to my directors and stop the process.

    I want to know what else can happen if i continue shrinking the database.

    The database is 24*7 mode (few user at night time approx:50 to 60).

    The process is as follows.

    Backup takes 3 hours to compete.After Backup complete shrink database job exceutes.

    Hope u undestand.

    from

    Killer

  • It's a performance hit. If your asking weather or not a shrink will corrupt your DB, the answer is no. You should go back and suggest running the shrink once a week, unless you HAVE to shrink the DB everyday due to size constriants. After you run a shrink, you should also update stat's and reindex if possible.

  • - I hope you're performing SQL backups using the T-sql backup statement or a maintenance job from sqlserver

    - If a db is "to big" that my have two causes :

       1) the is a whole bunch of insert/update/delete with rowrelocation or not resulting in empty pages.

       2) insert into tables with clustered indexes cause many pagesplits

      In both cases schedule DBCC DBReindex on your tables having clusered indexes. (Let's hope every table has one !)

    This will optimize your data and your dataaccess !!!

    We also schedule sp_updatestats after a reindex, because stats may get inaccurate due to frequent small operations that did not trigger the stats.

    like Christian Benvenuto mentioned you have to take at least 2 performance-hits !

    1) during the shrink operation

    2) during the new extend

    Your applications - and users - will be better served with a scheduled reindex than with a scheduled shrink, because a shrink does not optimize your data.

    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

  • Dear all,

    Thanks a lot. I am able to convince the director for stopping the shrink database process.

    This is dont becaue of  ur help guys.

    thanks once again.

     

    From

    Killer

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

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