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

Logs Doesn't Truncated Wen TL Job Run

Created: 24 Jul 2013 • Updated: 02 Aug 2013 | 26 comments
This issue has been solved. See solution.

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,

 

 

Operating Systems:

Comments 26 CommentsJump to latest comment

Mark_Solutions's picture

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

Authorised Symantec Consultant

Don't forget to "Mark as Solution" if someones advice has solved your issue - and please bring back the Thumbs Up!!.

SOLUTION
Marianne's picture

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

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

BasigDegrasia3's picture

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??).

Bader N. AL-Shammari

Systems Engineer

BasigDegrasia3's picture

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

AttachmentSize
dbclient.docx 26.58 KB

Bader N. AL-Shammari

Systems Engineer

Marianne's picture

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.

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

BasigDegrasia3's picture

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,

Bader N. AL-Shammari

Systems Engineer

BasigDegrasia3's picture

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)

Bader N. AL-Shammari

Systems Engineer

Marianne's picture

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

 

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

BasigDegrasia3's picture

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

 

thanks

AttachmentSize
dbclient.txt 211.49 KB

Bader N. AL-Shammari

Systems Engineer

Mark_Solutions's picture

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

Authorised Symantec Consultant

Don't forget to "Mark as Solution" if someones advice has solved your issue - and please bring back the Thumbs Up!!.

BasigDegrasia3's picture

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

Bader N. AL-Shammari

Systems Engineer

BasigDegrasia3's picture

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

Bader N. AL-Shammari

Systems Engineer

Marianne's picture

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.

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

Mark_Solutions's picture

Can you post your bch files please

Authorised Symantec Consultant

Don't forget to "Mark as Solution" if someones advice has solved your issue - and please bring back the Thumbs Up!!.

Will Restore's picture

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.

Will Restore -- where there is a Will there is a way

BasigDegrasia3's picture

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). 

Bader N. AL-Shammari

Systems Engineer

BasigDegrasia3's picture

Attached bch

Truncted Logs.jpg

Bader N. AL-Shammari

Systems Engineer

Marianne's picture

Please post dbclient log as well as SQL Errorlog.

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

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

Omar Villa's picture

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.

Omar Villa

Netbackup Expert

Twiter: @omarvillaNBU

 

SRP's picture

I believe this is similar to a "problem" I was facing.

The drive that we use for the log files was filling up on one of our SQL servers.

Checked to make sure that they were being truncated and found that they were.

Spoke to the SQL dba, who told me that SQL keeps the logical file size at a maximum written size - it does not automatically shrink down once the the logs have been truncated - it involves a manual process through SQL to physically shrink down the actual log.

Hope this helps

 

Sean

BasigDegrasia3's picture

Hi Marianne,

I attached the last dbclient log file, but I don't know from where I can get the SQL Errorlog log file.

AttachmentSize
dbclient.txt 56.65 KB

Bader N. AL-Shammari

Systems Engineer

Marianne's picture

I told you twice before:

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

The default installation path for SQL is C:\Program Files\mssql

If the installation path is different, your SQL dba will be able to tell you where SQL is installed.

You really need the errorlog to see what SQL is doing.

Have you discussed SQL truncate function as per this post that I have referred you to a couple of days ago?

https://www-secure.symantec.com/connect/forums/how-backup-sql-transaction-logs#comment-8898601

Also mentioned in this post: https://www-secure.symantec.com/connect/forums/logs-doesnt-truncated-wen-tl-job-run#comment-9030981 
 and this one as well: https://www-secure.symantec.com/connect/forums/logs-doesnt-truncated-wen-tl-job-run#comment-9038231

You seem to be ignoring the need for SQL dba to perform manual shrinking job, right?

 

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

Marianne's picture

Have you had a look at the dbclient log?

backup log "RSA" failed:

BACKUP LOG cannot be performed because there is no current database backup

This means that you need to take a successful backup of RSA database before log backup can be done.

This is only one that failed, the rest were all successful:

INF - OPERATION #1 of batch C:\Program Files\Veritas\NetBackup\DbExt\MsSql\TL.bch FAILED with STATUS 1 (0 is normal).

INF - OPERATION #2 of batch C:\Program Files\Veritas\NetBackup\DbExt\MsSql\TL.bch SUCCEEDED with STATUS 0 (0 is normal).

INF - OPERATION #3 of batch C:\Program Files\Veritas\NetBackup\DbExt\MsSql\TL.bch SUCCEEDED with STATUS 0 (0 is normal)

INF - OPERATION #4 of batch C:\Program Files\Veritas\NetBackup\DbExt\MsSql\TL.bch SUCCEEDED with STATUS 0 (0 is normal)

INF - OPERATION #5 of batch C:\Program Files\Veritas\NetBackup\DbExt\MsSql\TL.bch SUCCEEDED with STATUS 0 (0 is normal)

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

bshammari@sipchem.com's picture

Thanks Marrine and I am sorry if i am disturbing you,

  1. I attach the last SQL Log File.
  2. RSA Full Backup is completed.
  3. ALL DBs are Full Recovery Mode Option.
  4. I know that we can shrink the size manually from SQL, but we don't wanna go to this step.

 

AttachmentSize
SQL_Log.txt 1.5 KB
Marianne's picture

SQL does not believe that there was a successful database backup.

INFO ODBC return code <-1>, SQL State <37000>, SQL Message <4214><[Microsoft][ODBC SQL Server Driver][SQL Server]BACKUP LOG cannot be performed because there is no current database backup.>.

Your latest log is only showing log backup attempt - no database backup.

TIMESTAMP 20130730101514
MASTERSERVER SIPC-NETBACKUP
CLIENT SIPC-VCENTER01
INSTANCE SIPC-VCENTER01
DATABASE RSA
OPERATION BACKUP
OBJECTTYPE TRXLOG

 

TIMESTAMP 20130730132617
MASTERSERVER SIPC-NETBACKUP
CLIENT SIPC-VCENTER01
INSTANCE SIPC-VCENTER01
DATABASE RSA
OPERATION BACKUP
OBJECTTYPE TRXLOG
 
You need something like this:
 
OPERATION BACKUP
DATABASE "RSA"
OBJECTTYPE DATABASE
SQLHOST "SIPC-VCENTER01"
SQLINSTANCE "SIPC-VCENTER01"
NBSERVER "SIPC-NETBACKUP"
MAXTRANSFERSIZE 0
BLOCKSIZE 7
STRIPES 5
ENDOPER TRUE
 
OPERATION BACKUP
DATABASE "RSA"
OBJECTTYPE TRXLOG
SQLHOST "SIPC-VCENTER01"
SQLINSTANCE "SIPC-VCENTER01"
NBSERVER "SIPC-NETBACKUP"
MAXTRANSFERSIZE 0
BLOCKSIZE 7
STRIPES 5
ENDOPER TRUE
 

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

bshammari@sipchem.com's picture

The problem was solved,

  1. Stop the application server.
  2. Start the service again.
  3. TL backup started.
  4. Then the log truncated successfully.

Reason: a Ghost process was stoping the netbackup from doing it processes.

 

Thanks,