SQL Server 2005 Logs Not Truncating

[KDOG1122]

I'm running windows 2003 R2 on my master and two media servers with NetBackup 6.5.  I'm able to backup my SQL database but my logs won't truncate. 

I went to the "NetBackup MS SQL Client" on the client server. 

Selected "File" and choose "BackuP SQL Server Objects" and on the left hand pane I selected the server name.  On the right hand side I selected the database. 

Under "Type of Backup" I selcted "Full" and under "Backup Script" I selected "Save" and created a batch file name and specified the location to save.  I left everything else default. 

I followed the same proceedure when for truncating the logs but instead of choosing "Full" under "Type of Backup" I selected "Transaction Log" and under "Transaction Log Backup Options" I selected "Backup and truncate transaction log".  Under "Backup Script" I selected "Save" and created a batch file name and specified the location to save.  I left everything else default. 

I sent back to the SQL policy I created and made sure I had an "Application Backup" and "Automatic Backup" type schedule created.  Under "backup selection tab" I added the two script path (which I indicated in the above paragraph). 

I run a manual full backup of the database and it backups the database but I get an status "1" code for partical success but have status "0" on the two databases it backups.  I went to the local server to see if it truncated the logs and the size still indicates 20GB.  What am I doing wrong?

Please help!

[marekkedzierski]

Create dbclient logs folder and run backup again, check application logs also. It will probably include errors from MSSQL.

[msoucy]

Hi, I am also seeing the transaction logs not being truncated. I would expect them to be smaller after each transaction log backup. I do a full, add to database, do diff then a trx log, mod data, do diff, tx, etc...(1 full, 6 diff/trx log backups per full iteration) via an automated script in perl. I do create the policy with the default-application and then add a full backup schedule. However I create the batch files on the fly for each of the backups adn execute the backups via dbbackex.exe which is the command line version for backing it up. Would not having the diff/trx log backups in the policy make *any* difference seeing I am calling the backups from command line vs using the GUI? If I were to add in the differential batch file to the policy will it work for that schedule? What about transaction log backups viat the GUI? Does it take into account there are multiple selections/batch files in it and use the correct one? I don't think it does. I think it ignores anything but the first one, or it will do all of them since they are in the same policy. If that's the case it would seem I'd need to have mulitple policies, one for full, one for diff and one for transaction logs as this is different from a regular file backup.

I took a look in the dbclient directory as someone had created it previously but I can't see anything amiss in there.

Any ideas as to why the trx logs aren't being trunc'd? I am running NBU 5.1 MP6 as well on 2003 servers.

[msoucy]

Disregard my last post. I was thinking of cold backup testing which does flat file backups hence the large size of the transaction log. On running my test at the end of the hot backups the trx log was <1Gb (5Gb original database size w/1Mb trx log and database up to 17Gb at end of test using ems data gen too). But I still don't see anything in the logs showing truncation happening. Is there something specific to look for to see this in the log file(s)?