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,

[SRP]

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

[BasigDegrasia3]

Hi Marianne,

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

[Marianne]

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?

[Marianne]

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)

[bshammari]

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.

[Marianne]

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

[bshammari]

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,