Post reply

Shrinking database

  • September 3, 2008 at 6:47 am

    #72727

    Hi,

    I used this script for shrinking database ,but i read somewhere that dont use shrink command if its bigger than 5gb and i dont know what the use of 1 and 10 in this script .

    dump tran HFM with no_log;

    DBCC SHRINKdatabase(HFM,1)

    DBCC SHRINKFILE(HFM_Log, 10)

    so plz help and give me ur suggestion

  • September 3, 2008 at 7:13 am

    #866463

    Don't shrink your database at all. It causes very bad index fragmentation and the DB will just grow again next time some data is added or those fragmented indexes are rebuilt. Repeated grows and shrinks cause file-level fragmentation.

    In addition, that Dump.. with no log is breaking your recovery chain and could lead to loss of data in the case that you have to restore your database. It's an old (deprecated) version of Backup log .. with truncate only. See this post for details on that - Recovery model and transaction logs[/url]

    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

  • September 3, 2008 at 7:20 am

    #866466

    so plz tell me what can i do in this situation .Because there is no more disk space on drive .Please give me any tsql or any suggestion to solve the problem .

    Really thanxx for ur reply .

    Regards

    Jagpalsingh

  • September 3, 2008 at 7:26 am

    #866470

    Get a bigger drive.

    If you're putting data into the database it is going to grow. You can look at archiving or deleting data that's no longer necessary, but that's something that you will have to decide on based on the DB, the app and any data-retention laws that may apply

    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

  • September 3, 2008 at 7:31 am

    #866472

    You can go ahead and use the DBCC commands to shrink the database files. I never heard of any issue regarding the 5 GB.

    Keep in mind that shrinking database files is often a bad idea because it can lead to fragmented files, especially if there are other files on the same drive. I'm not going to get into the issues here. Search this site and you'll find information on the topic.


    Alvin Ramard
    Memphis PASS Chapter[/url]

    All my SSC forum answers come with a money back guarantee. If you didn't like the answer then I'll gladly refund what you paid for it.

    For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]

  • September 3, 2008 at 7:31 am

    #866473

    or is there any way to shrink .

Viewing 6 posts - 1 through 5 (of 5 total)

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