SQL Server Performance

DATABASE DATA FILE SIZE increases very fast and shrinking not help

Discussion in 'SQL Server 2008 Questions [Archive Only - Closed]' started by wayar2007, Jan 17, 2011.

  1. wayar2007 New Member

    HiiiI have problem in my DB, its size = 2GB, and it increased every weak to 100GB, i tried many times to know reasons, but no way.Also i need to know is there any data lost in changing DB recovery model, for example from Full to simple?.
  2. ghemant Moderator

    Hi, Is this your database file i.e. mdf or ndf file growth you are talking about? or you are talking about Transaction Log file growth!! Here is a reference of an article I've wrote sometime back about few reasons of T-Log growth http://www.sql-server-citation.com/2009/08/how-to-get-rid-off-from-transaction-log.html.

    You might also consider the auto growth point http://www.sql-server-citation.com/2010/01/common-mistakes-in-sql-server-part-5.html

    When you change the recovery model from full to simple it is always recommended to take the full backup before and after changing the recovery model it is because switching from full to simple will break the log chain, after you have your database in simple recovery model you can not have advantage of point-in-time recovery.

    And that is why it is recommended to have production databases in the full recovery mode and to take periodic t-log backup at regular interval.

    Regards
  3. RamJaddu Member

  4. Luis Martin Moderator

    [quote user="wayar2007"]HiiiI have problem in my DB, its size = 2GB, and it increased every weak to 100GB, i tried many times to know reasons, but no way.Also i need to know is there any data lost in changing DB recovery model, for example from Full to simple?.[/quote]
    About why database is growing could be just for users using DB.
    There is no reason to lost data changing from full to simple. If you do that, you have to restore the last full backup if you have some problem.
  5. RamJaddu Member

    Chaging from full to simple recovery model does't effect you data file size, as Luis suggest restore full backup incase any issues.
    Please carefull doing any db file shrinking that might create fragmentations will effect performance.
  6. satya Moderator

    Welcome to the forums.
    As referred above you should not attempt to SHRINk when other processes are contributing the size of transaction log. In this case I recommend to monitor the database usage including the queries, adhoc queries, scheduled jobs and any sort of connectivity that is happening on SQL Server.
    Also it is not advisable to SHRINk on daily basis without knowing the knock on affect of performance loss due to the optimization jobs that were helping the performance.

Share This Page