SharePoint Cafe

What follows is a long email thread about SQL databases, the size of the LDF File and Backups in portal server.  Since this is an email thread, you'll need to start reading from the bottom and work your way to the top.  This issue is about the size of the LDF file relative to SharePoint Portal Server and the backup process. 

And many thanks to Sara for allowing me to post this here. 

----------------------------------------------------------------------

Hi Bill – Feel free to do whatever you’d like with the info. I’m hoping it will eventually work its way into something “official” as it’s certainly an issue – especially as the size of the SharePoint databases begins to grow. <snip personal conversation>

Anyways, the end result was that:

(1)     I was able to use the assorted tools mentioned to manually shrink the unwieldy sizes of my _SITES logfiles – these were the ones that were WAY out of hand (i.e. database size of 30-40 GB; logfile size of 50+ GB!) – so this does work, and potentially needs to become a part of one’s maintenance plan.

(2)     Per Microsoft – there really is no “rule of thumb” to guess at what a good logfile size SHOULD be; however, it’s really a moot point now due to the final recommendation for the RECOVERY METHOD.

(3)     Per Microsoft – the only supported restore method for SPS is via SPS’s backup/restore tool. It is NOT supported to restore SPS via its restore tool, and then restore some (or all) of the databases to a more current point in time via SQL using backed-up transaction logs. Hence – with SPS there’s no point to having the databases set to FULL RECOVERY at all; SIMPLE RECOVERY provides better performance.

(4)     If you have a standalone WSS installation (no SPS), then there are some options for using SQL tools to restore to a more current point in time – depending on your disaster recovery plan, you may or may not want to change the RECOVERY MODE of the WSS DBs.

Some of the actual “conversation” with Microsoft: 

What size SHOULD the LDF files be for optimal SPS performance?

(came from Microsoft SQL support team)

When you back up an LDF file, you are given the opportunity to specify the new size for this file.  If you don't specify a size, SQL will try to compact this file down as small as possible which might be 1 or 2 MB in size.  Although this is ideal as far as drive space is concerned, it is NOT ideal for performance with the SQL databases for SharePoint.  There is no set formula or rule on how large the LDF file should be, but it should be of an adequate size that will prevent it from having to grow automatically every single day when it reaches its current size limit.  For instance, if you state that the new size of the LDF file is going to be 2 GB in size, the initial size of the LDF file will be 2 GB, and it will stay this size until 2GB worth of new transactions have taken place.  When this point is reached, the LDF file will grow automatically, and for that short time during its automatic growth, you might see a small performance hit on the SQL Server.  This is what needs to be avoided if possible.  The SQL Engineer I spoke with stated that if your MDF file was around 30 to 50 GB in size, a 2 to 3 GB LDF file should be more than adequate to start out with, but again there is no hard formula for what the size of the LDF file should be.

Why are some DBs set to FULL RECOVERY and some are not? Is there even any supported SharePoint restore method that uses SharePoint’s RESTORE to restore the full databases; and then a more current Transaction Log restore within SQL to move databases set to FULL RECOVERY to a more current point in time?

(came from Microsoft SPS escalation team)

The reason that the _SITES database is the only database that has Full Recovery Mode enabled is because it is created by Windows SharePoint Services and the other databases are created by SharePoint Portal Server. [Actually – the Config DB is also set to FULL RECOVERY by default – but it stays small.]  If you have a standalone deployment of Windows SharePoint services, it is possible to back up the database via SQL and add the data from the transaction logs after the fact.  With a SharePoint Portal Server deployment all of the databases must be backed up and restored from the same point in time, so this option is not possible.  I believe this is why the Solution Object from our Development Team stated that it would be all right in a Portal deployment to go ahead and change the Recovery method to simple as the only supported restore method is from the Full SQL Backups taken by the SharePoint Portal Data Backup and Restore utility.

Anyway – since I’m a full SPS deployment – my next (and hopefully final!) reconfig task is to set all my databases to SIMPLE RECOVERY for better performance – and this will make the whole logfile size thing a moot point!

Sara

At a minimum, may I turn this into a blog entry?  I’ll post it at mindsharpblogs.com, if that would be acceptable to you.

I’m hoping that info about the SQL DBs & logfile sizes, maintenance plans, etc. will eventually be documented via KB or whatever for SPS admins (many of whom, like me, are probably NOT SQL DBAs so are somewhat clueless about SQL maintenance… but nevertheless have to deal with it as a part of SPS!).

I’m still muddling things through with PSS – it actually appears that, by default, the PROF & SERV DBs are set to SIMPLE RECOVERY in SQL, while the Config & SITE DBs are set to FULL RECOVERY… which means very different backup/recovery scenarios… which gets very confusing as you then end up (assuming you follow SQL guidelines for frequent & separate backup of the logfiles for the FULL RECOVERY DBs) with the SPS DBs backed up to different points in time: all of them backed up to one point via SPS’s tools, and then the FULL RECOVERY DB logfiles backed up to a more current point in time.

The whole “logfile backup” thing is also somewhat separate from the “shrink the logfiles to manageable sizes” thing, which was my original question – this task took a series of logfile backups + DBCC SHRINKFILE commands (multiple times) to finally make it work.

I’ll be interested to see what the final recommendations end up being (assuming that there ARE some final recommendations!).

Take care,

Sara 

Thanks for the update, Sara.  I’ll be sure to include this in my revised courseware.

Hi again,

I actually ended up with a free case from Microsoft Premier Support, as this is apparently a current issue being tossed around by the SPS & SQL folks.

It appears that:

1. Although SPS can back up the SQL databases through the builtin backup/restore utility – it can’t really (supportably) affect the behavior of the SQL databases & transaction logs themselves… this has to be configured within SQL.
2. If SQL 2000 is installed as recommended for SharePoint (which is essentially with default configuration), then the SQL recovery mode is set to FULL RECOVERY, which logs all transactions so the installation can be recovered to the point of failure (and not just to the last backup)… which is a good thing.
3. Additionally, with SQL 2000 installed as recommended, transaction logs filesize is set to be unlimited… so it just continues to grow.
4. By default, auto shrink is NOT enabled… which they say is a good thing as auto shrink can’t be scheduled so can kick on at very inopportune times.
5. When a full backup is successfully completed, the committed transactions are purged from the logfile; however – the filesize itself remains the same as it doesn’t go through a shrink process; hence – the LDF filesize remains at the largest size it’s ever reached.

Current recommendations (which I’m going to try tonight):

1. Do NOT turn on Auto Shrink for the databases; instead create a scheduled job using the DBCC SHRINKFILE utility (KB272318) to shrink the logfile sizes (can be scheduled within Enterprise Manager using the Mgmt tools).
2. Leave the SQL RECOVERY MODE at “Full Recovery” for all SPS SQL databases; this allows for a restore up to the point of failure.
3. Be sure to create a scheduled job that backs up the Transaction Logs separate from the SharePoint backups; without the Transaction Logs SQL can’t be restored to the point of failure (can be done within Enterprise Manager using the Mgmt tools).

I’m going to try the steps tonight & see how it goes… it sounds like some recommendations/solutions will eventually work their way into SPS documentation since there are definitely related performance issues as well as disaster recovery issues.

Hope all is well!

Thanks,

Sara

Hi Bill,

In all your spare time – if you have a few moments for another question from an old student I’d sure appreciate your input!

In looking at the SQL databases housing my SPS2003 data, I see that for the larger databases (typically _SITE) the LDF file (which I believe is the SQL transaction logfile) is quite a bit larger than the MDF file (which I believe is the primary SQL database file). For example, one of my _SITE database files is about 33GB in size; its companion LDF file is about 55GB in size.

I’m successfully backing up my SPS sites with the builtin backup/restore tool [still working out issues with the CommVault Qinetix SPS backup piece] – successful backups are logged both by SharePoint as well as on the SQL box itself… and I know the backups are working as I’ve had to move my SPS sites around WAY more often than I’d like via the restore tool.

I’ve only recently begun to really take a look at the actual SQL files (had to move the databases to larger partitions) – and I guess I expected the logfiles to be purged once successful backups are done – but it appears that they just keep growing.

I got a newsgroup response saying that the behavior of the SQL LDF files actually depends on settings within SQL itself – and that I probably don’t need the logfiles if I’m using SPS backups or some other tool (which I am) – but I’m wondering if there are any SPS recommendations about the SQL logfile settings?

I pretty much installed SQL with the default settings other than file locations (but in an active/passive 2-node SQL cluster).

I can’t seem to find anything on recommended SQL settings for SharePoint – is there any info you can pass along?

(please let me know if this doesn’t make any sense)

Thanks,

Sara