SQL Backups are partially successful

[prasad10kv]

Hi

Server's OS is Windows 2012. SQL server 2012 is installed in it. NBU Version: 7.6.0.4

Please find below log description regarding the SQL server getting partially successful.

11/18/2015 1:22:12 AM - Info ascc(pid=103478) MSSQL: Processing SQL Server instance: MSSQLSERVER       
11/18/2015 1:22:13 AM - Info ascc(pid=103478) MSSQL: Processing SQL Server database: MSSQLSERVER\master       
11/18/2015 1:22:13 AM - Info ascc(pid=103478) MSSQL: Processing SQL Server database: MSSQLSERVER\tempdb       
11/18/2015 1:22:13 AM - Info ascc(pid=103478) MSSQL: Processing SQL Server database: MSSQLSERVER\model       
11/18/2015 1:22:13 AM - Info ascc(pid=103478) MSSQL: Processing SQL Server database: MSSQLSERVER\msdb       
11/18/2015 1:22:13 AM - Info ascc(pid=103478) MSSQL: Processing SQL Server database: MSSQLSERVER\AdventureWorks2012       
11/18/2015 1:22:13 AM - Info ascc(pid=103478) MSSQL: Skipping log truncation for database AdventureWorks2012. It is using the simple recovery model and does not support log backups w/truncation
11/18/2015 1:22:13 AM - Info ascc(pid=103478) MSSQL: Processing SQL Server database: MSSQLSERVER\Tfs_Configuration       
11/18/2015 1:22:14 AM - Info ascc(pid=103478) MSSQL: [ODBC MSG]BACKUP LOG [Tfs_Configuration] TO DISK = 'C:\Users\NETBAC~1\AppData\Local\Temp\NBU6496000001D28BFAF1D0_TMP_DIR\MSSQLSERVER_Tfs_Configuration.1447830028' WITH STATS = 10
11/18/2015 1:22:14 AM - Info ascc(pid=103478) MSSQL: [ODBC MSG][Microsoft][SQL Native Client][SQL Server]11 percent processed.     
11/18/2015 1:22:14 AM - Info ascc(pid=103478) MSSQL: [ODBC MSG][Microsoft][SQL Native Client][SQL Server]22 percent processed.     
11/18/2015 1:22:14 AM - Info ascc(pid=103478) MSSQL: [ODBC MSG][Microsoft][SQL Native Client][SQL Server]37 percent processed.     
11/18/2015 1:22:14 AM - Info ascc(pid=103478) MSSQL: [ODBC MSG][Microsoft][SQL Native Client][SQL Server]51 percent processed.     
11/18/2015 1:22:14 AM - Info ascc(pid=103478) MSSQL: [ODBC MSG][Microsoft][SQL Native Client][SQL Server]66 percent processed.     
11/18/2015 1:22:15 AM - Info ascc(pid=103478) MSSQL: [ODBC MSG][Microsoft][SQL Native Client][SQL Server]80 percent processed.     
11/18/2015 1:22:15 AM - Info ascc(pid=103478) MSSQL: [ODBC MSG][Microsoft][SQL Native Client][SQL Server]100 percent processed.     
11/18/2015 1:22:15 AM - Info ascc(pid=103478) MSSQL: [ODBC MSG][Microsoft][SQL Native Client][SQL Server]Processed 279 pages for database 'Tfs_Configuration', file 'Tfs_Configuration_log' on file 1.
11/18/2015 1:22:15 AM - Info ascc(pid=103478) MSSQL: [ODBC MSG][Microsoft][SQL Native Client][SQL Server]BACKUP LOG successfully processed 279 pages in 1.071 seconds (2.031 MB/sec).
11/18/2015 1:22:16 AM - Info ascc(pid=103478) MSSQL: Processing SQL Server database: MSSQLSERVER\Tfs_DefaultCollection       
11/18/2015 1:22:16 AM - Info ascc(pid=103478) MSSQL: [ODBC MSG]BACKUP LOG [Tfs_DefaultCollection] TO DISK = 'C:\Users\NETBAC~1\AppData\Local\Temp\NBU6496000001D28BFAFCA0_TMP_DIR\MSSQLSERVER_Tfs_DefaultCollection.1447830030' WITH STATS = 10
11/18/2015 1:22:16 AM - Info ascc(pid=103478) MSSQL: [ODBC MSG][Microsoft][SQL Native Client][SQL Server]36 percent processed.     
11/18/2015 1:22:16 AM - Info ascc(pid=103478) MSSQL: [ODBC MSG][Microsoft][SQL Native Client][SQL Server]100 percent processed.     
11/18/2015 1:22:17 AM - Info ascc(pid=103478) MSSQL: [ODBC MSG][Microsoft][SQL Native Client][SQL Server]Processed 23 pages for database 'Tfs_DefaultCollection', file 'Tfs_DefaultCollection_log' on file 1.
11/18/2015 1:22:17 AM - Info ascc(pid=103478) MSSQL: [ODBC MSG][Microsoft][SQL Native Client][SQL Server]BACKUP LOG successfully processed 23 pages in 0.048 seconds (3.590 MB/sec).
11/18/2015 1:22:17 AM - Info ascc(pid=103478) MSSQL: Processing SQL Server database: MSSQLSERVER\PASADataFactory       
11/18/2015 1:22:17 AM - Info ascc(pid=103478) MSSQL: [ODBC MSG]BACKUP LOG [PASADataFactory] TO DISK = 'C:\Users\NETBAC~1\AppData\Local\Temp\NBU6496000001D28BFB0770_TMP_DIR\MSSQLSERVER_PASADataFactory.1447830032' WITH STATS = 10
11/18/2015 1:22:18 AM - Warning ascc(pid=103478) MSSQL: Failed to execute the sql statement: BACKUP LOG [PASADataFactory] TO DISK = 'C:\Users\NETBAC~1\AppData\Local\Temp\NBU6496000001D28BFB0770_TMP_DIR\MSSQLSERVER_PASADataFactory.1447830032' WITH STATS = 10.
.ODBC Error Code:    -1
.Diagnostic Records:    2
.
.
. Native Error Code:        4214
. State Code:            42000
. Error Msg:            [Microsoft][SQL Native Client][SQL Server]BACKUP LOG cannot be performed because there is no current database backup.
.
.
. Native Error Code:        3013
. State Code:            42000
. Error Msg:            [Microsoft][SQL Native Client][SQL Server]BACKUP LOG is terminating abnormally.
.

11/18/2015 1:22:18 AM - Warning ascc(pid=103478) MSSQL: Log truncation failed for database : MSSQLSERVER\PASADataFactory     
11/18/2015 1:22:18 AM - Warning ascc(pid=103478) MSSQL: MSSQL application state capture was partially successful     
Status 1
11/18/2015 2:00:11 AM - Info nbjm(pid=4724) starting backup job (jobid=188608) for client PTCEBSSQL3PROD.pasa.pas.local, policy PTC-VMWARE-PLAT-60DAY-IP-SQL, schedule Daily  
11/18/2015 2:00:11 AM - Info nbjm(pid=4724) requesting MEDIA_SERVER_ONLY resources from RB for backup job (jobid=188608, request id:{28176294-420D-4F47-814F-31D2BF6E9965})  
11/18/2015 2:00:11 AM - requesting resource PTCPRDNBUMEDIA1-DISK-MSDP-1
11/18/2015 2:00:11 AM - requesting resource ptcprdnbumaster.pasa.pas.local.NBU_CLIENT.MAXJOBS.PTCEBSSQL3PROD.pasa.pas.local
11/18/2015 2:00:11 AM - requesting resource ptcprdnbumaster.pasa.pas.local.VMware.Datastore.PTCPRDVC05/PASA/VNX6998_lun15_PO
11/18/2015 2:00:12 AM - granted resource ptcprdnbumaster.pasa.pas.local.NBU_CLIENT.MAXJOBS.PTCEBSSQL3PROD.pasa.pas.local
11/18/2015 2:00:12 AM - granted resource ptcprdnbumaster.pasa.pas.local.VMware.Datastore.PTCPRDVC05/PASA/VNX6998_lun15_PO
11/18/2015 2:00:24 AM - estimated 0 Kbytes needed
11/18/2015 2:00:24 AM - begin Parent Job
11/18/2015 2:00:24 AM - begin Application State Capture, Step By Condition
Status 0
11/18/2015 2:00:24 AM - end Application State Capture, Step By Condition; elapsed time: 0:00:00
11/18/2015 2:00:24 AM - begin Application State Capture, Application State Capture
11/18/2015 2:00:34 AM - end Application State Capture, Application State Capture; elapsed time: 0:00:10
Status 1
11/18/2015 2:00:34 AM - end Parent Job; elapsed time: 0:00:10
the requested operation was partially successful(1)

The job was successfully completed, but some files may have been
busy or inaccessible. See the problems report or the client's logs for more details.

Please help in resolving this.

[Marianne]

Have you showed all of this to your dba?

The errors are pretty self-explanatory.  

One db cannot truncate logs because the database is in simple recovery mode, another one cannot backup transaction log because there is no successful db backup.

Dba should either ensure all dbs are in full recovery mode or you should remove these dbs from the log backup script.

Run a full db backup for those that have not previously been backed up.

[prasad10kv]

Hi Marianne

Thanks for your reply. I have a told my DBA team to change the recovery mode from simple to Full for the DBs present in that server.

Actually it is a VM. In VMware tab of policy, I have unchecked the truncate logs and took the full backup then the application state capture was successful. After that I have checked the "truncate logs" option and ran the backup again it was partially successful.

I have a doubt here. When do we get this warning "Log truncation failed for database" and why? Please explain and help.

[prasad10kv]

Hi Marianne

Forgot to ask one more question in the previous comment.

Please explain when do we get the warning " Failed to execute the sql statement: BACKUP LOG". Find below instance for your reference.

Warning ascc(pid=170484) MSSQL: Failed to execute the sql statement: BACKUP LOG [BusinessMashupsDev] TO DISK = 'C:\Windows\TEMP\NBU7508000001D299FE0958_TMP_DIR\MSSQLSERVER_BusinessMashupsDev.1448002823' WITH STATS = 10.
.ODBC Error Code:    -1
.Diagnostic Records:    2
.
.
. Native Error Code:        4214
. State Code:            42000
. Error Msg:            [Microsoft][SQL Server Native Client 10.0][SQL Server]BACKUP LOG cannot be performed because there is no current database backup.
.
.
. Native Error Code:        3013
. State Code:            42000
. Error Msg:            [Microsoft][SQL Server Native Client 10.0][SQL Server]BACKUP LOG is terminating abnormally

[prasad10kv]

Hi Marianne

Thanks for your reply. I have taken this up to my DBA team. If you don't mind could you please explain when and why do we get the warning " Failed to execute the sql statement: BACKUP LOG". Find below instance for your reference.

Warning ascc(pid=170484) MSSQL: Failed to execute the sql statement: BACKUP LOG [BusinessMashupsDev] TO DISK = 'C:\Windows\TEMP\NBU7508000001D299FE0958_TMP_DIR\MSSQLSERVER_BusinessMashupsDev.1448002823' WITH STATS = 10.
.ODBC Error Code:    -1
.Diagnostic Records:    2
.
.
. Native Error Code:        4214
. State Code:            42000
. Error Msg:            [Microsoft][SQL Server Native Client 10.0][SQL Server]BACKUP LOG cannot be performed because there is no current database backup.
.
.
. Native Error Code:        3013
. State Code:            42000
. Error Msg:            [Microsoft][SQL Server Native Client 10.0][SQL Server]BACKUP LOG is terminating abnormally

[Michal_Mikulik1 · Accepted solution]

Hello,

you have probably missed Symantec VSS provider installation. See MSSQL Admin Guide:

Symantec recommends that you use the Symantec VSS provider. To truncate logs,

you must use the Symantec VSS provider to create full VSS backups. The VMware

VSS provider creates copy-only backups, which cannot be used as a basis to

truncate logs.

So MS SQL Server does not konow that any full backup was done.

Michal

[Marianne]

Please explain when do we get the warning ......

I'm not sure that I understand your question.

If I look at your opening post, you seemed to have received the error when the backup failed on 18 Nov at 1:22:18 AM

If you need to know under which circumstances such an error is displayed....

..... When a log backup is attempted without a successful database backup in place.

So, to troubleshoot, have a good look at the current backup policy config for this client.