Home Forums SQL Server 2005 Administering Database Size is shwoing Much larger then Actual Data RE: Database Size is shwoing Much larger then Actual Data

  • This Database used to be in simple recovery model and I did change it to Full now. I am taking T-log backup and full backup .

    No, no... My apologies if I was not clear. You want to set it back to simple unless you need to do point in time backups/restores. If you are NOT doing that then there is absolutely no reason to set your DB to full recovery mode. Take a look at this article for more details about recovery models.

    The reason I asked is because: If your DB was in simple mode then the log should not get as big as it did. In simple mode the transaction logs will grow but SQL Server maintains the log file size for you. If you are in FULL recovery mode then the log file is only shrunk after you do a transaction log backup. If you are in simple mode and the transaction log is getting that big it could be because of a few things (e.g. open transactions).

    For a good transaction log strategy I would suggest this free book (free pdf verison):

    SQL Server Transaction Log Management by Tony Davis and Gail Shaw

    [what] he is saying is that data space as well as index space grew by 500% when the actual record count grew by 15% .

    This goes back to my original question - where is this person getting these metrics? Data and index space on what grew by 500% on what? ... and when? Was the DB size growing at by, say, 10% a month and then blew up by 500% last month? Last week? If so, did anything change during those times... that is what you need to sort out.

    I noticed that those indexes are pretty large (several that are 500MB+) - are you storing images or blobs in your db?

    "I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

    -- Itzik Ben-Gan 2001