Post reply

DATABASE

  • June 11, 2009 at 2:42 am

    #135091

    Hi,

    I have a database whose datafile size is 6751 and used space is 6129.I am shrinking it and again after some time the size become the same(6751 and used space is 6129).I just want to know why the gap is always like this?????????

    Thanks in advance.

    Sunanda

  • June 11, 2009 at 2:49 am

    #1007841

    Hi,

    Is your databse recovery mode is Full, Pls chek it .

  • June 11, 2009 at 2:55 am

    #1007843

    Why do you want to shrink? Databases tend to grow as more data gets put in them. It's in their nature.

    Shrinking causes massive fragmentation and will just result in the data file growing again next time data gets added. When that happens, the entire system will slow down as the file is expanded. Also repeated shrinks and grows will cause fragmentation at the file-system level, which is hard to fix.

    See - http://sqlinthewild.co.za/index.php/2007/09/08/shrinking-databases/

    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

  • June 11, 2009 at 2:57 am

    #1007844

    What is the space available.

    Tanx 😀

  • June 11, 2009 at 3:03 am

    #1007846

    GilaMonster (6/11/2009)

    Why do you want to shrink? Databases tend to grow as more data gets put in them. It's in their nature.

    Gila so what should be done when your log file or data file grows and reaches the limit of the space available in server.

    We wont be able to access the server or database right.

    Tanx 😀

  • June 11, 2009 at 3:15 am

    #1007849

    HI.

    Looking at the numbers -> 6129 + 10% = 6742. This is close to your growth number. Have you set growth to 10% plus in your database Properties? Choose Files & check Autogrowth?

    Neal

  • June 11, 2009 at 3:48 am

    #1007862

    GilaMonster said very correct that data file size grow is it's nature..Even if u try to shrink the file, it'll automatically grow..It can be useful in case of log file.....But even that is recommendation from microsoft that always avoid truncating log file of database.....

  • June 11, 2009 at 3:52 am

    #1007865

    Eswin (6/11/2009)

    Gila so what should be done when your log file or data file grows and reaches the limit of the space available in server.

    Get more disks or delete some data.

    If you're properly managing the log it should not grow continuously. There'll be a point where the size of the log is mostly stable. If the log is growing continuously then it's time to review the log maintenance that's done.

    If the database is growing it means there's more data in it. Options there are archive (with maybe a once-off shrink and index rebuild afterwards), compression (SQL 2008 only) or buy more disks.

    If the data file fills the disk and SQL gives errors saying there's no space available then shrinking won't help. Shrinking releases unused space back to the OS. If the data file's full (which is the only case where SQL would be giving errors) then there's no space to give back to the OS anyway.

    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

  • June 11, 2009 at 4:17 am

    #1007875

    If your database is in Full recovery mode and you have some index rebuild job running, that can explain the database grow. If you are rebuilding the indexes it will cause the transaction log to grow and after you backup the transaction log the size will stay the same while the used space will be less. I think you should follow Gail's advice. Good luck!



    My blog

  • June 11, 2009 at 4:18 am

    #1007876

    agreed with Neal ...also check the table size of all the tables for some more idea :

    sp_msforeachtable "sp_spaceused '?'"

    Note : this will not show the size of system tables

    Abhay Chaudhary
    Sr.DBA (MCITP/MCTS :SQL Server 2005/2008 ,OCP 9i)

  • June 12, 2009 at 12:50 am

    #1008413

    OK.Everything is true and also the datafiles will increase as per the insertion of the data.But why so much free space remain where as it is not the same for other databases.Actually it was eye catching as I found this much of gape always remain.

  • June 12, 2009 at 6:56 am

    #1008576

    600 MB of a 6 GB database isn't much. It's 10% of the data file size. Which, incidentally, is the default autogrow %

    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

Viewing 12 posts - 1 through 11 (of 11 total)

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