Logs Doesn't Truncated Wen TL Job Run

[BasigDegrasia3]

Hi Every Body.

When a Transaction Log Policy run for an SQL DB, the job is completed successfully with state 0. While the logs trauncated option is selected inthe backup patch file. But when the job started the logs doesnt truncated and the size still not changed. Please see the figures attached and give me your advises.

Regards,

[Mark_Solutions · Accepted solution]

Have you run a Successful full backup first?

After that please check the logs for the job to see if it has any notes in it .. on the client under \veritas\netbackup\logs\user_ops\MsSql\logs\ you should find the relevant logs - you can also create the dbclient folder under the logs directory to gather more information

See what you find

[Marianne]

Check SQL error log as well.
Log backup and truncate is recorded there, along with reason for failure to truncate.

[BasigDegrasia3]

Hi Guys,

Thanks for your fast respond.

Yes Mark, we already run successful Full backup. I browsed the location \veritas\netbackup\logs\user_ops\MsSql\logs\ and i found relevant logs created in the same day we had this problem, but to be honest , i don't know how to read and understand these logs.

I also created the folder dbclient under the log directory.

Marianne, from where i can check the SQL logs (From the client??).

[BasigDegrasia3]

By the way, I attached the output of dbclient after running a full backup.

[Marianne]

Do you have more than one script? 
The dbclient log shows output of successful database backup script:
C:\Program Files\Veritas\NetBackup\DbExt\MsSql\Full.bch

I cannot find any evidence of transaction log backups.
The 1st screenshot seems to be a file/script called TL?
When is this script running? Do you have a separate policy running the 'TL' script?

SQL ERRORLOG is usually located in <install_path>\mssql\log.

[BasigDegrasia3]

Hi Marianne,

Yes we have two policy (2 Scripts), one to run a full backup Weekly named Full.bch and the second one is to backup the transaction log named TL.bch

The previous log i attached was created after running the Full backup immediatly.

as I know. and correct my if am mistaken, Full backup is like transaction log backup it will truncate and delete logs after backup. And the space should be shrinked.

What happen in our case here, the backup is running several times and the space as it is and seems that logs does't truncated.

Regards,

[BasigDegrasia3]

the Transaction Log job completed just know and backup only 9 KB whic ic impossobile, our Transaction Log size is around 130 GB. Below is the detailed status of the job, please advise.

7/25/2013 10:02:40 AM - Info nbjm(pid=4412) starting backup job (jobid=392626) for client sipc-vcenter01, policy Test123321, schedule once 
7/25/2013 10:02:40 AM - Info nbjm(pid=4412) requesting STANDARD_RESOURCE resources from RB for backup job (jobid=392626, request id:{F9B4AFAD-2B84-4EA1-87A2-4F9E02F69C63}) 
7/25/2013 10:02:40 AM - requesting resource sipc-esp02-hcart-robot-tld-1
7/25/2013 10:02:40 AM - requesting resource sipc-netbackup.NBU_CLIENT.MAXJOBS.sipc-vcenter01
7/25/2013 10:02:40 AM - requesting resource sipc-netbackup.NBU_POLICY.MAXJOBS.Test123321
7/25/2013 10:02:41 AM - granted resource sipc-netbackup.NBU_CLIENT.MAXJOBS.sipc-vcenter01
7/25/2013 10:02:41 AM - granted resource sipc-netbackup.NBU_POLICY.MAXJOBS.Test123321
7/25/2013 10:02:41 AM - granted resource 0315L3
7/25/2013 10:02:41 AM - granted resource IBM.ULT3580-TD4.001
7/25/2013 10:02:41 AM - granted resource sipc-esp02-hcart-robot-tld-1
7/25/2013 10:02:41 AM - started
7/25/2013 10:02:42 AM - estimated 0 Kbytes needed
7/25/2013 10:02:42 AM - Info nbjm(pid=4412) started backup (backupid=sipc-vcenter01_1374735761) job for client sipc-vcenter01, policy Test123321, schedule once on storage unit sipc-esp02-hcart-robot-tld-1
7/25/2013 10:02:43 AM - started process bpbrm (2004)
7/25/2013 10:02:49 AM - Info bpbrm(pid=2004) sipc-vcenter01 is the host to backup data from    
7/25/2013 10:02:49 AM - Info bpbrm(pid=2004) reading file list from client       
7/25/2013 10:02:49 AM - connecting
7/25/2013 10:02:52 AM - Info bpbrm(pid=2004) starting bpbkar32 on client        
7/25/2013 10:02:52 AM - connected; connect time: 00:00:03
7/25/2013 10:02:55 AM - Info bpbkar32(pid=9004) Backup started          
7/25/2013 10:02:55 AM - Info bptm(pid=4328) start           
7/25/2013 10:02:55 AM - Info bptm(pid=4328) using 131072 data buffer size       
7/25/2013 10:02:55 AM - Info bptm(pid=4328) setting receive network buffer to 525312 bytes     
7/25/2013 10:02:55 AM - Info bptm(pid=4328) using 32 data buffers        
7/25/2013 10:02:55 AM - mounting 0315L3
7/25/2013 10:02:56 AM - Info bptm(pid=4328) start backup          
7/25/2013 10:02:56 AM - Info bptm(pid=4328) backup child process is pid 7676.6352      
7/25/2013 10:02:56 AM - Info bptm(pid=4328) Waiting for mount of media id 0315L3 (copy 1) on server sipc-esp02.
7/25/2013 10:02:56 AM - Info bptm(pid=7676) start           
7/25/2013 10:03:15 AM - Info bpbkar32(pid=9004) change journal NOT enabled for <C:\Program Files\Veritas\NetBackup\DbExt\MsSql\Log.bch>     
7/25/2013 10:03:43 AM - Info bptm(pid=4328) media id 0315L3 mounted on drive index 2, drivepath {3,0,2,0}, drivename IBM.ULT3580-TD4.001, copy 1
7/25/2013 10:03:43 AM - mounted; mount time: 00:00:48
7/25/2013 10:03:43 AM - positioning 0315L3 to file 1
7/25/2013 10:03:47 AM - Info bptm(pid=4328) waited for full buffer 0 times, delayed 0 times   
7/25/2013 10:03:47 AM - positioned 0315L3; position time: 00:00:04
7/25/2013 10:03:47 AM - begin writing
7/25/2013 10:03:53 AM - Info bptm(pid=4328) EXITING with status 0 <----------       
7/25/2013 10:03:53 AM - Info bpbrm(pid=2004) validating image for client sipc-vcenter01       
7/25/2013 10:03:55 AM - end writing; write time: 00:00:08
7/25/2013 10:03:56 AM - Info bpbkar32(pid=9004) done. status: 0: the requested operation was successfully completed   
the requested operation was successfully completed(0)

[Marianne]

Full backup is like transaction log backup it will truncate and delete logs after backup.  

No - SQL backups need a separate job for transaction logs. It can even be in the same backup script. 
See About NetBackup for SQL Server–Perform multiple operations in sequence in NBU for SQL Admin Guide. 

Please copy latest dbclient log and SQL ERRORLOG to .txt files and post as File Attachments. (.txt files read easier than Word docs). 
The only place where we can see what exactly happened to transaction logs is in the ERRORLOG as this is a SQL function, not NBU.

Also have a look at this post:
https://www-secure.symantec.com/connect/forums/how-backup-sql-transaction-logs#comment-8898601

[BasigDegrasia3]

I attached the latest dbclient Log as you request, but I don't know from where can I get SQL ERRORLOG test file..

thanks

[Mark_Solutions]

Your transaction bch file includes the master database - this should be excluded as you cannot do log truncation for the master database

Only three of the log backups worked correctly due to these warnings:

For VMwareUpdateMgr, vCenterDB and at least one other:

 <16> Dbbackrec::PerformNBOperation: USER - Operation inhibited by NetBackup for Microsoft SQL Server: Only a full or incremental database backup can be performed on database <VmwareView> because it uses the simple recovery model or has 'truncate log on checkpoint' set

According to the log these jobs should end in a status of 1 so should be clearly seen in activity monitor

You need to either change the database recovery type or change the backup type

[BasigDegrasia3]

Hi Mark,

I exclude the DBs Master, Model, and MSDB.

and i run the job now that wil finish in a coouple of minutes.

The recovery type should be a simple recovery mode?

Thanks

[BasigDegrasia3]

TL Backup Completed successfully with status 0, but still logs doesn't truncated.

[Marianne]

Database should NOT be in simple recovery mode - full recovery mode is needed.
Please work with your SQL dba? He/she will know exactly what to do if you show the error message that Mark pointed out above.

SQL dba will also help you to locate the SQL Errorlog.
As per my post above:

SQL ERRORLOG is usually located in <install_path>\mssql\log.

[Mark_Solutions]

Can you post your bch files please

[Will_Restore]

Basig, you might review this doc

         http://msdn.microsoft.com/en-us/library/aa174538(v=sql.80).aspx

and note the last line

Truncation does not reduce the size of a physical log file, it reduces the size of the logical log file. For information on shrinking the size of a physical log file, see Shrinking the Transaction Log.

[BasigDegrasia3]

Hi

All DBs now are full recovery modeI, i Run the TL backup again but still log didn't truncated. attached is the bch files (Full Backup and Transaction Log Backup). 

[BasigDegrasia3]

Attached bch

[Marianne]

Please post dbclient log as well as SQL Errorlog.

Copy logs to .txt files and upload as File attachments.

[Omar_Villa]

Hi, Maybe there is an issue wih sql try running a sql backup and see if that trucates the logs this will tell at what level the issue is. Hope this helps.