Coffeehouse Thread

8 posts

Forum Read Only

This forum has been made read only by the site admins. No new threads or comments can be added.

If you never do a Full backup on SQL server. Can that give performance issues?

Back to Forum: Coffeehouse
  • User profile image
    fabian

    We have been troubleshooting severe performance problems all day, on a busy web application that uses SQL server 2008 R2. The database is 120 GB. In order to try to reproduce the problem in the test / dev environment we took a full backup of the database using Management studio.

    The strange thing is ... after we did the backup ... the performance issues are gone!

    We have since figured out that no full backup has been run for over 1 month and no maintenance jobs are scheduled on the SQL server The recovery mode is set to Simple

    My question is: I you don't ever perform a full backup. Can that give performance issues? I am thinking about that the transaction log is never commited.

    or is this just pure coincidence?

     

  • User profile image
    blowdart

    , fabian wrote

    My question is: I you don't ever perform a full backup. Can that give performance issues? I am thinking about that the transaction log is never commited.

    or is this just pure coincidence?

     

    What did you run to do the backup? It's possible that the transaction logs were truncated at that point, as you no longer needed them to roll back - you have the backup for that.

     

  • User profile image
    fabian

    We just fired up management studio and right clicked the database and created a full backup.

     

     

     

  • User profile image
    figuerres

    , blowdart wrote

    *snip*

    What did you run to do the backup? It's possible that the transaction logs were truncated at that point, as you no longer needed them to roll back - you have the backup for that.

     

     

    that may well be what happened.

    you may also want to setup some maint. plans to re-build indexes and some other stuff...

    like a nightly backup ....

  • User profile image
    fabian

    ohh yes. We are going the hold a DBA tomorrow and have him to go through the server. The client thought it was good enough just to snapshot the virtual server once a day.

  • User profile image
    elmer

    Might depend on the config of your backup. A typical "backup" can consist of a number of processes, including compressing, truncating, re-indexing, etc.

  • User profile image
    Maddus Mattus

    @fabian: A backup forces a write to committed data and also does a truncate of unused space, so maybe your disks are full?

    Maybe it's the OS which is having a hard time due to low disk space.

  • User profile image
    ZippyV

    SSMS -> Right click database -> Properties -> Options -> Recovery Model. Yours is probably set to Full.

    http://msdn.microsoft.com/en-us/library/ms189275(SQL.105).aspx">http://msdn.microsoft.com/en-us/library/ms189275(SQL.105).aspx

    http://msdn.microsoft.com/en-us/library/ms189275(SQL.105).aspx

Conversation locked

This conversation has been locked by the site admins. No new comments can be made.