Announcement

Collapse
No announcement yet.

Backing up the transaction logs

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • Backing up the transaction logs

    I have a SQL Server Standard edition 2008 R2 db with an external application that backs up the db hourly to a SAN. Recently, the db logs and the transaction logs grew so big that the db shut down and would not accept any more read/writes to the server.

    The Server is set to full recovery model.

    I have offloaded some of the older db and trans logs to another file server and I want to set a maintenance plan to back up the transaction logs every 30 mins. This is supposed to truncate log sizes when this happens. Since, we have hourly backups is it worth doing this? Also, it is supposed to truncate the transaction logs when a log back up is set but when I looked at the log files they seem to be the same size.

    I have scoured google but can't seem to get my head around it.

    Thanks,

    Philip

  • #2
    Re: Backing up the transaction logs

    IIRC a log backup does not actually reduce the physical file size, you need to run DBCC SHRINKFILE to reduce it.
    http://msdn.microsoft.com/en-gb/library/ms365418.aspx

    WHat it should do is mark areas of the log as overwritable, so the file size does not grow so rapidly

    What backup app are you using - is it SQL Aware?
    Tom Jones
    MCT, MCSE (2000:Security & 2003), MCSA:Security & Messaging, MCDBA, MCDST, MCITP(EA, EMA, SA, EDA, ES, CS), MCTS, MCP, Sec+
    PhD, MSc, FIAP, MIITT
    IT Trainer / Consultant
    Ossian Ltd
    Scotland

    ** Remember to give credit where credit is due and leave reputation points where appropriate **

    Comment


    • #3
      Re: Backing up the transaction logs

      Originally posted by Ossian View Post
      IIRC a log backup does not actually reduce the physical file size, you need to run DBCC SHRINKFILE to reduce it.
      http://msdn.microsoft.com/en-gb/library/ms365418.aspx

      WHat it should do is mark areas of the log as overwritable, so the file size does not grow so rapidly

      What backup app are you using - is it SQL Aware?
      I'm using AppAssure which is supposed to be SQL aware. how do I check to see if the areas of the log are overwritable? Can I run any sort of select statement or check on those logs?

      Thanks,

      Comment


      • #4
        Re: Backing up the transaction logs

        i think this is what your looking for:

        AppAssure SQL Log Truncation Support

        When the database recovery model is set to Simple, the SQL Database Engine performs periodic log truncation automatically. Therefore, you may chose not to enable log truncation through AppAssure at your discretion. Using AppAssure to truncate the logs supplements the log truncation function of the SQL Database Engine.
        at least i think that is what your looking for. so maybe if you can change the type of dB model, your app will take care of the t-logs for ya.

        hope that helps...
        its easier to beg forgiveness than ask permission.
        Give karma where karma is due...

        Comment


        • #5
          Re: Backing up the transaction logs

          Originally posted by James Haynes View Post
          i think this is what your looking for:

          AppAssure SQL Log Truncation Support



          at least i think that is what your looking for. so maybe if you can change the type of dB model, your app will take care of the t-logs for ya.

          hope that helps...
          We use SQL in tandem with application with a lot of read writes to the DB so the full recovery model might be best here. However, I do notice that we take hourly backups of the DB via AppAssure so it might be worth going to the Simple Recovery model since at the most, we would only be an hour behind in terms of data loss if the DB were to go down or become corrupt.

          Comment


          • #6
            Re: Backing up the transaction logs

            In our institute we are using this shareware utility for MS SQL and MySQL backing up and replication of all types.

            http://handybackup.net/download.shtml

            We are trying configuring internal tools for these activities; these tools are perfect but control of it is complicated and requires more skilled pros than just general system admins.

            Comment

            Working...
            X