Forum Discussion

tarmizi's avatar
tarmizi
Level 5
12 years ago
Solved

How to backup SQL transaction logs?

Hi, 

I've got some question where after backing up the transaction log, it doesn't clear/truncate the transaction log. Now my customer disk space is low and need a way to prevent it form hanging the SQL. Have any of you guys know how to deal with it?

Thank you

7.1.x and Earlier
Backing Up
Backup and Recovery
Configuring
NetBackup
Windows Server (2003-2008)
  • RLeon's avatar
    RLeon
    12 years ago

    Log truncation happens within the log file, with the file size staying the same. To actually give the file system back this "empty space" within the log file, the DBA can manually or automatically perform a shrink operation.

    Please refer to the following links for further details.
    The first two were written for Backup Exec, but the topic is just as applicable to NetBackup, as well as any other backup software.

    http://www.symantec.com/docs/TECH33654
    http://www.symantec.com/docs/TECH82950
    http://msdn.microsoft.com/en-us/library/ms178037%28v=sql.105%29.aspx
     

6 Replies

Sort By
  • huanglao2002's avatar
    huanglao2002
    Level 6
    12 years ago

    you can use netbackup for sql agent to do this.

    Create backup scripts

    1 open sql gui tools

    2 set connection property

    3 Type of Backup list, select "transaction log" and check "Back up and truncate
    transaction log" options.

     

    after this step

    you can create a sql type backup policy,and use this script to backup sql log.

  • Marianne's avatar
    Marianne
    Level 6
    12 years ago
    Please show us the script used for backing up transaction logs? NBU will notify SQL that the backup was successful, and then SQL truncates the logs. Evidence can be seen in the SQL ERRORLOG: Flushcache: cleaned up ##### buffs .......... There maybe something like this as well: avoided #### new dirty buffs..... Meaning that SQL itself was unable to cleanup everything.
  • tarmizi's avatar
    tarmizi
    Level 5
    12 years ago

    Hi huanglao2002,

    I have done the step given in the guide also the SQL database backup policy also been created. The transaction log backup ran first and the SQL database backup policy later. But somehow the transaction log still did not truncated. The space of the log still doesn't cleared thus it eaten up the disk space. Is there need to be done by DBA on the MSSQL server? maybe the size restiction of transaction log need to be altered?

    Thank you

  • huanglao2002's avatar
    huanglao2002
    Level 6
    12 years ago

    The space of the log still doesn't cleared thus it eaten up the disk space. Is there need to be done by DBA on the MSSQL server?

     

    yes,if the log datafile have imcreace very big.you can ask the MS SQL admin to reduce the log data file size. because of the actual log occpy size is very small. it's mean big datafile contain little data.

    As my experence this operate is safe and simple.

     

  • Marianne's avatar
    Marianne
    Level 6
    12 years ago
    It is normal to run database backup first, then log backup. Please check SQL ERRORLOG to see what happened at the end of the log backup.
  • RLeon's avatar
    RLeon
    Level 6
    12 years ago

    Log truncation happens within the log file, with the file size staying the same. To actually give the file system back this "empty space" within the log file, the DBA can manually or automatically perform a shrink operation.

    Please refer to the following links for further details.
    The first two were written for Backup Exec, but the topic is just as applicable to NetBackup, as well as any other backup software.

    http://www.symantec.com/docs/TECH33654
    http://www.symantec.com/docs/TECH82950
    http://msdn.microsoft.com/en-us/library/ms178037%28v=sql.105%29.aspx