Video Screencast Help
Symantec to Separate Into Two Focused, Industry-Leading Technology Companies. Learn more.

How to backup SQL transaction logs?

Created: 20 Jun 2013 • Updated: 24 Jul 2013 | 6 comments
tarmizi's picture
This issue has been solved. See solution.

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

Operating Systems:

Comments 6 CommentsJump to latest comment

huanglao2002's picture

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.

AttachmentSize
NetBackup_AdminGuide_MSSQL_Win.pdf 2 MB
Marianne's picture

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.

Supporting Storage Foundation and VCS on Unix and Windows as well as NetBackup on Unix and Windows
Handy NBU Links

tarmizi's picture

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

Marianne's picture

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.

Supporting Storage Foundation and VCS on Unix and Windows as well as NetBackup on Unix and Windows
Handy NBU Links

huanglao2002's picture

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.

 

RLeon's picture

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%2...
 

SOLUTION