Data Backup and Recovery

SMMOSS and Large SQL Transaction Logs

abaxendale
4,911 Views

Hi,

I am trying to understand why we have such large transaction logs on our SharePoint server, specifically the content database Tlogs. The current DB size is around 8GB with the Tlogs being 5GB.

I can see in SQL management studio (2008) that both the DB and Tlog are being backed up by SMMOSS (2.0.2.0), but I am confused as to why the logs are not truncated following each SMMOSS backup.

This also leads me to another question... what should the recovery model (logging type) for the SharePoint databases be set to? at the moment they are set to Full. I know that in Snap manager for SQL you can use the transaction log backups to perform a 'point in time' restore, but I cant see this option in SMMOSS therefore should the DB recovery model be set to Simple?

Any suggestions welcome!

Thanks,

Alex

8 REPLIES 8

abaxendale
4,911 Views

Response from Netapp was that SMMOSS does not truncate SQL logs by default and that I would have to crate a seperate jobs using the truncate switch.

Think I will just change the recovery model to simple in SQL, as SMMOSS can't do point in time restored anyway.

Alex

abhisek
4,911 Views

Hi Alex ,

SMMOSS does support point in time restore. However I see you are still on older version SMMOSS 2.0.You would require to use the latest one SMSP 6.0.

In a point-in-time restore, databases are restored to a point in time selected from the timeline browser.

A point-in-time restore occurs in two restore scenarios:

•The database is restored to a given time from a backed up transaction log.

•The database is restored but only a subset of backed up transaction logs are applied to it.

Also performing a point-in-time database recovery results in a new recovery path.

It is better to use SMSP 6.0 for backup and restoration of SharePoint Content Db’s instead of SMSQL.

Regards,

Abhishek

abaxendale
4,911 Views

Hi Abhishek,

Yes we are on SMMOSS 2.0.2.0 but connot upgrade untill we have upgraded our ontap version which we hope to do in the next few months.

Do you know if SMSP 6.0 truncates the transaction logs once it has backed them up?

Seems strange that the version jumped up to version 6.0, as I didn't see any updates inbetween...

Thanks,

Alex

abhisek
4,911 Views

Alex,

We had a SMMOSS 5.0 before SMSP 6.0.No it does not truncate the log after taking backup.

This is something in future consideration.

Regards,

Abhishek

abuchmann
4,911 Views

Hi guys

I have exactly the same problem with smsp 6.0. Do you recommend to create an additional smsql job? Or is there a way to add the truncate switch to a smsp backup?

It kind of surprised me, that the logs aren't truncated by default.

kind regards,

adrian

qzhang
4,911 Views

This is a known issue for SMSP with SMSQL 5.1, which the log is not truncated by default. Another SMSQL log backup job with log truncation could be the workaround. Or you can wait for about 2-3 weeks for SMSQL 5.1P1 release, it will have the fix for this issue.

Thanks,

-Qing

abaxendale
4,911 Views

Only saw your reply after my last post!

Thanks for the info!

Alex

abaxendale
4,911 Views

Hi Adrian,

I was a little surprised also that the tlogs are not truncated. Makes me wonder how many people are using the product with ever expanding transaction logs!

I ended up changing the SQL db recovery model to simple as we take 3 snapshots throughout the day in SMMOSS, so don't really need point in time recoverability.

We will be upgrading our ontap version soon as it is holding us back on upgrading to SMSP 6. I might change the recovery model back to full if you manage to find a way to truncate the logs in this version.

Cheers,

Alex

Public