SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Avamar Transaction Log Backup Issue


Avamar Transaction Log Backup Issue

Author
Message
Jysafe Lerroy
Jysafe Lerroy
SSC-Enthusiastic
SSC-Enthusiastic (104 reputation)SSC-Enthusiastic (104 reputation)SSC-Enthusiastic (104 reputation)SSC-Enthusiastic (104 reputation)SSC-Enthusiastic (104 reputation)SSC-Enthusiastic (104 reputation)SSC-Enthusiastic (104 reputation)SSC-Enthusiastic (104 reputation)

Group: General Forum Members
Points: 104 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,
Steve Jones
Steve Jones
SSC Guru
SSC Guru (150K reputation)SSC Guru (150K reputation)SSC Guru (150K reputation)SSC Guru (150K reputation)SSC Guru (150K reputation)SSC Guru (150K reputation)SSC Guru (150K reputation)SSC Guru (150K reputation)

Group: Administrators
Points: 150844 Visits: 19455
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
My Blog: www.voiceofthedba.com
Jysafe Lerroy
Jysafe Lerroy
SSC-Enthusiastic
SSC-Enthusiastic (104 reputation)SSC-Enthusiastic (104 reputation)SSC-Enthusiastic (104 reputation)SSC-Enthusiastic (104 reputation)SSC-Enthusiastic (104 reputation)SSC-Enthusiastic (104 reputation)SSC-Enthusiastic (104 reputation)SSC-Enthusiastic (104 reputation)

Group: General Forum Members
Points: 104 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?
Steve Jones
Steve Jones
SSC Guru
SSC Guru (150K reputation)SSC Guru (150K reputation)SSC Guru (150K reputation)SSC Guru (150K reputation)SSC Guru (150K reputation)SSC Guru (150K reputation)SSC Guru (150K reputation)SSC Guru (150K reputation)

Group: Administrators
Points: 150844 Visits: 19455
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
My Blog: www.voiceofthedba.com
SQLRNNR
SQLRNNR
SSC Guru
SSC Guru (68K reputation)SSC Guru (68K reputation)SSC Guru (68K reputation)SSC Guru (68K reputation)SSC Guru (68K reputation)SSC Guru (68K reputation)SSC Guru (68K reputation)SSC Guru (68K reputation)

Group: General Forum Members
Points: 68701 Visits: 18570
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, MVP


SQL RNNR

Posting Performance Based Questions - Gail Shaw

Jysafe Lerroy
Jysafe Lerroy
SSC-Enthusiastic
SSC-Enthusiastic (104 reputation)SSC-Enthusiastic (104 reputation)SSC-Enthusiastic (104 reputation)SSC-Enthusiastic (104 reputation)SSC-Enthusiastic (104 reputation)SSC-Enthusiastic (104 reputation)SSC-Enthusiastic (104 reputation)SSC-Enthusiastic (104 reputation)

Group: General Forum Members
Points: 104 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?
SQLRNNR
SQLRNNR
SSC Guru
SSC Guru (68K reputation)SSC Guru (68K reputation)SSC Guru (68K reputation)SSC Guru (68K reputation)SSC Guru (68K reputation)SSC Guru (68K reputation)SSC Guru (68K reputation)SSC Guru (68K reputation)

Group: General Forum Members
Points: 68701 Visits: 18570
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, MVP


SQL RNNR

Posting Performance Based Questions - Gail Shaw

Steve Jones
Steve Jones
SSC Guru
SSC Guru (150K reputation)SSC Guru (150K reputation)SSC Guru (150K reputation)SSC Guru (150K reputation)SSC Guru (150K reputation)SSC Guru (150K reputation)SSC Guru (150K reputation)SSC Guru (150K reputation)

Group: Administrators
Points: 150844 Visits: 19455
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
My Blog: www.voiceofthedba.com
SQLRNNR
SQLRNNR
SSC Guru
SSC Guru (68K reputation)SSC Guru (68K reputation)SSC Guru (68K reputation)SSC Guru (68K reputation)SSC Guru (68K reputation)SSC Guru (68K reputation)SSC Guru (68K reputation)SSC Guru (68K reputation)

Group: General Forum Members
Points: 68701 Visits: 18570
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, MVP


SQL RNNR

Posting Performance Based Questions - Gail Shaw

Steve Jones
Steve Jones
SSC Guru
SSC Guru (150K reputation)SSC Guru (150K reputation)SSC Guru (150K reputation)SSC Guru (150K reputation)SSC Guru (150K reputation)SSC Guru (150K reputation)SSC Guru (150K reputation)SSC Guru (150K reputation)

Group: Administrators
Points: 150844 Visits: 19455
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
My Blog: www.voiceofthedba.com
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search