Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Avamar Transaction Log Backup Issue Expand / Collapse
Author
Message
Posted Monday, February 4, 2013 9:34 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, December 18, 2013 9:44 PM
Points: 18, Visits: 78
Hi,

We just recently started using Avamar to backup everything including an hourly SQL server transaction log backup. The issue we’re having is that since we started using Avamar, the transaction file (LDF) file keeps growing very large. Has anyone experienced this issue before? If not, any idea?

Thanks,
Post #1415376
Posted Monday, February 4, 2013 10:01 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Today @ 2:13 PM
Points: 31,040, Visits: 15,472
I haven't used Avamar, but if the log file is growing, either you have a growing workload that needs more log space, or the log isn't being marked as backed up.

An hourly backup (diff/incremental) from Avamar, doesn't mean that it is performing a SQL Server log backup properly. It needs to be marked in msdb.

Have you checked MSDB to see if the log backups are being noted by SQL Server? If not, it's not doing them correctly.







Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1415390
Posted Monday, February 4, 2013 1:21 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, December 18, 2013 9:44 PM
Points: 18, Visits: 78
Steve Jones - SSC Editor (2/4/2013)
I haven't used Avamar, but if the log file is growing, either you have a growing workload that needs more log space, or the log isn't being marked as backed up.

An hourly backup (diff/incremental) from Avamar, doesn't mean that it is performing a SQL Server log backup properly. It needs to be marked in msdb.

Have you checked MSDB to see if the log backups are being noted by SQL Server? If not, it's not doing them correctly.


Thanks for getting back to me on this.

How do I check that in MSDB?
Post #1415476
Posted Monday, February 4, 2013 1:51 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Today @ 2:13 PM
Points: 31,040, Visits: 15,472
Look for backup history for any of the databases. The restore dialog might be the easiest if you haven't done it before.

See if you have options to restore transaction logs.







Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1415484
Posted Monday, February 4, 2013 1:56 PM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Today @ 2:44 PM
Points: 17,650, Visits: 15,503
Jysafe Lerroy (2/4/2013)
Hi,

We just recently started using Avamar to backup everything including an hourly SQL server transaction log backup. The issue we’re having is that since we started using Avamar, the transaction file (LDF) file keeps growing very large. Has anyone experienced this issue before? If not, any idea?

Thanks,


We ran into this once a few years back and found that the Avamar backup was not backing up the t-log despite running. We went with native SQL Backup for t-logs and then backed up those files using Avamar.




Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp
Post #1415485
Posted Tuesday, February 5, 2013 9:56 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, December 18, 2013 9:44 PM
Points: 18, Visits: 78
Thank you both for getting back to me.

SQLRNNR, what version of Avamar are you running and have you contacted EMC with this issue? If so, what was their response.

Steve, I'm curious to know if there is a script that I can run against the MSDB table to check if the t-log is being backed up?
Post #1415966
Posted Tuesday, February 5, 2013 10:29 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Today @ 2:44 PM
Points: 17,650, Visits: 15,503
This was 2+ years ago. I don't recall the version. We did contact EMC and got nothing in return.



Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp
Post #1415988
Posted Tuesday, February 5, 2013 10:35 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Today @ 2:13 PM
Points: 31,040, Visits: 15,472
This should help:

SELECT T1.Name AS DatabaseName, 
Isnull(Max(T2.recovery_model), 'No Backup Taken') AS recovery_model,
'Log' AS BackupType,
Isnull(CONVERT(VARCHAR(23), CONVERT(DATETIME, Max(T2.backup_finish_date), 131)), '') AS LastBackUpTaken
FROM sys.sysdatabases T1
LEFT OUTER JOIN msdb.dbo.backupset T2
ON T2.database_name = T1.name
WHERE type = 'L'
GROUP BY T1.Name

You can also right click a database, select "restore | database" and in the lower pane of the dialog, you should see full and log backups (and diffs).







Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1415993
Posted Tuesday, February 5, 2013 10:50 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Today @ 2:44 PM
Points: 17,650, Visits: 15,503
Expanding on Steve's script a little, here is one that I did:

SELECT T1.Name AS DatabaseName
,ISNULL(t2.database_name, 'No Backup Taken') AS LogBackupAvail
,T1.recovery_model_desc
,'Log' AS BackupType
,ISNULL(CONVERT(VARCHAR(23), CONVERT(DATETIME, Max(T2.backup_finish_date), 131))
, CASE WHEN T1.recovery_model_desc = 'Simple'
THEN 'N/A'
ELSE 'Backup Not Taken'
END
) AS LastBackUpTaken
FROM sys.databases T1
LEFT OUTER JOIN msdb.dbo.backupset T2
ON t1.name = t2.database_name
AND t2.type = 'L'
GROUP BY T1.Name,T2.database_name,T1.recovery_model_desc





Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp
Post #1415999
Posted Tuesday, February 5, 2013 10:54 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Today @ 2:13 PM
Points: 31,040, Visits: 15,472
SQLRNNR (2/5/2013)
Expanding on Steve's script a little, here is one that I did:


Show off







Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1416000
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse